11g新特性:hangdiag.sql实例hang诊断脚本

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.askmaclean.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!

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569