Post-Mortem Analysis Tools: ASH Dump

Editor’s note: This “post-mortem analysis” series consists of four posts in total. Make sure you also check out:

• Post-Mortem Analysis Tools: Systemstate Dump
• Post-Mortem Analysis Tools: Hanganalyze
• Post-Mortem Analysis Tools: Using Preliminary Connection (publishing Thursday, April 22)

Now, onto today’s post:

As a DBA you’ve probably encountered a situation where a database is hanging, you restart it and after restart everything seems OK, with the problem solved. However, in terms of RCA (root cause analysis), you’re at a loss.

As mentioned in the post dealing with systemstate dump, there are a few possible tools to use in this situation, and the second one, and our subject for today is the ASH dump.

It’s very simple to extract, and worth spending an extra minute for further RCA:

sqlplus / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump ashdumpseconds 30
oradebug tracefile_name

An example of execution:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump ashdumpseconds 30
Statement processed.
SQL> oradebug tracefile_name
/db/oracle/diag/rdbms/pythiandb/pythiandb/trace/pythiandb_ora_22024.trc

The command below will generate an ASH dump from the last 30 seconds to a trace file. You can also generate an ASH dump for minutes by changing the line with ashdumpseconds by:

SQL> oradebug dump ashdump 5

Another way to do it is:

SQL> alter session set events 'immediate ashdump(5)';

Or the equivalent for ashdumpseconds:

SQL> alter session set events 'immediate ashdumpseconds(300)';

Repeating information from other posts in this series, if you cannot create a connection to the database with SQL*Plus (even as SYSDBA), because it’s a hang situation, you can use a preliminary connection.

I’ll discuss this further in a future post. But if you need help ASAP, I’d recommend you read:

• How to Collect Systemstate Dumps When you Cannot Connect to Oracle (Doc ID 121779.1)
• Important Customer information about using Numeric Events (Doc ID 75713.1)

The trace file is generated with instructions to import data with SQLLDR. This way you can realize your post-mortem analysis.

An example of an ASH dump file is:

ASHDUMPSECONDS
=====================================================
Processing Oradebug command 'dump ashdumpseconds 30'
ASH dump
<>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table 
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum &lt; 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file  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= errors=1000000
---------------------------------------------------
<>
<>
####
4092499541,1,93736863,"06-15-2016 16:58:00.581442000",118,13423,1,152,"a3dj32s553jwz",0,3,16794496187212003770,"",0,3121342805,1,20,0,27310348,"06/15/2016 16:57:59",0,0,0,0,0,0,0,310662678,642,1415053318,9371681,422864,0,511985,590,62515,1,289642,7,1595,0,94,12553,,0,1024,3427055676,"","","","","devapp16",35734,""
####
4092499541,1,93736863,"06-15-2016 16:58:00.581442000",309,869,1,0,"",65535,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,112941199,13,0,0,0,0,499675,4294967295,0,1,4294967295,0,0,0,86,12553,,0,0,3427055676,"sqlplus@devdb09 (TNS V1-V3)","sqlplus@devdb09 (TNS V1-V3)","","","devdb09",0,""
####
<>
*** 2016-06-15 16:58:13.931
Oradebug command 'dump ashdumpseconds 30' console output:

I hope this was helpful! If you have questions or thoughts, please leave them in the comments.

You can find other posts in this series here:

• Post-Mortem Analysis Tools: Systemstate Dump
• Post-Mortem Analysis Tools: Hanganalyze
• Post-Mortem Analysis Tools: Using Preliminary Connection (publishing Thursday, April 22)