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