Sunday, August 16, 2015

Oracle : Script shows the enqueue waits in the database

select /*+ RULE CURSOR_SHARING_EXACT */
  to_char(inst_id,'999') "INS",
  ksqsttyp "TYP",
  decode(
    ksqsttyp,
    'CI','Cross Instance - initiation of processes on other instances',
    'CF','Controlfile - contention for controlfile',
    'CU','Cursor Binding',
    'HW','High Watermark - manually add extents',
    'RO','Releasable Object - contention during delete/truncate',
    'ST','Space Management - use LMTs or incr. next extent',
    'TM','Trans Mgmt - check FK idx',
    'TX','Trans Enq - incr. init/max trans or more commits',
    null) "ACTION",
  to_char(sum(ksqstwat),'999,999') "WAITS"
--  , to_char(sum(ksqstget),'999,999,999') "GETS"
from X$KSQST
where ksqstwat > 0
group by inst_id, ksqsttyp
order by 4 desc
/

No comments:

Post a Comment