/*
 * Title  : sho_tx_sql.sql
 * Author : EXADIME LLC
 * Created: 24-Apr-2021
 * Updated: 24-Apr-2021
 * Release: 1.0.0
 * Purpose: Shows sql that causes enq: TX - row lock contention wait.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback on
set serverout on
set termout on

set linesize 125
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

column sno	format 99999		heading "#"
column sd	format 999999		heading "SID"
column sn	format 9999999		heading "Serial#"
column sld	format a15		heading "Sql Id"
column dr	format 999,999,999	heading "Disk Reads"
column bg	format 999,999,999	heading "Buffer Gets"
column rp	format 999,999,999	heading "Rows Processed"
column exs	format 999,999,999	heading "Executions"
column ft	format a25		heading "First Load Time"

spool &&rpt_dir/sho_tx_sql.rpt

select 
	rownum		sno,
	sid		sd,
	serial#		sn,
	q.sql_id	sld,
	disk_reads	dr,
        buffer_gets	bg,
        rows_processed	rp,
        executions	exs,
	first_load_time ft 
from 
	v$session s,v$sql q
where
	sid in (
		select
			sid
		from
			v$session
		where
			state in ('WAITING')
		and
			wait_class != 'Idle'
		and
			event='enq: TX - row lock contention'
		and
			(q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)
	);

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
