This dump was introduced in Oracle 10.1.
To dump the ASH data currently stored in the SGA use :
ASHDUMP <MIN>
where MIN is the number of samples to dump specified in minutes.
For example to dump 5 minutes data:
ALTER SESSION SET EVENTS 'immediate ashdump(5)';
Alternatively use ORADEBUG. For example:
ORADEBUG DUMP ASHDUMP 5
The time interval can also be specified in seconds using: dump:
ASHDUMPSECONDS <SEC>
where SEC is the number of samples to dump specified in seconds.
For example:
For example to dump 5 minutes data:
ALTER SESSION SET EVENTS 'immediate ashdumpseconds(300)';
Alternatively use ORADEBUG. For example:
ORADEBUG DUMP ASHDUMPSECONDS 300
The trace output includes the following:
Example output:
ASH dump <<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>> **************** SCRIPT TO IMPORT **************** ------------------------------------------ Step 1: Create destination table------------------------------------------ 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>>> #### 2066695660,1,3752270,"08-28-2015 20:36:44.900500000",12,1,2,0,"",0,0,0,"",0,0, 0,0,0,0,"",0,0,0,0,0,0,0,4078387448,54586,2,3,2,0,1566,4294967295,0,3730633840, 4294967295,0,0,0,0,0,,0,0,165959219,"oracle@vm3.juliandyke.com (CKPT)","","", "","vm3.juliandyke.com",0,"" #### 2066695660,1,3752267,"08-28-2015 20:36:41.890500000",12,1,2,0,"",0,0,0,"",0,0, 0,0,0,0,"",0,0,0,0,0,0,0,4078387448,54584,2,3,2,0,1001,4294967295,0,3730633840, 4294967295,0,0,0,0,0,,0,0,165959219,"oracle@vm3.juliandyke.com (CKPT)","","", "","vm3.juliandyke.com",0,"" #### 2066695660,1,3752216,"08-28-2015 20:35:50.813547000",3,1,2,0,"",0,0,0,"",0,0, 0,0,0,0,"",0,0,0,0,0,0,0,866018717,18476,100,0,0,1002486,0,4294967291,0,3730633840, 4294967295,0,0,0,0,0,,0,0,165959219,"oracle@vm3.juliandyke.com (PSP0)","","", "","vm3.juliandyke.com",0,"" #### <<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>
For addtional information see:
ORADEBUG DOC EVENT ACTION ASHDUMP ORADEBUG DOC EVENT ACTION ASHDUMPSECONDS