oradebug hanganalyze简单说明

有时候知识顿悟的太晚，就来不及了，这里仅仅介绍如果通过oradebug hanganalyze来查看阻塞的回话

查看帮助

SQL> oradebug help
HELP           [command]         Describe one or all commands
SETMYPID                 Debug current process
SETOSPID       <ospid>             Set OS pid of process to debug
SETORAPID      <orapid> ['force']     Set Oracle pid of process to debug
SETORAPNAME    <orapname>         Set Oracle process name to debug
SHORT_STACK                 Get abridged OS stack
CURRENT_SQL                 Get current SQL
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
PDUMP           [interval=<interval>]     Invoke named dump periodically
           [ndumps=<count>]  <dump_name> <lvl> [addr]
DUMPSGA        [bytes]             Dump fixed SGA
DUMPLIST                 Print a list of available dumps
EVENT           <text>             Set trace event in process
SESSION_EVENT  <text>             Set trace event in session
DUMPVAR        <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE       <address> <type> <count>  Print/dump an address with type info
SETVAR           <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variable
PEEK           <addr> <len> [level]     Print/Dump memory
POKE           <addr> <len> <value>     Modify memory
WAKEUP           <orapid>          Wake up Oracle process
SUSPEND                  Suspend execution
RESUME                     Resume execution
FLUSH                     Flush pending writes to trace file
CLOSE_TRACE                 Close trace file
TRACEFILE_NAME                 Get name of trace file
SETTRACEFILEID <identifier name>     Set tracefile identifier
LKDEBUG                  Invoke global enqueue service debugger
NSDBX                     Invoke CGS name-service debugger
-G           <Inst-List | def | all>     Parallel oradebug command prefix
-R           <Inst-List | def | all>     Parallel oradebug prefix (return output
SETINST        <instance# .. | all>     Set instance list in double quotes
SGATOFILE      <SGA dump dir>          Dump SGA to file; dirname in double quotes
DMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>          Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]     Analyze system hang
FFBEGIN                  Flash Freeze the Instance
FFDEREGISTER                 FF deregister instance from cluster
FFTERMINST                 Call exit and terminate instance
FFRESUMEINST                 Resume the flash frozen instance
FFSTATUS                 Flash freeze status of instance
SKDSTTPCS      <ifname>  <ofname>     Helps translate PCs to names
WATCH           <address> <len> <self|exist|all|target>    Watch a region of memory
DELETE           <local|global|target> watchpoint <id>    Delete a watchpoint
SHOW           <local|global|target> watchpoints    Show  watchpoints
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
IPC                     Dump ipc information
UNLIMIT                  Unlimit the size of the trace file
CALL           [-t count] <func> [arg1]...[argn]  Invoke function with arguments
CORE                     Dump core without crashing process
PROCSTAT                 Dump process statistics

试验

session1
delete from t1 where object_id=100;

session 2
delete from t1 where object_id=100; --hang住

session 3 
delete from t1 where object_id=100; --hang住

session 4
--登录sys用户做hanganalyze
sqlplus / as sysdba
oradebug hanganalyze 4;

生成trace文件，这里仅仅查看最后面的内容

[3]/1/4/48125/0x6d9c42a0/30449/NLEAF/[95] :这里最后的[95],代表该回话session_id=4 被序号[95]所在的session_id阻塞（nodenum=3），

[97]/1/98/57865/0x6dadd320/31682/NLEAF/[95][3]：最后的[95][3]就代表这个session_id=98被序号[95][3]所在的两个session_id阻塞，这里和我的实验思路也是一致的

 

State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[3]/1/4/48125/0x6d9c42a0/30449/NLEAF/[95]
[95]/1/96/18625/0x6dae34e0/30326/LEAF/
[97]/1/98/57865/0x6dadd320/31682/NLEAF/[95][3]

hanganalyze相关内容后续慢慢整理，自己也是在不断的学习中，最近通过hanganalyze解决了不少问题，在实际处理问题中非常的好用。