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.
