Check blocker using undocumented SQL*Plus commands

It is common that DBA called into a scene where DB is responding very slow, even for SQL*Plus. Following technique can be used to debug it using low level approach and rely on trace files generated

• Run: sqlplus -prelimi
• SQL> oradebug setmypid
• SQL> oradebug hanganlyze 12, which will create new trace file in user_dump_dest.  The file name will contain UNIX process ID with the naming convention of SID_ora_PID.trc
• Look for line that says

Found 122 objects waiting for

<0/226/17/0x1102dea82/982/no>
Open chains found:
Chain 1 : :
<0/226/17/0x1102dea82/982/no>
<0/543/43/0x1102da108/991/no>

• Line 2 value 226 and 17 is Oracle session SID and Serial#, last 2 value 982 is UNIX PID
• My session is waiting latch contention due to SID 226
• UNIX PID 982 and 991 are 2 processes that causing contention
• Use oradebug to set PID to 982 and 991, if file SID_ora_982.trc, and SID_ora_991.trc not created by Oracle database
• Open both files and look for SQL statename with "name=UPDATE ..." where after name= will be the SQL statement
  
• Search for section "PROCESS STATE"
• Under this section, it will show "waiter count=203" which tells you how nay sessions are waiting for this process to release the latch
• Pass the SQL statement to application owner to identify which process trigger it, and re-schedule it