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.oracledatabase12g.com & www.askmaclean.com
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!