How Oracle Ash performance reports are used

Active session History Report
Active session History
V$active_session_ The history view provides information on extracting session activity at the instance level. The active session is sampled once per minute and stored in a circular buffer in the SGA. Any session that is connected to the database and is waiting for a conversation that is not part of an idle wait event is considered an active session. Each session sample is a set of row data and $active _session_history View returns the row data for each sampled active session, returning the first row of data for the most recent sampled session. Because active session sampling is a cyclic buffer stored in the SGA, the larger the system activity, The less active time can be stored in the loop buffer. This means that each session that is sampled during this period appears in the v$ view or the time of the session activity is displayed in the v$ view, which is entirely dependent on the database activity.


As part of the Awr snapshot, the contents of the V$active_session_history view are also flushed to disk. Because the contents of the v$ view become very large when there is a busy system activity, only a subset of the sessions are sampled and written to disk. By capturing only active sessions, Then only the associated set of data for the session being executed is generated, not the data for all sessions in the system. Using Ash allows you to v$active_session_history the current data and dba_hist_active_sess_ in the view Historical data in the history view for inspection and performance analysis, It is often possible to avoid the need for replay effort to collect additional performance tracking information. Ash contains the execution plan for each SQL statement that is captured. You can use this information to identify which portion of SQL execution consumes most of the SQL execution time. The Ash report presents the following kinds of information:
SQL identifier for SQL statement
SQL execution plan identifier and hash value of the SQL execution plan used to execute the SQL statement
SQL Execution Plan Information
Number of objects, number of files and number of blocks
Wait for event identifiers and parameters
Conversation identifier and session sequence number
Module and operation name
Service Hash identifier
User Group identifier




Use the active session history report to perform the following profiling:
Transient performance problems usually last only a few minutes.
Range or targeted performance analysis through a variety of dimensions or a combination of time, session, module, operation, or sql_id


Transient performance problems are short-lived in ADDM analysis. ADDM attempts to report the performance issues that most affect the DB time during the analysis cycle. If a particular problem persists for very short periods of time then the severity of its may be averaged or minimized due to other performance issues. But the problem may be in the ADDM. Does not appear, whether a performance issue will be captured by ADDM depends on the duration of this issue and the time interval for generating the awr snapshot.


If a performance issue persists for a long time within the interval of two awr snapshot generation, then this problem is captured by ADDM. For example, if the Awr snapshot time is set to one hours, A performance problem lasted 30 minutes then this problem is not considered a transient performance issue because it lasts for a long time within the snapshot interval and it is captured by ADDM. However, if a performance problem lasts only 2 minutes, it may be considered a transient performance issue because it only lasts for a short time during the snapshot interval, so this issue does not will appear in the ADDM report. For example, if you are told that the system is slow between 10:00 and 10:10, the ADDM analysis interval is from 10:00 to 11:00. The problem is not shown in the ADDM report.


The ash report is divided into sections. The HTML report includes links that can quickly navigate to different sections.




Generate a Ash report
The Ashrpt.sql script is used to generate a report in HTML or text format to display ash information for a specific duration. In order to generate a ash report, do the following:
1. At the SQL prompt, enter:
@ $ORACLE _home/rdbms/admin/ashrpt.sql
2. Specify the report format you want to generate HTML or text:
Enter value for Report_type:text
In this example, choose the ash report that generates the text format
3. Specify the start time of the ash report:
Enter value for Begin_time: 10
In this example, the start time is 10 minutes before the current time.
4. Enter the duration of this report if you want to capture ash information from the specified start time to the current time:
Enter Value for Duration:
In this example, the default is to continue to the current system time minus the specified start time result is the duration of the time.
5. Enter the report name or use the default report name:
Enter value for Report_name:
Using the report name Ashrpt_1_0310_0131.txt
In this example, the default ash name, ashrpt_1_0310_0131, is generated. This report collects ash information from 10 minutes before the current system time continues
Ends at the current time.


Generate Ash report for a specified DB instance
The Ashrpti.sql script is used to generate an HTML or text-formatted report on a specified database and instance to display ash information for a specific duration. This report allows you to specify the database and instance before setting the time to collect ash information, in order to generate a ash report that performs the following actions:
1. At the SQL prompt, enter:
@ $ORACLE _home/rdbms/admin/ashrpti.sql
2. Specify the report format you want to generate HTML or text:
Enter value for report_type:html
In this example, choose the ash report that generates the HTML format
3. Lists the available database IDs and instance numbers:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num db Name Instance Host
----------- -------- ------------ ------------ ------------
3349173529 1 CS CS CS1
3349173529 1 test1 test1 test1
Enter the database identifier (dbid) and the instance number (Inst_num):
Enter value for dbid:3349173529
Using 3349173529 for database ID
Enter value for Inst_num:1


