ZAHIR MOHIDEEN: December 2011

These are few ways you can look at the ASH data.

1) Generating ASH Report from Enterprise Manager
2) Querying v$active_session_history
3) Generating ASH Report from sql ( ashrpt.sql or ashrpti.sql in ORACLE_HOME\admin )
4) Dump ASH to a file .

The first three steps are pretty straightforward.
I would opt for first method , as it is available right there in OEM and then I can navigate to appropriate SQLs.

But , for others , we do have some options . ...

Let us see how method 4 works.

The following example dumps the ASH data for the past 5 minutes.

C:>sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 5
Statement processed.

You can use the file to load into the table via SQL Loader or External Table and analyze the ASH data.

Contents of the trace file
--------------------------

Trace file D:\APP\ORACLE\diag\rdbms\dbdev\dbdev\trace\dbdev_ora_5592.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1
CPU                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:1835M/3839M, Ph+PgF:5607M/7677M
Instance name: dbdev
Redo thread mounted by this instance: 1
Oracle process number: 28
Windows thread id: 5592, image: ORACLE.EXE (SHAD)

*** 2011-12-02 09:53:33.615
*** SESSION ID:(24.6023) 2011-12-02 09:53:33.615
*** CLIENT ID:() 2011-12-02 09:53:33.615
*** SERVICE NAME:(dbdev.test.edu) 2011-12-02 09:53:33.615
*** MODULE NAME:(sqlplus.exe) 2011-12-02 09:53:33.615
*** ACTION NAME:() 2011-12-02 09:53:33.615

Processing Oradebug command 'setmypid'

*** 2011-12-02 09:53:33.615
Oradebug command 'setmypid' console output:

*** 2011-12-02 09:53:57.881
Processing Oradebug command 'dump ashdump 5'
ASH dump
<<>>
****************
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 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
---------------------------------------------------
<<>>
<<>>
####
2627103816,1,27387994,"12-02-2011 09:53:47.800000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59482,100,0,0,998719,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387993,"12-02-2011 09:53:46.801000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58536,0,1,1,0,12920,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387990,"12-02-2011 09:53:43.802000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,4078387448,58530,2,3,2,0,651,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387977,"12-02-2011 09:53:30.771000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59466,100,0,0,999755,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387959,"12-02-2011 09:53:12.731000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59447,100,0,0,999253,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387943,"12-02-2011 09:52:56.717000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59431,100,0,0,1000095,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387942,"12-02-2011 09:52:55.716000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59430,100,0,0,998594,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387926,"12-02-2011 09:52:39.717000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59414,100,0,0,997838,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387909,"12-02-2011 09:52:22.719000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59393,100,0,0,999836,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387892,"12-02-2011 09:52:05.705000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59376,100,0,0,1001355,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387875,"12-02-2011 09:51:48.707000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59359,100,0,0,1000171,0,4294967291,0,3937105842,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387858,"12-02-2011 09:51:31.708000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59343,100,0,0,999867,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387841,"12-02-2011 09:51:14.697000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59325,100,0,0,999688,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387825,"12-02-2011 09:50:58.698000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54366,5,1,1000,999949,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387824,"12-02-2011 09:50:57.698000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59308,100,0,0,1000193,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387814,"12-02-2011 09:50:47.683000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54355,5,1,1000,999911,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387812,"12-02-2011 09:50:45.683000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58069,0,1,1,0,11257,4294967295,0,3306949592,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387809,"12-02-2011 09:50:42.683000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58065,0,15,1,0,428,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387807,"12-02-2011 09:50:40.683000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59292,100,0,0,1000681,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387790,"12-02-2011 09:50:23.685000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59274,100,0,0,999428,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387773,"12-02-2011 09:50:06.686000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59258,100,0,0,999501,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387756,"12-02-2011 09:49:49.672000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59241,100,0,0,999596,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387750,"12-02-2011 09:49:43.672000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54291,5,1,1000,1001875,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387739,"12-02-2011 09:49:32.659000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59225,100,0,0,1000266,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387722,"12-02-2011 09:49:15.641000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59207,100,0,0,999939,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387706,"12-02-2011 09:48:59.624000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59189,100,0,0,999878,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
<<>>

*** 2011-12-02 09:53:57.896
Oradebug command 'dump ashdump 5' console output:

ZAHIR MOHIDEEN

Friday, December 2, 2011

Dump ASH Data.