11g中引入了hangdiag.sql 这个脚本来帮助Oracle Support 用来收集诊断实例hang住所需要的diagnostic信息，这个脚本直接存放在11g 以后的$ORACLE_HOME/rdbms/admin目录下，无需用户另外下载。
该脚本用于收集单实例的hang诊断信息还是很不错的，如果是RAC环境的话推荐使用racdiag.sql这个脚本。
值得一提的是 hangdiag.sql会做 11g中独有的(oradebug dump hangdiag_header 1)并将v$wait_chain的源信息x$ksdhng_chains文本形式输出， 之后会做ashdump和systemstate(level 267 可能耗时较长)的dump转储操作，这样获得的trace文件信息就很全面了。
hangdiag.sql:
[oracle@vrh2 ~]$ cat $ORACLE_HOME/rdbms/admin/hangdiag.sql Rem Rem $Header: hangdiag.sql 08-jun-2007.02:06:43 amysoren Exp $ Rem Rem hangdiag.sql Rem Rem Copyright (c) 2007, Oracle. All rights reserved. Rem Rem NAME Rem hangdiag.sql - Hang Diagnosis script Rem Rem DESCRIPTION Rem It is generally useful (but not necessary) to run this script from a Rem prelim mode connection. Rem Rem This script dumps data required to diagnose hangs: Rem 1. ASH dump Rem 2. Systemstate dump with short callstacks Rem 3. Hang analysis results (x$ksdhng_chains) Rem Rem NOTES Rem It is required to set PID using oradebug setmypid/setospid/setorapid Rem before invoking this script. Rem Rem "oradebug tracefile_name" gives the file name including the path of Rem the trace file containing the dumps. Rem Rem Rem MODIFIED (MM/DD/YY) Rem amysoren 06/08/07 - direct_access command syntax change Rem avaliani 05/17/07 - bug 5499564: add x$ksdhng_chains Rem amysoren 01/10/07 - add ashdump, systemstate dump Rem amysoren 01/10/07 - Created Rem -- begin hang diag dump oradebug dump hangdiag_header 1 -- dump hang analysis chains oradebug direct_access enable trace oradebug direct_access disable reply oradebug direct_access set content_type = 'text/plain' oradebug direct_access select * from x$ksdhng_chains -- dump ash data oradebug dump ashdump 5 -- dump systemstate with short callstacks oradebug dump systemstate 267 x$ksdhng_chains内部视图的字段如下 SQL> desc x$ksdhng_chains; Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER CHAIN_ID NUMBER CHAIN_IS_CYCLE NUMBER CHAIN_SIGNATURE VARCHAR2(801) CHAIN_SIGNATURE_HASH NUMBER INSTANCE NUMBER OSID VARCHAR2(25) PID NUMBER SID NUMBER SESS_SERIAL# NUMBER BLOCKER_IS_VALID NUMBER BLOCKER_INSTANCE NUMBER BLOCKER_OSID VARCHAR2(25) BLOCKER_PID NUMBER BLOCKER_SID NUMBER BLOCKER_SESS_SERIAL# NUMBER BLOCKER_CHAIN_ID NUMBER IN_WAIT NUMBER TIME_SINCE_LAST_WAIT_SECS NUMBER WAIT_ID NUMBER WAIT_EVENT NUMBER WAIT_EVENT_TEXT VARCHAR2(64) P1 NUMBER P1_TEXT VARCHAR2(64) P2 NUMBER P2_TEXT VARCHAR2(64) P3 NUMBER P3_TEXT VARCHAR2(64) IN_WAIT_SECS NUMBER TIME_REMAINING_SECS NUMBER NUM_WAITERS NUMBER ROW_WAIT_OBJ# NUMBER ROW_WAIT_FILE# NUMBER ROW_WAIT_BLOCK# NUMBER ROW_WAIT_ROW# NUMBER
使用范例如下：
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmac.cn & www.askmac.cn SQL> oradebug setmypid; Statement processed. SQL> SQL> SQL> @?/rdbms/admin/hangdiag Statement processed. Statement processed. Statement processed. Statement processed. Statement processed. Statement processed. Statement processed. SQL> oradebug tracefile_name; /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ora_22612.trc Sample output 示例输出如下： Processing Oradebug command 'setmypid' Oradebug command 'setmypid' console output: <none> Processing Oradebug command 'dump hangdiag_header 1' Hang diagnosis dumps Oradebug command 'dump hangdiag_header 1' console output: <none> Result of fixed table query: select * from x$ksdhng_chains ADDR = 7FA5333FDC08 INDX = 0 INST_ID = 2 CHAIN_ID = 1 CHAIN_IS_CYCLE = 0 CHAIN_SIGNATURE = 'SQL*Net message from client'<='enq: TM - contention' CHAIN_SIGNATURE_HASH = 373050554 INSTANCE = 2 OSID = 26593 PID = 47 SID = 188 SESS_SERIAL# = 1879 BLOCKER_IS_VALID = 1 BLOCKER_INSTANCE = 2 BLOCKER_OSID = 25967 BLOCKER_PID = 43 BLOCKER_SID = 176 BLOCKER_SESS_SERIAL# = 9631 BLOCKER_CHAIN_ID = 0 IN_WAIT = 1 TIME_SINCE_LAST_WAIT_SECS = 0 WAIT_ID = 8 WAIT_EVENT = 234 WAIT_EVENT_TEXT = enq: TM - contention P1 = 1414332422 P1_TEXT = name|mode P2 = 79775 P2_TEXT = object # P3 = 0 P3_TEXT = table/partition IN_WAIT_SECS = 692 TIME_REMAINING_SECS = -1 NUM_WAITERS = 0 ROW_WAIT_OBJ# = 79775 ROW_WAIT_FILE# = 0 ROW_WAIT_BLOCK# = 0 ROW_WAIT_ROW# = 0 ADDR = 7FA5333FDC08 INDX = 1 INST_ID = 2 CHAIN_ID = 1 CHAIN_IS_CYCLE = 0 CHAIN_SIGNATURE = 'SQL*Net message from client'<='enq: TM - contention' CHAIN_SIGNATURE_HASH = 373050554 INSTANCE = 2 OSID = 25967 PID = 43 SID = 176 SESS_SERIAL# = 9631 BLOCKER_IS_VALID = 0 BLOCKER_INSTANCE = 0 BLOCKER_OSID = BLOCKER_PID = 0 BLOCKER_SID = 0 BLOCKER_SESS_SERIAL# = 0 BLOCKER_CHAIN_ID = 0 IN_WAIT = 1 TIME_SINCE_LAST_WAIT_SECS = 0 WAIT_ID = 148 WAIT_EVENT = 352 WAIT_EVENT_TEXT = SQL*Net message from client P1 = 1650815232 P1_TEXT = driver id P2 = 1 P2_TEXT = #bytes P3 = 0 P3_TEXT = IN_WAIT_SECS = 568 TIME_REMAINING_SECS = -1 NUM_WAITERS = 1 ROW_WAIT_OBJ# = -1 ROW_WAIT_FILE# = 0 ROW_WAIT_BLOCK# = 0 ROW_WAIT_ROW# = 0 2 rows selected Processing Oradebug command 'dump ashdump 5' ASH dump <<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>> <<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>> **************** SCRIPT TO IMPORT **************** ------------------------------------------ Step 1: Create destination table <ashdump> ------------------------------------------ CREATE TABLE ashdump AS SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0 ---------------------------------------------------------------- Step 2: Create the SQL*Loader control file <ashldr.ctl> as below ---------------------------------------------------------------- load data infile * "str '\n####\n'" append into table ashdump fields terminated by ',' optionally enclosed by '"' ( SNAP_ID CONSTANT 0 , DBID , INSTANCE_NUMBER , SAMPLE_ID , SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME ,'MM-DD-YYYY HH24:MI:SSXFF')" , SESSION_ID , SESSION_SERIAL# , SESSION_TYPE , USER_ID , SQL_ID , SQL_CHILD_NUMBER , SQL_OPCODE , FORCE_MATCHING_SIGNATURE , TOP_LEVEL_SQL_ID , TOP_LEVEL_SQL_OPCODE , SQL_PLAN_HASH_VALUE , SQL_PLAN_LINE_ID , SQL_PLAN_OPERATION# , SQL_PLAN_OPTIONS# , SQL_EXEC_ID , SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START" , PLSQL_ENTRY_OBJECT_ID , PLSQL_ENTRY_SUBPROGRAM_ID , PLSQL_OBJECT_ID , PLSQL_SUBPROGRAM_ID , QC_INSTANCE_ID , QC_SESSION_ID , QC_SESSION_SERIAL# , EVENT_ID , SEQ# , P1 , P2 , P3 , WAIT_TIME , TIME_WAITED , BLOCKING_SESSION , BLOCKING_SESSION_SERIAL# , BLOCKING_INST_ID , CURRENT_OBJ# , CURRENT_FILE# , CURRENT_BLOCK# , CURRENT_ROW# , TOP_LEVEL_CALL# , CONSUMER_GROUP_ID , XID , REMOTE_INSTANCE# , TIME_MODEL , SERVICE_HASH , PROGRAM , MODULE , ACTION , CLIENT_ID , MACHINE , PORT , ECID ) --------------------------------------------------- Step 3: Load the ash rows dumped in this trace file --------------------------------------------------- sqlldr userid/password control=ashldr.ctl data=<this_trace_filename> errors=1000000 --------------------------------------------------- <<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>> <<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>> #### 881465081,2,2082369,"01-10-2012 00:49:06.871520000",188,1879,1,0,"562cub3hk2tjy", 0,26,0,"",0,0,0,0,0,33554433,"01/10/2012 00:37:27",0,0,0,0,0,0,0,668627480,9,1414332422, 79775,0,0,0,176,9631,2,79775,0,0,0,94,12553,,0,1024,3427055676,"sqlplus@vrh2.oracle.com (TNS V1-V3)", "sqlplus@vrh2.oracle.com (TNS V1-V3)","","","vrh2.oracle.com",0,"" ....................................... Processing Oradebug command 'dump systemstate 267' =================================================== SYSTEM STATE (level=11, with short stacks) ------------ System global information: ............................. call stack performance statistics: total : 0.030000 sec setup : 0.000000 sec stack unwind : 0.000000 sec symbol translation : 0.030000 sec printing the call stack: 0.000000 sec printing frame data : 0.000000 sec printing argument data : 0.000000 sec ----- End of Call Stack Trace -----
好了 ， 就这么简单， 把trace文件打个zip包传到 My Oracle Support上去吧， 当然你也可以找我！我叫 Maclean.Liu!
