\u8be5\u7bc7\u6587\u7ae0\uff0c\u4ece\u4e00\u6761sql hang\u4f4f\uff0c\u4ece\u89c6\u56fe\uff0c10046\u4e8b\u4ef6\u4ee5\u53cahanganalyze\u5206\u6790\u5e76\u89e3\u51b3\u3002<\/p>\n
\u7248\u672c\u5982\u4e0b\uff1a<\/p>\n
\r\nSQL> set line 400\r\nSQL> select * from gv$version;\r\n\r\n INST_ID BANNER\r\n---------- --------------------------------------------------------------------------------\r\n 1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\n 1 PL\/SQL Release 11.2.0.3.0 - Production\r\n 1 CORE 11.2.0.3.0 Production\r\n 1 TNS for Linux: Version 11.2.0.3.0 - Production\r\n 1 NLSRTL Version 11.2.0.3.0 - Production\r\n<\/pre>\n\u6a21\u62df\u5982\u4e0b\uff1a<\/p>\n
\r\nSQL> conn yallonking\/yallonking\r\nConnected.\r\nSQL> update yallonking set name='cfca' where id=3;\r\n--hang\r\n<\/pre>\n\u8be5\u4f1a\u8bdd\u6b64\u523bhang\u4f4f<\/p>\n
\uff08\u4e00\uff09\u901a\u8fc7\u76f8\u5173\u89c6\u56fe\u67e5\u770b\u9501\u4fe1\u606f<\/strong><\/p>\n
\r\nSQL> SELECT inst_id,DECODE(request, 0, 'Holder :', 'Waiter :') || sid sess,\r\n 2 id1,\r\n 3 id2,\r\n 4 lmode,\r\n 5 2 request,\r\n 6 type\r\n 7 FROM gV$LOCK\r\n 8 WHERE (id1, id2, type) IN\r\n 9 (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)\r\n 10 ORDER BY inst_id,id1, request;\r\n\r\n INST_ID SESS ID1 ID2 LMODE REQUEST TY\r\n---------- ------------------------------------------------ ---------- ---------- ---------- ---------- --\r\n 1 Holder :33 589849 746 6 2 TX\r\n 1 Waiter :32 589849 746 0 2 TX\r\n<\/pre>\n\u6b64\u5904\u53d1\u73b032\u53f7\u4f1a\u8bdd\u5728\u7b49\u5f8533\u53f7\u4f1a\u8bdd\u6240\u6301\u6709\u884c\u9501
\nTM - DML enqueue
\nLMODE 6 - exclusive (X)
\nREQUEST 2 - row-S (SS)<\/p>\n\u67e5\u770b32\u53f7\u4f1a\u8bdd\u5bf9\u5e94\u7684sql\uff08\u4e5f\u662fhang\u4f4f\u7684sql\uff09<\/p>\n
\r\nSQL> select s.inst_id,s.sid,s.MACHINE, s.serial#, s.status, s.username, t.sql_text from gv$session s, gv$sqltext t where s.sql_address = t.address and s.sid in ('33','32') order by s.sid, t.piece;\r\n\r\nINST_ID SID MACHINE SERIAL# STATUS USERNAME SQL_TEXT\r\n------- ---- --------------- ---------- -------- --------------- ----------------------------------------------------------------\r\n 1 32 rhl6.0x64 401 ACTIVE YALLONKING update yallonking set name='cfca' where id=3\t\t \r\n<\/pre>\n\uff08\u4e8c\uff09\u901a\u8fc710046\u67e5\u770b\u8be5\u4f1a\u8bdd\u6240\u5bf9\u5e94\u7684\u7b49\u5f85\u4e8b\u4ef6\t<\/strong> <\/p>\n
\r\nSQL> select a.spid from v$process a ,v$session b\r\n 2 where a.addr=b.paddr and b.sid in (32);\r\n\r\nSPID\r\n------------------------\r\n9601\r\n \r\nSQL> oradebug setospid 9601\r\nOracle pid: 27, Unix process pid: 9601, image: oracle@rhl6.0x64 (TNS V1-V3)\r\nSQL> oradebug unlimit\r\nStatement processed.\r\nSQL> oradebug tracefile_name\r\n\/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9601.trc\r\nSQL> oradebug Event 10046 trace name context forever, level 12\r\nStatement processed.\r\n\/\/\u6b64\u5904\u91cd\u65b0\u5728\u539f\u7a97\u53e3\u6267\u884chang\u4f4f\u7684sql\u8bed\u53e5\uff0c\u5e76\u7b49\u5f85\u4e00\u4f1a\u513f\r\nSQL> oradebug Event 10046 trace name context off\r\nStatement processed.\r\n\r\n<\/pre>\n\u67e5\u770b10046\u751f\u6210\u7684trace\u6587\u4ef6\t\t <\/p>\n
\r\n[root@rhl6 ~]# cat \/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9601.trc\r\nTrace file \/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9601.trc\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\nORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0\/db_1\r\nSystem name: Linux\r\nNode name: rhl6.0x64\r\nRelease: 2.6.32-71.el6.x86_64\r\nVersion: #1 SMP Wed Sep 1 01:33:01 EDT 2010\r\nMachine: x86_64\r\nVM name: VMWare Version: 6\r\nInstance name: ora11gr2\r\nRedo thread mounted by this instance: 1\r\nOracle process number: 27\r\nUnix process pid: 9601, image: oracle@rhl6.0x64 (TNS V1-V3)\r\n... \u7701\u7565 ...\r\n*** 2013-11-15 06:14:55.634\r\nFinished processing ORADEBUG command (#7) 'Event 10046 trace name context forever, level 12'\r\n\r\n*** 2013-11-15 06:14:58.767\r\nWAIT #140147045660632: nam='enq: TX - row lock contention' ela= 772381307 name|mode=1415053318 usn<<16 | slot=589849 sequence=746 obj#=71458 tim=1384467298767000\t\/\/\u53d1\u73b0\u7b49\u5f85\u4e8b\u4ef6\uff0c\u5bf9\u8c61\u4fe1\u606f\r\nERROR #140147045660632:err=1013 tim=1384467298767346\t\/\/\u7528\u6237\u4eba\u4e3a\u53d6\u6d88\u5f53\u524d\u64cd\u4f5c\r\nWAIT #140147045660632: nam='SQL*Net break\/reset to client' ela= 686 driver id=1650815232 break?=0 p3=0 obj#=71458 tim=1384467298768299\r\nWAIT #140147045660632: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=71458 tim=1384467298768388\r\n\r\n*** 2013-11-15 06:15:06.190\r\nWAIT #140147045660632: nam='SQL*Net message from client' ela= 7421834 driver id=1650815232 #bytes=1 p3=0 obj#=71458 tim=1384467306190279\r\nCLOSE #140147045660632:c=0,e=18,dep=0,type=1,tim=1384467306190408\r\n=====================\r\nPARSING IN CURSOR #140147045663736 len=44 dep=0 uid=80 oct=6 lid=80 tim=1384467306240216 hv=1429454996 ad='8ee71a38' sqlid='ft3g9b9am7h4n'\r\nupdate yallonking set name='cfca' where id=3\t\/\/\u7b49\u5f85\u7684\u4f1a\u8bdd\u6240\u6267\u884c\u7684sql\r\nEND OF STMT\r\nPARSE #140147045663736:c=3999,e=49757,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1625575613,tim=1384467306240215\r\n\r\n*** 2013-11-15 06:16:39.102\r\nReceived ORADEBUG command (#8) 'Event 10046 trace name context off' from process 'Unix process pid: 9655, image: <none>'\r\n\r\n*** 2013-11-15 06:16:39.103\r\nFinished processing ORADEBUG command (#8) 'Event 10046 trace name context off'\r\n<\/pre>\n\u4ece\u4e0a\u8fb9\u53ef\u4ee5\u770b\u51fa\u7b49\u5f85\u4e8b\u4ef6\u662f\"enq: TX - row lock contention\" obj#=71458<\/p>\n
\r\nSQL> col OWNER for a20\r\nSQL> col OBJECT_NAME for a20\r\nSQL> col OBJECT_ID for 999999\r\nSQL> select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects where OBJECT_ID=71458;\r\n\r\nOWNER OBJECT_NAME OBJECT_ID\r\n-------------------- -------------------- ---------\r\nYALLONKING YALLONKING 71458\r\n<\/pre>\n\uff08\u4e09\uff09\u901a\u8fc7hanganalyze\u5206\u6790<\/strong>
\n\u5728\u53e6\u4e00\u4e2a\u4f1a\u8bdd\u4e2d<\/p>\n\r\nSQL>oradebug setmypid\r\nSQL>oradebug unlimit;\r\nSQL>oradebug dump hanganalyze 3\t\/\/\u6b64\u5904\u9009\u62e93\u7ea7\uff0c\u6307\u5b9a\u5355\u4e00\u8fdb\u7a0b\r\nSQL> oradebug tracefile_name\r\n\/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9781.trc\r\n<\/pre>\n10 Dump all processes (IGN state)
\n5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
\n4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
\n3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
\n1-2 Only HANGANALYZE output, no process dump at all<\/p>\n\u67e5\u770b\u751f\u6210\u7684trace\u6587\u4ef6<\/p>\n
\r\n[root@rhl6 ~]# cat \/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9781.trc\r\nTrace file \/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9781.trc\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\nORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0\/db_1\r\nSystem name: Linux\r\nNode name: rhl6.0x64\r\nRelease: 2.6.32-71.el6.x86_64\r\nVersion: #1 SMP Wed Sep 1 01:33:01 EDT 2010\r\nMachine: x86_64\r\nVM name: VMWare Version: 6\r\nInstance name: ora11gr2\r\nRedo thread mounted by this instance: 1\r\nOracle process number: 28\r\nUnix process pid: 9781, image: oracle@rhl6.0x64 (TNS V1-V3)\r\n\r\n\r\n*** 2013-11-15 06:20:03.265\r\n*** SESSION ID:(35.153) 2013-11-15 06:20:03.265\r\n*** CLIENT ID:() 2013-11-15 06:20:03.265\r\n*** SERVICE NAME:(SYS$USERS) 2013-11-15 06:20:03.265\r\n*** MODULE NAME:(sqlplus@rhl6.0x64 (TNS V1-V3)) 2013-11-15 06:20:03.265\r\n*** ACTION NAME:() 2013-11-15 06:20:03.265\r\n \r\nProcessing Oradebug command 'setmypid'\r\n\r\n*** 2013-11-15 06:20:03.265\r\nOradebug command 'setmypid' console output: <none>\r\n\r\n*** 2013-11-15 06:20:08.081\r\nProcessing Oradebug command 'unlimit'\r\n\r\n*** 2013-11-15 06:20:08.081\r\nOradebug command 'unlimit' console output: <none>\r\n\r\n*** 2013-11-15 06:20:13.734\r\nProcessing Oradebug command 'dump hanganalyze 3'\r\n\r\n*** 2013-11-15 06:20:13.805\r\n===============================================================================\r\nHANG ANALYSIS:\r\n instances (db_name.oracle_sid): ora11gr2.ora11gr2\r\n oradebug_node_dump_level: 3\r\n os thread scheduling delay history: (sampling every 1.000000 secs)\r\n 0.000000 secs at [ 06:20:13 ]\r\n NOTE: scheduling delay has not been sampled for 0.692331 secs 0.000000 secs from [ 06:20:09 - 06:20:14 ], 5 sec avg\r\n 0.000000 secs from [ 06:19:14 - 06:20:14 ], 1 min avg\r\n 0.000000 secs from [ 06:15:14 - 06:20:14 ], 5 min avg\r\n===============================================================================\r\n \r\nChains most likely to have caused the hang:\r\n [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'\r\n Chain 1 Signature Hash: 0x38c48850\r\n \r\n===============================================================================\r\nNon-intersecting chains:\r\n \r\n-------------------------------------------------------------------------------\r\nChain 1:\r\n-------------------------------------------------------------------------------\r\n Oracle session identified by:\r\n {\r\n instance: 1 (ora11gr2.ora11gr2)\r\n os id: 9601\r\n process id: 27, oracle@rhl6.0x64 (TNS V1-V3)\r\n session id: 32\r\n session serial #: 401\r\n }\r\n is waiting for 'enq: TX - row lock contention' with wait info:\t\t\/\/32\u53f7\u4f1a\u8bdd\u7b49\u5f85\u884c\u9501\r\n {\r\n p1: 'name|mode'=0x54580006\r\n p2: 'usn<<16 | slot'=0x90019\r\n p3: 'sequence'=0x2ea\r\n time in wait: 5 min 7 sec\t\/\/\u7b49\u5f85\u65f6\u957f\r\n timeout after: never\r\n wait id: 36\r\n blocking: 0 sessions\r\n wait history:\r\n * time between current wait and wait #1: 0.050514 sec\r\n 1. event: 'SQL*Net message from client'\r\n time waited: 7.421834 sec\r\n wait id: 35 p1: 'driver id'=0x62657100\r\n p2: '#bytes'=0x1\r\n * time between wait #1 and #2: 0.000057 sec\r\n 2. event: 'SQL*Net message to client'\r\n time waited: 0.000002 sec\r\n wait id: 34 p1: 'driver id'=0x62657100\r\n p2: '#bytes'=0x1\r\n * time between wait #2 and #3: 0.000087 sec\r\n 3. event: 'SQL*Net break\/reset to client'\r\n time waited: 0.000686 sec\r\n wait id: 33 p1: 'driver id'=0x62657100\r\n p2: 'break?'=0x0\r\n }\r\n and is blocked by\r\n => Oracle session identified by:\t\t\/\/32\u53f7\u4f1a\u8bdd\u88ab33\u53f7\u4f1a\u8bdd\u963b\u585e\r\n {\r\n instance: 1 (ora11gr2.ora11gr2)\r\n os id: 9548\r\n process id: 26, oracle@rhl6.0x64 (TNS V1-V3)\r\n session id: 33\r\n session serial #: 263\r\n }\r\n which is waiting for 'SQL*Net message from client' with wait info:\t\t\/\/33\u53f7\u4f1a\u8bdd\u662f\u4e00\u4e2a\u7a7a\u95f2\u7b49\u5f85\u7684\u4f1a\u8bdd\r\n {\r\n p1: 'driver id'=0x62657100\r\n p2: '#bytes'=0x1\r\n time in wait: 24 min 49 sec\t\t\/\/\u7a7a\u95f2\u65f6\u957f\r\n timeout after: never\r\n wait id: 53\r\n blocking: 1 session\r\n wait history:\r\n * time between current wait and wait #1: 0.000011 sec\r\n 1. event: 'SQL*Net message to client'\r\n time waited: 0.000002 sec\r\n wait id: 52 p1: 'driver id'=0x62657100\r\n p2: '#bytes'=0x1\r\n * time between wait #1 and #2: 0.002205 sec\r\n 2. event: 'db file sequential read'\r\n time waited: 0.015846 sec\r\n wait id: 51 p1: 'file#'=0x1\r\n p2: 'block#'=0x109b8\r\n p3: 'blocks'=0x1\r\n * time between wait #2 and #3: 0.001734 sec\r\n 3. event: 'SQL*Net message from client'\r\n time waited: 1 min 11 sec\r\n wait id: 50 p1: 'driver id'=0x62657100\r\n p2: '#bytes'=0x1\r\n }\r\n \r\nChain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'\r\nChain 1 Signature Hash: 0x38c48850\r\n-------------------------------------------------------------------------------\r\n \r\n===============================================================================\r\nExtra information that will be dumped at higher levels:\r\n[level 4] : 1 node dumps -- [LEAF] [LEAF_NW] \r\n[level 5] : 1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW] \r\n \r\nState of ALL nodes\r\n([nodenum]\/cnode\/sid\/sess_srno\/session\/ospid\/state\/[adjlist]):\r\n[31]\/1\/32\/401\/0x97bbfaa0\/9601\/NLEAF\/[32]\r\n[32]\/1\/33\/263\/0x97bbcaf8\/9548\/LEAF\/\r\n\r\n*** 2013-11-15 06:20:13.807\r\n===============================================================================\r\nEND OF HANG ANALYSIS\r\n===============================================================================\r\n\r\n*** 2013-11-15 06:20:13.807\r\n===============================================================================\r\nHANG ANALYSIS DUMPS:\r\n oradebug_node_dump_level: 3\r\n===============================================================================\r\n \r\nState of LOCAL nodes\r\n([nodenum]\/cnode\/sid\/sess_srno\/session\/ospid\/state\/[adjlist]):\r\n[31]\/1\/32\/401\/0x97bbfaa0\/9601\/NLEAF\/[32]\r\n[32]\/1\/33\/263\/0x97bbcaf8\/9548\/LEAF\/\r\n \r\n \r\nNo processes qualify for dumping.\r\n \r\n===============================================================================\r\nHANG ANALYSIS DUMPS: END\r\n===============================================================================\r\n\r\n*** 2013-11-15 06:20:13.808\r\nOradebug command 'dump hanganalyze 3' console output: <none>\r\n\r\n*** 2013-11-15 06:21:12.687\r\nProcessing Oradebug command 'tracefile_name'\r\n\r\n*** 2013-11-15 06:21:12.687\r\nOradebug command 'tracefile_name' console output: \r\n\/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_9781.trc\r\n<\/pre>\n\u6740\u6389\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff08\u8be5\u4f1a\u8bdd\u662f\u4e00\u4e2a\u7a7a\u95f2\u4f1a\u8bdd\uff09\uff0c\u963b\u585e\u7684\u4f1a\u8bdd\uff0832\uff09\u5373\u523b\u6062\u590d<\/p>\n
\r\nSQL> select 'alter system kill session '||''''||c.sid||','||c.serial#||''''||';' from v$session c where c.sid=33;\r\n\r\n'ALTERSYSTEMKILLSESSION'||''''||C.SID||','||C.SERIAL#||''''||';'\r\n--------------------------------------------------------------------------------\r\nalter system kill session '33,263';\r\n\r\nSQL> alter system kill session '33,263';\r\n\r\nSystem altered.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"\u8be5\u7bc7\u6587\u7ae0\uff0c\u4ece\u4e00\u6761sql hang\u4f4f\uff0c\u4ece\u89c6\u56fe\uff0c10046\u4e8b\u4ef6\u4ee5\u53cahanganalyze\u5206\u6790\u5e76\u89e3\u51b3\u3002 \u7248\u672c\u5982\u4e0b\uff1a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[36],"tags":[],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1163"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1163"}],"version-history":[{"count":2,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1163\/revisions"}],"predecessor-version":[{"id":1165,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1163\/revisions\/1165"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1163"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}