Active Session History, (ASH) reports.

 Active Session History, (ASH) reports are one of my favorite when investigating a performance issue.  

The biggest challenge I think that faces many administrators is to know when to turn to an ASH report and how to use the report to diagnose an issue.

# Generating an ASH Report on PDB database.

[oracle@ora-19c]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 6 17:49:49 2024

Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                         READ WRITE NO

SQL> alter session set container=PDB;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Enter value for awr_location: AWR_PDB

Specify the begin time in minutes before the system date:

Enter value for begin_time: -30

In this example, 30 minutes before the current time is selected.

Enter value for begin_time: -30

Enter value for duration: <ENTER>

Enter value for report_name: ASH_report.txt

SQL> !

find same file(ASH_report.txt) in same location.

#Report Options.

You’ll notice that the SQL Details page offers you two reports that you can run-  AWR SQL Report and the ASH Report.  

These reports can both be found in the $ORACLE_HOME/rdbms/admin directory.

AWR SQL_ID specific report is named awrsqrpt.sql and the ASH report is named ashrpt.sql if you’re interested in running them from the command line via SQLPlus.

We are going to choose to use the ASH report instead of the SQL_ID specific AWR report OR a full AWR report for a couple reasons:

1. We are only interested in this specific SQL_ID, so the full AWR report would be overkill.

2. The specific AWR SQL_ID report wouldn’t include the session, blocked session and other wait info we are interested in.

3. We aren’t interested in execution plans, which the SQL_ID specific AWR Report includes.

4. The ASH report allows us to use samples and drill down to by time vs. snapshots, which comes in handy when we are inspecting particular set of transactions. We aren’t concerned with number of executions, which is offered in AWR, but not in samples with ASH.