Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Offline Analysis of ASH Data with ASHDUMP

6 July 2017 1 Comment Written by Christian Antognini

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

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';

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

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;

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

$ 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

$ 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.

11gR2, 12cR1, 12cR2, Active Session History (ASH)
TVD$XTAT 4.0 Beta 11
Activating and Deactivating Performance Feedback

1 Comment

  1. Andy Klock Andy Klock
    14 July 2017    

    Thanks Christian, this was a timely post. I was just trying to sqlldr an ashdump from automatic hang resolution and got errors due to my env not using infile * “str ‘n####n'” correctly and thus searched my way here. Your view worked a charm and after a slight change to Tanel’s ashtop script to use your view I was able to quickly see what was going on. Thank you so much!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.