Offline Analysis of ASH Data with ASHDUMP

From time to time, it happens to me to carry out offline analyses of ASH data. For that, I mean to analyze the ASH data without having access to the database instance that generated it. For that purpose, Oracle Database provides the possibility to dump the content of the ASH buffer as well as information on how to load it through SQL*Loader to a file. The typical steps to carry out to move the data from the source to the destination database (the best thing is to use a destination database with exactly the same version as the source database) are the following:

On the source database…

• Check the number of ASH samples and the oldest entry (to make sure that the period you are looking for is in there)

SQL> SELECT sample_count, oldest_sample_time FROM v$ash_info;

SAMPLE_COUNT OLDEST_SAMPLE_TIME
------------ -------------------------------
       34997 06-JUL-17 08.31.23.294000000 AM

• Dump the ASH buffer for the last n minutes (60 minutes in the example)

SQL> ALTER SYSTEM SET events 'immediate trace name ashdump level 60';

• Get the name of the trace file containing the ASH data

SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------
/u00/oracle/diag/rdbms/dba121/DBA121/trace/DBA121_ora_22501.trc

On the destination database…

• Create a table named ASHDUMP

SQL> CREATE TABLE ashdump AS SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0;

• Extract the SQL*Loader control file from the trace file

$ cd /u00/oracle/diag/rdbms/dba121/DBA121/trace/
$ sed -n '1,/^Step 2:/d;/^Step 3:/,$d;p' DBA121_ora_22501.trc > ashldr.ctl

• Run SQL*Loader to insert ASH data into the ASHDUMP table

$ sqlldr control=ashldr.ctl data=DBA121_ora_22501.trc errors=1000000

Once the data is available in the ASHDUMP table, you can start the analysis. But, in general, you cannot execute against the ASHDUMP table exactly the same queries as the ones you would use with the V$ACTIVE_SESSION_HISTORY view. In fact, the structure and information it provides are quite different.

To make the analysis easier I create a view (ASHDUMP_V) on top of the ASHDUMP table that is much more close to the V$ACTIVE_SESSION_HISTORY view. It allows me to run the same queries that I would use against the V$ACTIVE_SESSION_HISTORY view.

How does the view look like? It is not so simple. Hence, I provide it as a script that contains not only the CREATE VIEW statement but also grants the necessary privileges to the owner of the view. Note that the script has to be executed as SYS.

Here you can download the script for the last four versions (in general, every new version adds new columns):

• 12.2.0.1: ashdump12201.sql
• 12.1.0.2: ashdump12102.sql
• 12.1.0.1: ashdump12101.sql
• 11.2.0.4: ashdump11204.sql

Feel free to test them. In case you find a mismatch between my view and the V$ACTIVE_SESSION_HISTORY view, please, let me know. There might be cases that I did not handle. In any case, note that since some data is missing in the dump. Therefore, not all information can be reconstructed.