SQL QUERY TO FIND HIGH UNDO USAGE ORACLE

SQL query to find high undo usage currently

col SQL_TEXT for a20
col NOSPACEERRCNT for 999
col UNSC for 999
col ORA1555 for 999
select to_char(us.BEGIN_TIME,'DD-MM-YY HH24:MI') BT,
       to_char(us.END_TIME,'DD-MM-YY HH24:MI') ET,
	   us.MAXQUERYLEN as MQL,
	   us.MAXQUERYID,
	   st.SQL_TEXT,
	   us.TUNED_UNDORETENTION as TR,
	   us.ACTIVEBLKS as ABLK,     --Retention not valid but space is valid
	   us.UNEXPIREDBLKS as UXBLK, --Commited txn but retention not passed and need for Read consistency 
	   us.EXPIREDBLKS as XBLK, --Txn Commited and no longer required for RC and RB
	   us.NOSPACEERRCNT as ORA30036,
	   us.SSOLDERRCNT as ORA1555,
	   us.UNXPSTEALCNT as UNSC
from v$undostat us
inner join v$sqltext st on (us.MAXQUERYID = st.sql_id)
order by us.MAXQUERYLEN desc;

This query help you to find the Top undo consuming SQL with the usage details to extract historical undo consumption

col SQL_TEXT for a20
col NOSPACEERRCNT for 999
col UNSC for 999
col ORA1555 for 999
select to_char(dhu.BEGIN_TIME,'DD-MM-YY HH24:MI') BT,
       to_char(dhu.END_TIME,'DD-MM-YY HH24:MI') ET,
	   dhu.MAXQUERYLEN as MQL,
	   dhu.MAXQUERYSQLID,
	   dhsq.SQL_TEXT,
	   dhu.TUNED_UNDORETENTION as TR,
	   dhu.ACTIVEBLKS as ABLK,     --Retention not valid but space is valid
	   dhu.UNEXPIREDBLKS as UXBLK, --Commited txn but retention not passed and need for Read consistency 
	   dhu.EXPIREDBLKS as XBLK, --Txn Commited and no longer required for RC and RB. Can be borrowed
	   dhu.NOSPACEERRCNT as ORA30036, 
	   dhu.SSOLDERRCNT as ORA1555,
	   dhu.UNXPSTEALCNT as UNSC
from dba_hist_undostat dhu
inner join dba_hist_sqltext dhsq on (dhu.MAXQUERYSQLID = dhsq.sql_id)
order by dhu.MAXQUERYLEN desc;

Leave a Reply