4. This step applies only to the collection of ash reports for an active data guard physical standby instance, and if this is not the case, you can skip this step. In order to generate a ash report for a physical repository, This repository instance must be opened as read-only. This Ash report contains ash information from the primary database stored in the disk and the ash in the standby database memory. Specifies whether data sampling is used for the primary or standby database to generate the report:
You is running ASH report on a Standby database.
To generate the more 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 S standby library is selected


5. Specify the start time of the ash report:
Enter value for Begin_time: 10
In this example, the start time is 10 minutes before the current time.
6. Enter the duration of this report if you want to capture ash information from the specified start time to the current time:
Enter Value for Duration:
In this example, the default is to continue to the current system time minus the specified start time result is the duration of the time.
7. Specify Slot_width in seconds
Enter value for Slot_width:
In this example, the default value is used
8. Interpret the values of the following target information for the input report according to the instructions for subsequent prompts:
target_session_id
target_sql_id
Target_wait_class
Target_service_hash
Target_module_name
Target_action_name
target_client_id
Target_plsql_entry


9. Enter the report name or use the default report name:
Enter value for Report_name:
Using the report name Ashrpt_1_0310_0131.txt
In this example, the default ash name ashrpt_1_0310_0131 is generated. This report collects ash information from 10 minutes before the current system time for a database instance with a database ID of 3349173529 that continues until the current time ends.




Generating ASH reports for Oracle RAC
The Ashrpti.sql script is used to generate an HTML or text-formatted report to the specified database and instance in an Oracle RAC environment to display ash information for a specific duration only ash data that is written to the disk is used to generate the ASH report. This report will only be for the Dba_ A sample of ash found in the Hist_active_sess_history table in the last 10 minutes. In order to generate Ash reports in an Oracle RAC environment, perform the following actions:
1. At the SQL prompt, enter:
@ $ORACLE _home/rdbms/admin/ashrpti.sql
2. Specify the report format you want to generate HTML or text:
Enter value for report_type:html
In this example, choose the ash report that generates the HTML format
3. Lists the available database IDs and instance numbers:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num db Name Instance Host
----------- -------- ------------ ------------ ------------
3349173529 1 CS CS CS1
3349173529 1 test1 test1 test1
3349173529 2 test2 test2 test2
3349173529 3 test3 test3 test3
3349173529 4 test4 test4 test4
Enter the database identifier (dbid) and the instance number (Inst_num):
Enter value for dbid:3349173529
Using Database id:3349173529
Enter instance numbers. Enter ' All ' for all instances in an Oracle
RAC cluster or explicitly specify list of instances (e.g.,).
Defaults to the current instance.
Enter value for Inst_num:all
Using instance number (s): all


4. Specify the start time of the ash report:
Enter value for Begin_time: -1:10
In this example, the start time is 1 hours and 10 minutes before the current time.
5. Enter the duration of this report if you want to capture ash information from the specified start time to the current time:
Enter value for Duration:10
In this example, the duration is 10 minutes.
6. Specify Slot_width in seconds
Enter value for Slot_width:
In this example, the default value is used
7. Interpret the values of the following target information for the input report according to the instructions for subsequent prompts:
target_session_id
target_sql_id
Target_wait_class
Target_service_hash
Target_module_name
Target_action_name
target_client_id
Target_plsql_entry


8. Enter the report name or use the default report name:
Enter value for Report_name:
Using the report name Ashrpt_1_0310_0131.txt
In this example, the default ash name ashrpt_1_0310_ is used 0131 are generated. This report collects ash information for all instances of the database with database ID 3349173529 from 1 hours and 10 minutes before the current system time until the end of 1 hours before the current time.




Using the Ash report
After the ash report is generated, you can re-retrieve information that is identified as a transient performance issue. The ash report is divided into the following sections:
Top Events
Load profile
Top SQL
Top PL/SQL
Top Java
Top Sessions
To Objects/files/latches
Activity over time




Top Evnets
The top-level Wait events section describes the top-level wait events that are generated by the user, background, and so on in the sampled session activity, and uses this information to identify which wait events cause transient performance issues. The top-level wait event contains the following sections:
Top-level user events
This section of information shows a high percentage of user process wait events in the sample session activity.


Top-level post-event
This section of information shows the background process wait event that is a high percentage of the sample session activity.


