This chapter describes how to use sampled data to identify transient performance problems in Oracle Database and contains the following topics:
Transient performance problems are short-lived and do not appear in the Automatic Database Diagnostics Monitor (ADDM) analysis. ADDM tries to report the most significant performance problems during an analysis period in terms of their impact on DB time. Whether a performance problem is captured by ADDM depends on its duration compared to the interval between AWR snapshots.
If a performance problem lasts for a significant portion of the time between snapshots, it will be captured by ADDM. For example, if the snapshot interval is set to one hour, then a performance problem that lasts for 30 minutes should not be considered as a transient performance problem because its duration represents a significant portion of the snapshot interval and will likely be captured by ADDM.
If a particular problem lasts for a very short duration, then its severity might be averaged out or minimized by other performance problems in the analysis period, and the problem may not appear in the ADDM findings. Using the same example where the snapshot interval is set to one hour, a performance problem that lasts for only 2 minutes may be a transient performance problem because its duration represents a small portion of the snapshot interval and will likely not show up in the ADDM findings.
To facilitate the identification of transient performance problems, Oracle Database samples active sessions every second and stores the sampled data in a circular buffer in the shared global area (SGA). Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. By capturing only active sessions, a manageable set of data is represented with its size being directly related to the work being performed, rather than the number of sessions allowed on the system.
Active Session History (ASH) enables you to examine and perform detailed analysis on the sampled session activity in the V$ACTIVE_SESSION_HISTORY view. The data present in ASH can be rolled up in various dimensions that it captures over a specified duration and gathered into an ASH report.
Note:
Data visibility and privilege requirements may differ when using ASH features with pluggable databases (PDBs). For information about how manageability features—including ASH features—work in a multitenant container database (CDB), see Oracle Database Administrator's Guide.See Also:
"Active Session History Statistics" for information about ASHASH reports enable you to perform analysis of:
Transient performance problems that typically last for a few minutes
Scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL identifier
ASH reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains ASH information used to identify blocker and waiter identities, their associated transaction identifiers, and SQL statements for a specified duration.
This section describes how to generate ASH reports and contains the following topics:
The primary interface for generating ASH reports is Oracle Enterprise Manager. Whenever possible, generate ASH reports using Oracle Enterprise Manager.
If Oracle Enterprise Manager is unavailable, then generate ASH reports by running SQL scripts. The DBA role is required to run these scripts.
See Also:
Oracle Database 2 Day + Performance Tuning Guide for information about generating ASH reports using Oracle Enterprise ManagerThis section describes how to generate ASH reports by running SQL scripts in the command-line interface.
This section contains the following topics:
The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration on the local database instance.
To generate an ASH report on the local database instance using the command-line interface:
At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
Specify whether you want an HTML or a text report:
Enter value for report_type: text
In this example, a text report is chosen.
Specify the begin time in minutes before the system date:
Enter value for begin_time: -10
In this example, 10 minutes before the current time is selected.
Specify the duration to capture ASH information in minutes from the begin time.
Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
Enter a report name, or accept the default report name:
Enter value for report_name: Using the report name ashrpt_1_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.
The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration on a specified database and instance. This script enables you to specify a database and instance for which the ASH report will be generated.
To generate an ASH report on a specific database instance using the command-line interface:
At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
Specify whether you want an HTML or a text report:
Enter value for report_type: html
In this example, an HTML report is chosen.
A list of available database IDs and instance numbers are displayed:
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 3309173529 1 MAIN main examp1690 3309173529 1 TINT251 tint251 samp251
Enter the values for the database identifier (dbid) and instance number (inst_num):
Enter value for dbid: 3309173529 Using 3309173529 for database id Enter value for inst_num: 1
To generate an ASH report on a physical standby instance, the standby database must be opened read-only. The ASH data on disk represents activity on the primary database and the ASH data in memory represents activity on the standby database.
Note:
This step is applicable only if you are generating an ASH report on an Active Data Guard physical standby instance. If this is not the case, then skip this step.Specify whether to generate the report using data sampled from the primary or standby database:
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. Enter value for stdbyflag: Using Primary (P) or Standby (S): S
In this example, the default value of Standby (S) is selected.
Specify the begin time in minutes before the system date:
Enter value for begin_time: -10
In this example, 10 minutes before the current time is selected.
Specify the duration to capture ASH information in minutes from the begin time.
Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
Specify the slot width in seconds that will be used in the Activity Over Time section of the report:
Enter value for slot_width:
In this example, the default value is accepted. For more information about the Activity Over Time section and how to specify the slot width, see "Activity Over Time".
Follow the instructions in the subsequent prompts and enter values for the following report targets:
target_session_id
target_sql_id
target_wait_class
target_service_hash
target_module_name
target_action_name
target_client_id
target_plsql_entry
Enter a report name, or accept the default report name:
Enter value for report_name: Using the report name ashrpt_1_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information on the database instance with a database ID value of 3309173529 beginning from 10 minutes before the current time and ending at the current time.
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 Real Application Clusters (Oracle RAC) environment. Only ASH data that is written to disk will be used to generate the report. This report will only use ASH samples from the last 10 minutes that are found in the DBA_HIST_ACTIVE_SESS_HISTORY table.
To generate an ASH report for Oracle RAC:
At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
Specify whether you want an HTML or a text report:
Enter value for report_type: html
In this example, an HTML report is chosen.
A list of available database IDs and instance numbers are displayed:
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 3309173529 1 MAIN main examp1690 3309173529 1 TINT251 tint251 samp251 3309173529 2 TINT251 tint252 samp252 3309173529 3 TINT251 tint253 samp253 3309173529 4 TINT251 tint254 samp254
Enter the values for the database identifier (dbid) and instance number (inst_num):
Enter value for dbid: 3309173529 Using database id: 3309173529 Enter instance numbers. Enter 'ALL' for all instances in an Oracle RAC cluster or explicitly specify list of instances (e.g., 1,2,3). Defaults to current instance. Enter value for inst_num: ALL Using instance number(s): ALL
Specify the begin time in minutes before the system date:
Enter value for begin_time: -1:10
In this example, 1 hour and 10 minutes before the current time is selected.
Specify the duration to capture ASH information in minutes from the begin time.
Enter value for duration: 10
In this example, the duration is set to 10 minutes.
Specify the slot width in seconds that will be used in the Activity Over Time section of the report:
Enter value for slot_width:
In this example, the default value is accepted. For more information about the Activity Over Time section and how to specify the slot width, see "Activity Over Time".
Follow the instructions in the subsequent prompts and enter values for the following report targets:
target_session_id
target_sql_id
target_wait_class
target_service_hash
target_module_name
target_action_name
target_client_id
target_plsql_entry
Enter a report name, or accept the default report name:
Enter value for report_name: Using the report name ashrpt_rac_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_rac_0310_0131 is generated. The report will gather ASH information on all instances belonging to the database with a database ID value of 3309173529 beginning from 1 hour and 10 minutes before the current time and ending at 1 hour before the current time.
After generating an ASH report, review its contents to identify possible causes of transient performance problems.
The contents of the ASH report are divided into the following sections:
See Also:
Oracle Real Application Clusters Administration and Deployment Guide for information about sections in the ASH report that are specific to Oracle Real Application Clusters (Oracle RAC)The Top Events section describes the top wait events of the sampled session activity categorized by user, background, and priority. Use the information in this section to identify wait events that may be causing a transient performance problem.
The Top Events section contains the following subsections:
The Top User Events subsection lists the top wait events from user processes that accounted for the highest percentages of sampled session activity.
The Top Background Events subsection lists the top wait events from backgrounds that accounted for the highest percentages of sampled session activity.
The Top Event P1/P2/P3 subsection lists the wait event parameter values of the top wait events that accounted for the highest percentages of sampled session activity, ordered by the percentage of total wait time (% Event). For each wait event, values in the P1 Value, P2 Value, P3 Value column correspond to wait event parameters displayed in the Parameter 1, Parameter 2, and Parameter 3 columns.
The Load Profile section describes the load analyzed in the sampled session activity. Use the information in this section to identify the service, client, or SQL command type that may be the cause of a transient performance problem.
The Load Profile section contains the following subsections:
The Top Service/Module subsection lists the services and modules that accounted for the highest percentages of sampled session activity.
The Top Client IDs subsection lists the clients that accounted for the highest percentages of sampled session activity based on their client ID, which is the application-specific identifier of the database session.
The Top SQL Command Types subsection lists the SQL command types—such as SELECT or UPDATE commands—that accounted for the highest percentages of sampled session activity.
The Top Phases of Execution subsection lists the phases of execution—such as SQL, PL/SQL, and Java compilation and execution—that accounted for the highest percentages of sampled session activity.
The Top SQL section describes the top SQL statements in the sampled session activity. Use this information to identify high-load SQL statements that may be the cause of a transient performance problem.
The Top SQL section contains the following subsections:
The Top SQL with Top Events subsection lists the SQL statements that accounted for the highest percentages of sampled session activity and the top wait events that were encountered by these SQL statements. The Sampled # of Executions column shows how many distinct executions of a particular SQL statement were sampled.
The Top SQL with Top Row Sources subsection lists the SQL statements that accounted for the highest percentages of sampled session activity and their detailed execution plan information. You can use this information to identify which part of the SQL execution contributed significantly to the SQL elapsed time.
The Top SQL Using Literals subsection lists the SQL statements using literals that accounted for the highest percentages of sampled session activity. You should review the statements listed in this report to determine whether the literals can be replaced with bind variables.
The Top Parsing Module/Action subsection lists the module and action that accounted for the highest percentages of sampled session activity while parsing the SQL statement.
The Complete List of SQL Text subsection displays the entire text of the SQL statements shown in the Top SQL section.
The Top PL/SQL section lists the PL/SQL procedures that accounted for the highest percentages of sampled session activity.
The PL/SQL Entry Subprogram column lists the application's top-level entry point into PL/SQL. The PL/SQL Current Subprogram column lists the PL/SQL subprogram being executed at the point of sampling. If the value of this column is SQL, then the % Current column shows the percentage of time spent executing SQL for this subprogram.
The Top Java section describes the top Java programs in the sampled session activity.
The Top Sessions section describes the sessions that were waiting for a particular wait event. Use this information to identify the sessions that accounted for the highest percentages of sampled session activity, which may be the cause of a transient performance problem.
The Top Sessions section contains the following subsections:
The Top Session subsection lists the sessions that were waiting for a particular wait event that accounted for the highest percentages of sampled session activity.
The Top Blocking Sessions subsection lists the blocking sessions that accounted for the highest percentages of sampled session activity.
The Top Sessions Running PQs subsection lists the sessions running parallel queries (PQs) that were waiting for a particular wait event, which accounted for the highest percentages of sampled session activity.
The Top Objects/Files/Latches section provides additional information about the most commonly-used database resources and contains the following subsections:
The Top DB Objects subsection lists the database objects (such as tables and indexes) that accounted for the highest percentages of sampled session activity.
The Top DB Files subsection lists the database files that accounted for the highest percentages of sampled session activity.
The Top Latches subsection lists the latches that accounted for the highest percentages of sampled session activity.
Latches are simple, low-level serialization mechanisms used to protect shared data structures in the System Global Area (SGA). For example, latches protect the list of users currently accessing the database and the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system-dependent, particularly regarding if and how long a process waits for a latch.
The Activity Over Time section is one of the most informative sections of the ASH report. This section is particularly useful for analyzing longer time periods because it provides in-depth details about activities and workload profiles during the analysis period.
The Activity Over Time section is divided into 10 time slots. The size of each time slot varies based on the duration of the analysis period. The first and last slots are usually odd-sized. All inner slots are equally sized and can be compared to each other. For example, if the analysis period lasts for 10 minutes, then all time slots will 1 minute each. However, if the analysis period lasts for 9 minutes and 30 seconds, then the outer slots may be 15 seconds each and the inner slots will be 1 minute each.
Each of the time slots contains information regarding that particular time slot, as described in Table 9-1.
| Column | Description |
|---|---|
|
Slot Time (Duration) |
Duration of the slot |
|
Slot Count |
Number of sampled sessions in the slot |
|
Event |
Top three wait events in the slot |
|
Event Count |
Number of ASH samples waiting for the wait event |
|
% Event |
Percentage of ASH samples waiting for wait events in the entire analysis period |
When comparing the inner slots, perform a skew analysis by identifying spikes in the Event Count and Slot Count columns. A spike in the Event Count column indicates an increase in the number of sampled sessions waiting for a particular event. A spike in the Slot Count column indicates an increase in active sessions, because ASH data is sampled from active sessions only and a relative increase in database workload. Typically, when the number of active session samples and the number of sessions associated with a wait event increases, the slot may be the cause of a transient performance problem.
To generate the ASH report with a user-defined slot size, run the ashrpti.sql script, as described in "Generating an ASH Report on a Specific Database Instance".