Active Session History (ASH) Report

Overview

You can run ASH reports to analyze transient performance problems with the database that only occur during specific times.

Working with the application owner, coordinate to run ASH during a time span the issue is occuring. Start this process just prior to app\job launch.

Example Report Creation Session 1

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

 Start: <Enter> Defaults to -15 mins
    Enter duration in minutes starting from begin time:
    Defaults to SYSDATE - begin_time
    Press Enter to analyze till current time

    So here wait for application to run then fail or
    experience the issue you are having.
    Then press <Enter> as shown below.

 Enter value for duration: <Enter> Sets report end to current time.
 Enter value for report_name: <Enter> Uses Default

 Report is generated...
 Report written to ashrpt_1_1019_1427.html

You can experiment with the capture time span or enter different values as might be more useful.

Example Report Creation Session 2

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

 Current Instance
 ~~~~~~~~~~~~~~~~
    DB Id    DB Name      Inst Num Instance
 ----------- ------------ -------- ------------
  1234567890 ORADB               2 oradb2

 Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type: html

 Enter value for begin_time: 03/28/21 10:00:00

 Defaults to SYSDATE - begin_time
 Press Enter to analyze till current time
 Enter value for duration: 03/28/21 13:00:00

 Specify the Report Name
 ~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is ashrpt_2_.html.  To use this name,
 press <return> to continue, otherwise enter an alternative.
 Enter value for report_name: <ENTER>

 Report runs...
 Report written to ashrpt_4_.html

Standby (Data Guard) Detection

If running from a Standby database you will get this prompt also:

 You are running ASH report on a Standby database. To generate the report
 over data sampled on the Primary database, enter 'P'.
 Defaults to 'S' - data sampled in the Standby database.

 If capturing for a process on your Standby database press S.