How To Generate ASH Report

Active Session History (ASH) is a very important feature to analyze sessions in Oracle to troubleshoot transient problems that last for just few minutes. ASH reports gives the details needed to analyze the sessions.

Below example shows how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using Enterprise Manager also.

Default Script Location : $ORACLE_HOME/rdbms/admin/
Scripts                             : ashrpt.sql
(for singleinstance)

ashrpti.sql (for  RAC)

1. Running ashrpt.sql (for single instance)

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

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
12345     TESTDB             1  TESTDB1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type:
html
..
..
Defaults to -15 mins
Enter value for begin_time: 09/15/11 00:00:00
Report begin time specified: 09/15/11 00:00:00

Enter value for duration: 5
Report duration specified:   5

Using 15-Sep-11 00:00:00 as report begin time
Using 15-Sep-11 00:05:00 as report end time

Enter value for report_name: ASH

Summary of All User Input
————————-
Format         : HTML
DB Id          : 12345
Inst num       : 1
Begin time     : 15-Sep-11 00:00:00
End time       : 15-Sep-11 00:05:00
Slot width     : Default
Report targets : 0
Report name    : ASH

End of Report
</body></html>
Report written to ASH

2. Running the ashrpti.sql (for RAC)

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

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

Type Specified:                                  html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
12345         1     TESTDB       TESTDB1      abcd123

Defaults to current database
Enter value for dbid: 12345

Using database id:
12345

Defaults to current instance
Enter value for inst_num: 1

Using instance number:
1
..
..

Defaults to -15 mins
Enter value for begin_time: 09/15/11 00:00:00
Report begin time specified: 09/15/11 00:00:00

Enter duration in minutes starting from begin time:
Defaults to SYSDATE – begin_time
Press Enter to analyze till current time
Enter value for duration:
5
Report duration specified:   5

Using 15-Sep-11 00:00:00 as report begin time
Using 15-Sep-11 00:05:00 as report end time

Enter value for slot_width: 1

(Here we get values for each minutes. So we get more precise values.)
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
Enter value for target_session_id:
SESSION report target specified:

Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_sql_id:
SQL report target specified:

Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter ‘CPU’ to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_wait_class:
WAIT_CLASS report target specified:

Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
Enter value for target_service_hash:
SERVICE report target specified:

Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_module_name:
MODULE report target specified:

Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_action_name:
ACTION report target specified:

Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_client_id:
CLIENT_ID report target specified:

Specify PLSQL_ENTRY name (eg: “SYS.DBMS_LOB.*”) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_plsql_entry:
PLSQL_ENTRY report target specified:

End of Report
</body></html>
Report written to ashrpt_1_0915_0005.html

Article by Divya

Comments are closed.