Search This Blog

2008-08-29

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

No comments:

Blog Archive