Oracle ASH report introduced in Oracle 10g release 2.
ASH collects samples of active sessions every second (waiting for non-idle events, or on the CPU working) from v$sessions (inactive sessions are not captured).
You can run ASH reports to analyze transient performance problems with the database that only occur during specific times. This technique is especially useful when you are trying to do either of the following:
1. Resolve transient performance problems that may last for only a short period of time, such as why a particular job or session is not responding when the rest of the instance is performing as usual
2. Perform scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL identifier
ASH report is a small report compared to the AWR report which will provide the db/instance details for a short period of time.
To capture a detailed history of database activity, Oracle Database samples active sessions each second with the Active Session History (ASH) sampler. AWR snapshot processing collects the sampled data into memory and writes it to persistent storage. ASH is an integral part of the Oracle Database self-management framework and is extremely useful for diagnosing performance problems.
ASH gathers sampled data at the session level rather than at the instance level. By capturing statistics for only active sessions, ASH collects a manageable set of data. The size of this data is directly related to the work being performed, rather than to the size of the entire database instance.
Sampled data captured by ASH can be aggregated based on the dimensions in the data, including the following:
* SQL identifier of a SQL statement
* Object number, file number, and block number
* Wait event identifier and parameters
* Session identifier and session serial number
* Module and action name
* Client identifier of the session
* Service hash identifier
Major ASH report sections are:
Top User Events
Top Background Events
Top Cluster Events
Top Service/Module
Top SQL Command Types
Top Phases of Execution
Top Remote Instances
Top SQL with Top Events
Steps to generate a ASH report (for all active sessions) :
1. Using ashrpt.sql
2. Using Oracle Enterprise Manager (OEM)
3. Using v$active_session_history
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/21 00:00:00 --->> MM/DD/YY HH24:MI:SS
Report begin time specified: 09/15/21 00:00:00
Enter value for duration: 5 --->> Enter the duration in minutes (How much time of report need to generate i.e. 5 means in minutes)
Report duration specified: 5
Using 15-Sep-21 00:00:00 as report begin time
Using 15-Sep-21 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-21 00:00:00
End time : 15-Sep-21 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/21 00:00:00
Report begin time specified: 09/15/21 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-21 00:00:00 as report begin time
Using 15-Sep-21 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
