Hanganalyze的使用
It is important to find the that the reason hangs the database.
How can we do, is a headache thing. And we can use oracle internal tool to find the cause, which is hanganalyze.
Level of hanganalyze
[sql] 10 Dump all processes (IGN state) 5 Level 4 + Dump all processes involved in wait chains (NLEAF state) 4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state) 3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state) 1-2 Only HANGANALYZE output, no process dump at all How to use hanganalyze [sql] Single node ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level'; ORADEBUG hanganalyze Rac: ORADEBUG setmypid ORADEBUG setinst all ORADEBUG -g def hanganalyze For example SQL> update test set empno=9999 where empno=7788; 1 row updated. Another session SQL> update test set empno=9111 where empno=7788; SQL> oradebug setmypid SQL> oradebug hanganalyze 3 Hang Analysis in /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc
The contents of /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc [sql] Trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name: SunOS Node name: Solaris10 Release: 5.10 Version: Generic_142910-17 Machine: i86pc Instance name: prod Redo thread mounted by this instance: 1 Oracle process number: 33 Unix process pid: 55, image: oracle@Solaris10 (TNS V1-V3) *** 2013-08-05 03:33:20.007 *** SESSION ID:(34.21990) 2013-08-05 03:33:20.007 *** CLIENT ID:() 2013-08-05 03:33:20.007 *** SERVICE NAME:(SYS$USERS) 2013-08-05 03:33:20.007 *** MODULE NAME:(sqlplus@Solaris10 (TNS V1-V3)) 2013-08-05 03:33:20.007 *** ACTION NAME:() 2013-08-05 03:33:20.007 Processing Oradebug command 'hanganalyze 3' *** 2013-08-05 03:33:20.303 =============================================================================== HANG ANALYSIS: instances (db_name.oracle_sid): prod.prod oradebug_node_dump_level: 3 analysis initiated by oradebug =============================================================================== Chains most likely to have caused the hang: [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention' Chain 1 Signature Hash: 0x38c48850 [b] Chain 2 Signature: 'Streams AQ: waiting for messages in the queue' Chain 2 Signature Hash: 0xa00e2e87 =============================================================================== Non-intersecting chains: ------------------------------------------------------------------------------- Chain 1: ------------------------------------------------------------------------------- Oracle session identified by: { instance: 1 (prod.prod) os id: 29878 process id: 32, oracle@Solaris10 (TNS V1-V3) session id: 5 session serial #: 20196 } is waiting for 'enq: TX - row lock contention' with wait info: { p1: 'name|mode'=0x54580006 p2: 'usn<<16 | slot'=0x4001e p3: 'sequence'=0x430 time in wait: 1 min 20 sec timeout after: never wait id: 27 blocking: 0 sessions current sql: update test set empno=9111 where empno=7788 short stack: ksedsts()+279<-ksdxfstk()+33<-ksdxcb()+928<-sspuser()+127<-__sighndlr()+6<-call_user_handler()+594<-sigacthandler()+238<-_syscall6()+27<-sskgp wwait()+293<-ksliwat()+3146<-kslwaitctx()+147<-ksqcmi()+4000<-ksqgtlctx()+2538<-ksqgelctx()+590<-ktcwit1()+377<-kdddgb()+2953<-kdusru()+6214<-updrowFastPath()+1696<-qer upFetch()+2551<-updaul()+1265<-updThreePhaseExe()+470<-updexe()+531<-opiexe()+9433<-kpoal8()+4217<-opiodr()+1087<-ttcpip()+1417<-opitsk()+1589<-opiino()+1777<-opiodr()+ 1087<-opidrv()+858<-sou2o()+87<-opimai_re wait history: * time between current wait and wait #1: 0.005578 sec 1. event: 'Disk file operations I/O' time waited: 0.000045 sec wait id: 26 p1: 'FileOperation'=0x2 p2: 'fileno'=0x4 p3: 'filetype'=0x2 * time between wait #1 and #2: 0.005175 sec 2. event: 'SQL*Net message from client' time waited: 1 min 42 sec wait id: 25 p1: 'driver id'=0x62657100 p2: '#bytes'=0x1 * time between wait #2 and #3: 0.000002 sec 3. event: 'SQL*Net message to client' time waited: 0.000002 sec wait id: 24 p1: 'driver id'=0x62657100 p2: '#bytes'=0x1 } and is blocked by => Oracle session identified by: { instance: 1 (prod.prod) os id: 29867 process id: 35, oracle@Solaris10 (TNS V1-V3) session id: 99 session serial #: 4407 } which is waiting for 'SQL*Net message from client' with wait info: { p1: 'driver id'=0x62657100 p2: '#bytes'=0x1 time in wait: 1 min 37 sec timeout after: never wait id: 86 blocking: 1 session current sql:short stack: ksedsts()+279<-ksdxfstk()+33<-ksdxcb()+928<-sspuser()+127<-__sighndlr()+6<-call_user_handler()+594<-sigacthandler()+238<-_syscall6()+27<-sskgp wwait()+293<-ksliwat()+3146<-kslwait()+263<-kwqidexfcy()+1415<-kwqidsc81i()+2140<-kwqidrdq()+11126<-kwqidsfmp()+500<-kwqidafm0()+4246<-kwqididqx()+3562<-kpoaqdq()+2955< -opiodr()+1087<-ttcpip()+1417<-opitsk()+1589<-opiino()+1777<-opiodr()+1087<-opidrv()+858<-sou2o()+87<-opimai_real()+541<-ssthrdmain()+295<-main()+203<-_start()+108 wait history: * time between current wait and wait #1: 0.000182 sec 1. event: 'SQL*Net message from client' time waited: 0.000325 sec wait id: 206307 p1: 'driver id'=0x54435000 p2: '#bytes'=0x1 * time between wait #1 and #2: 0.000023 sec 2. event: 'SQL*Net message to client' time waited: 0.000003 sec wait id: 206306 p1: 'driver id'=0x54435000 p2: '#bytes'=0x1 * time between wait #2 and #3: 0.000665 sec 3. event: 'Streams AQ: waiting for messages in the queue' time waited: 5.000056 sec wait id: 206305 p1: 'queue id'=0x3037 p2: 'process#'=0x3ff4ed7e0 p3: 'wait time'=0x5 } Chain 2 Signature: 'Streams AQ: waiting for messages in the queue' Chain 2 Signature Hash: 0xa00e2e87 ------------------------------------------------------------------------------- =============================================================================== Extra information that will be dumped at higher levels: [level 4] : 1 node dumps -- [LEAF] [LEAF_NW] [level 5] : 2 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW] State of ALL nodes ([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]): [4]/1/5/20196/3ff5c8e40/29878/NLEAF/[98] [98]/1/99/4407/3ff6d52a0/29867/LEAF/ [221]/1/222/38/3ff841aa8/14055/SINGLE_NODE/ *** 2013-08-05 03:33:20.311 =============================================================================== END OF HANG ANALYSIS =============================================================================== *** 2013-08-05 03:33:20.312 =============================================================================== HANG ANALYSIS DUMPS: oradebug_node_dump_level: 3 =============================================================================== State of LOCAL nodes ([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]): [4]/1/5/20196/3ff5c8e40/29878/NLEAF/[98] [98]/1/99/4407/3ff6d52a0/29867/LEAF/ [221]/1/222/38/3ff841aa8/14055/SINGLE_NODE/ No processes qualify for dumping. =============================================================================== HANG ANALYSIS DUMPS: END =============================================================================== *** 2013-08-05 03:33:20.312 Oradebug command 'hanganalyze 3' console output: Hang Analysis in /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc