The active session history (ASH) sampler performs the sampling. ASH samples the current state of all active sessions. The database collects this data into memory, where you can access it with a V$ view.
Using ASH enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information.
ASH also contains execution plan information for each captured SQL statement. You can use this information to identify which part of SQL execution contributed most to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:
■ SQL identifier of SQL statement
■ SQL plan identifier and hash value of the SQL plan used to execute the SQL statement
■ SQL execution plan information
■ 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
■ Consumer group identifier
You can gather ASH information over a specified duration into a report.
Generating an ASH Report
OEM is one great tool to generate the ASH report without any extra hassles. Below we have documented the manual way of generating ASH. The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration.
When it prompts you for begin time you can give an actual date, and time to the minute and second, or if you wanted to start from a begin time 20 minutes ago, just type -20 (yes, negative 20). And for duration give the number of minutes you want the report to cover, you can pick a couple of minutes before an incident and couple of minutes after the incident. Usually not try to go beyond 10-15 mins duration.
@$ORACLE_HOME/rdbms/admin/ashrpt.sql Enter value for report_type: text Specify the begin time in minutes before the system date: Enter value for begin_time: -10 Enter the duration in minutes that the report for which you want to capture ASH information from the begin time. Enter value for duration: Enter a report name, or accept the default report name: Enter value for report_name: Using the report name ashrpt_1_0310_0131.txt
Generating an ASH Report on a Specific Database Instance
The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for a specified database and instance.
Generating an Oracle RAC ASH Report
The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for specified databases and instances in an Oracle RAC environment.
Once the report is generated, look for the below:
1. Average Active Sessions: the more this statistics the more your DB was busy. So usually if you see this in the several tens or hundreds then you have a problem!
2. Top User Events: this gives you an idea as to what the most waited on event was within the time span you ran the report for. The % Event gives you an idea as to how much a particular wait event contributed to the overall wait.
3. Top SQL Command Types: this will give you an idea of what operation was being run the most, usually UPDATE/DELETES etc may show you locks etc. So pay attention.
4. Top SQL with TOP Events: this is the main section that shows you the most expensive SQL for the time period in question along with their SQL ID and the database wait event that contributed to their latency.
There are other details, but these 4 above will give you a very quick idea on what slowed the database and these ASH reports are much less verbose than the AWR reports and more importantly, ASH is much more granular.
Once you have identified the SQL ID, you can delve deeper to look at the plan and maybe craft a solution to the problematic SQL.
This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:
- Performance Tuning Basics 1 : Selectivity and Cardinality
- Performance Tuning Basics 2 : Parsing
- Performance Tuning Basics 3 : Parent and Child Cursors
- Performance Tuning Basics 4 : Bind Variables
- Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
- Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
- Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
- Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
- Performance Tuning Basics 9 : Optimizer Mode
- Performance Tuning Basics 10 : Histograms
- Performance Tuning Basics 11 : Steps to analyze a performance problem
- Performance Tuning Basics 12 : Dynamic Performance Views
- Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
- Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
- Performance Tuning Basics 15 : AWR Report Analysis
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
- Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021