Top-level wait event parameter P1/p2/p3
This information shows the parameter value of the wait event that is a high percentage of the sample session activity, which is displayed after sorting by the total wait time (%event) percentage. For each wait event p1,p2,p3 the value with the Wait event argument parameter 1,parameter 2, Parameter 3 of these three columns are associated.


Load profile
The Load profile section describes the loading analysis in the sampled session activity. Use this information to identify the service, customer, or SQL command type that caused the transient performance problem. The Load Summary section contains the following information:
Top Service/module
This section of information shows the service and module information that is a high percentage of the sample session activity


Top Client IDs
This section of information shows the ID information of the client that is a high percentage of the sample session activity it is a specific identifier for the application in the database session


Top SQL command types
This section of information shows a high percentage of SQL command types such as Select or update in a sample session activity


Top phases of execution
This section of information shows the compilation and execution of a high percentage of execution steps such as Sql,pl/sql and Java in the sample session activity.


Top SQL
The top-level SQL section describes the top-level SQL statements in the sampled session activity, which can be used to identify high-load SQL statements that cause transient performance problems
The top-level SQL section contains the following information:
Top SQL with top events
Top SQL with top row sources
Top SQL using literals
Top parsing Module/action
Complete list of SQL text


Top SQL with top events
This section of information shows the SQL statement that accounts for a high percentage of the total wait events in the sample session activity.


Top SQL with top row sources
This section shows the SQL statements that represent a high percentage of the sample session activity and their detailed execution plan information. This information allows you to identify which part of SQL execution consumes a lot of SQL execution time


Top SQL using literals
This section of information shows a high percentage of SQL statements that use the literal value in the sample session activity. You can recheck this section of the SQL statement to see if you can use a binding variable instead of the literal value.


Top parsing Module/action
This section of information shows the modules and actions that make up a high percentage of parsing SQL statements when executing in a sample session activity


Complete list of SQL text
This section of information shows the full text content of the top-level SQL statement


Top PL/SQL
This section of information shows a high percentage of the PL/SQL process in the sample session activity.


Top Java
This section of information shows a high percentage of Java programs in the sample session activity


Top Sessions
This section describes a specific wait event that the session is waiting for. Use this information to identify a high percentage of sessions in a sampled session activity they may be the cause of a transient performance problem. The Top Sessions section contains the following information:


Top Sessions
This section of information shows a high percentage of waiting sessions in the sample session activity


Top Blocking sessions
This section of information shows a high percentage of blocking sessions in the sample session activity


Top Sessions Running PQS
This section of information shows which parallel queries are waiting for a high percentage of the sample session activity


Top Objects/files/latches
This section of information shows the information that typically consumes the most database resources, including the following sections:
Top DB objects
This section of information shows database objects (such as tables and indexes) that represent a high percentage of all referenced objects in the sample session activity


Top db Files
This section of information shows the database files that account for a high percentage of access in the sample session activity


Top Latches
This section of information shows a high percentage of latch information in the sample session activity
A latch is a simple, low-level serialization mechanism used to protect shared data structures in the SGA. For example, a latch protects the current Access database and the data block structure in the buffer cache. When these structures are maintained or found, the time that the server or background process requests to hold the latch is very short. Latch implementation relies on the operating system, especially a process Latch for a long time.


Activity over time
This section is the most informative part of the ASH report. This information is for the long-period Ash report because it provides deep-level details about activities and workload summaries during analysis. Activity over Time is divided into 10 periods. The size of each period is based on the duration of the analysis. The first and last periods are strange. All internal periods are equal in size and they can be compared to each other. For example, If the analysis lasts for 10 minutes then all the time periods will be one minute. Then if the analysis time lasts 9 minutes and 30 seconds, then the external window may be every 1 minutes for each 15 second of the internal window.


The information that is included in each period for a specific time period is as follows:
Column description
Duration of the slot time (duration) window
Solt count the number of sessions sampled during the period
Event in the top three wait events in the session
The number of wait events waiting for the event Count Ash sample
%event Ash sampling Wait events as a percentage of the entire analysis period


When comparing internal periods, a skew analysis is performed by identifying the event count and slot count columns of the exception. The exception for the event Count column indicates that the number of wait events waiting in the sampling session has increased. The exception of the slot count column indicates that the activity's pride has increased, Because the ash data is only held in the sample description database from the active session
The load is increased. Typically, this period can cause transient performance issues when the math of the active session sampling and the wait events associated with these sessions increase.

How Oracle Ash performance reports are used