Note 853576 - Oracle 10g: Performance analysis w/ ASH and Oracle Advisors

Summary

Symptom
Workload, space requirements, and performance of the Oracle database Version 10g and higher should be analyzed.

Reason and Prerequisites
As of Oracle 10g, Oracle provides a multitude of new features that facilitate the analysis of workdload, space requirements and performance of the database:

• Active Session History (ASH): Analysis of historical wait events and CPU consumption of Oracle sessions

• Automatic Workload Repository (AWR): Collection of database performance data

• Automatic Database Diagnostic Monitor (ADDM): Tuning recommendations based on AWR

• Segment Advisor: Information about space requirements and fragmentation of segments

• SQL Tuning Advisor: Recommendations about optimizing SQL statements

• SQL Access Advisor: Recommendations about optimizing SQL statements

To be able to use these features, the Oracle Tuning Pack and the Oracle Diagnostics Pack must be licensed. If you have purchased the Oracle license through SAP, these packages are part of the license in accordance with Note 740897 .

Refer to Notes 618868, 619188 and 766349 for a general understanding of the Oracle performance analysis.

Data that is collected within the new functions that are described here is stored in the SYSAUX tablespace. To determine which components require most space in the SYSAUX tablespace, you can enter the following query:

SELECT * FROM
( SELECT
    OCCUPANT_DESC,
    SPACE_USAGE_KBYTES
  FROM
    V$SYSAUX_OCCUPANTS
  ORDER BY
    SPACE_USAGE_KBYTES DESC)
WHERE ROWNUM <=10;

For more information, see the Oracle documentation under:

Oracle 10g Database Performance Tuning Guide
-> 5 Automatic Performance Statistics
-> Automatic Workload Repository

Oracle 10g Database Performance Tuning Guide
-> 6 Automatic Performance Diagnostics
-> Automatic Database Diagnostic Monitor
Solution

1. Access to historical monitoring data

              ASH shows the last non idle-wait events and CPU usage of Oracle session in the V$ACTIVE_SESSION_HISTORY view. You can also link this information with the corresponding SQL statement and object.
              Different DBA_HIST views, which allow you to analyze past periods, are available based on the AWR data. The names correspond to the standard performance views to a large extent, with the difference that "V$" is replaced by "DBA_HIST_". The following views are especially relevant from a performance point of view:

• DBA_HIST_FILESTATXS: Data file accesses

• DBA_HIST_TEMPSTATXS: Tempfile accesses

• DBA_HIST_SYSTEM_EVENT: Accumulated Wait Events

• DBA_HIST_WAITSTAT: Buffer Busy Waits

• DBA_HIST_ENQUEUE_STATS: Enqueue Waits

• DBA_HIST_LATCH: Latch Waits

• DBA_HIST_SYSSTAT: System-wide statistical information

• DBA_HIST_ACTIVE_SESS_HISTORY: Snapshots of the wait event and the CPU consumption of active sessions

• DBA_HIST_SQLBIND: bind variable content

• DBA_HIST_SQL_PLAN: Execution plans

• DBA_HIST_OSSTAT: CPU information and memory information

              These views each contain a SNAP_ID column that uniquely identifies a system snapshot. Every hour a snapshot is created as standard. You can use DBA_HIST_SNAPSHOT to determine which time interval the individual SNAP_IDs cover:

SELECT SNAP_ID, SUBSTR(BEGIN_INTERVAL_TIME, 1, 30),
SUBSTR(END_INTERVAL_TIME, 1, 30)
FROM DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID;

2. Creation of an ASH overview report (Oracle 10.2 or higher)

              When you run an ASH overview report for a selected period, you receive the following information:

• Top Wait Events (incl. P1- /P2- /P3-parameter)

• SQL statements for the top activities

• Objects for the top activities

• Top activities for sub-intervals in the selected period

              Proceed as follows to create such a report:

• Change into the rdbms/admin directory below the Oracle home directory.

• Log on to the database with "sqlplus/as sysdba".

• Run the ASHRPT script to create the report by entering:@ashrpt

• Now enter "html" or "text", depending on the desired output format.

• Enter the start time of the period.

• Enter the end time of the period.

• If necessary, enter the name of the result report.

              If you enter a period for which no sample values exist, (for example, because these were overwritten by more recent data), the report creation terminates with:

ORA-20500: Could not find ASH Samples for the given dbid

3. Creating an AWR overview report

              Within the AWR, snapshots of the system status are created automatically each hour. Based on any two snapshots, a report can be created that gives an overview of the database activities in the period between the snapshots. This report can optionally be displayed in text format or HTML format. Such an AWR report is an enhancement of the Statspack report, which is also available with previous releases (see Note 717484).
              Proceed as follows to create such a report:

• Change into the rdbms/admin directory below the Oracle home directory.

• Log on to the database with "sqlplus/as sysdba".

• Execute the AWRRPT script to create the report by entering: @awrrpt

• Now enter "html" or "text", depending on the desired output format.

• The system may then query you as to the number of days for which the available snapshots should be displayed. Here, enter the number of days to be displayed.

• Now enter the number of the original snapshot.

• Then select the number of the final snapshot.

• If requested, enter a name for the report. If you only select RETURN, a report is generated with the displayed name.

• Now an AWR report is generated in the current directory, which then can be displayed, depending on the format, with a Web browser or a text editor.

              Among other things, the AWR report contains the following performance-relevant information:

• Size of the memory areas

• Load values (for example, number of disk reads/seconds)

• Hit ratios

• Non-Idle-Wait events that have occurred

• Top SQL statements with respect to elapsed time, CPU time, buffer gets, disk reads, number of executions, parses and sharable memory

• Instance activities (V$SYSSTAT info)

• Tablespace and date file I/O information

• Buffer busy wait statistics

• Enqueue statistics

• Latch statistics

• Segment statistics

• Profile parameters

              If the hourly snapshots are not sufficient on an individual basis, you can use

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
              a manual snapshot (for example immediately before starting and after finishing the activity to be analyzed).

4. Creating an AWR diff report

              An AWR diff report allows you to compare AWR data in two different time periods. Such a report is particularly useful if the performance has deteriorated compared to the performance during a past period, even though the processed load is comparable.
              Proceed as follows to create such a report:

• Change into the rdbms/admin directory below the Oracle home directory.

• Log on to the database with "sqlplus/as sysdba".

• Execute the AWRDDRPT script to create the report by entering:
  @awrddrpt

• Now enter "html" or "text", depending on the desired output format.

• The system may then query you as to the number of days for which the available snapshots should be displayed. Enter the number the number of days to be displayed for the selection of the first interval.

• Now enter the number of the original snapshot of the first interval.

• Then select the number of the final snapshot of the first interval.

• Repeat the last three steps for the second interval that is to be compared.

• If requested, enter a name for the report. If you only select RETURN, a report is generated with the displayed name.

• Now an AWR diff report is generated in the current directory, which then can be displayed, depending on the format, with a Web browser or a text editor.

              The AWR diff report contains the same information as a normal AWR report. However, the system always compares the values of both time periods that are being looked at.

5. Create an ADDM report

              On the basis of the AWR data, you can now create an ADDM report that contains automated tuning recommendations and an overview of non-critical areas. To do this, proceed as follows:

• Change into the rdbms/admin directory below the Oracle home directory.

• Log on to the database with "sqlplus/as sysdba".

• Execute the ADDMRPT script for creating a report by entering: @addmrpt

• Now enter the number of the original snapshot.

• Then select the number of the final snapshot.

• If requested, enter a name for the report. If you only select RETURN, a report is generated with the displayed name.

• Now an ADDM report is generated in the current directory which contains optimization options on the one hand, and an overview of non-critical areas on the other hand. This report represents a good starting-point for optimizing the database performance.

6. Analyzing the database using the Segment Advisor

              For more detailed information, see Note 927813.

7. Creating an AWR SQL report

              You can create an AWR SQL report for an individual SQL statement and the report contains such as the SQL statement itself, the load information (for example, disk reads, buffer gets, elapsed time) and the execution plan.
              To create an AWR SQL report, proceed as follows:

• Log on to the database with "sqlplus / as sysdba". Call the following to start the AWRSQRPT script:
  
  @awrsqrpt

• Now enter "html" or "text", depending on the desired output format.

• The system may then query you as to the number of days for which the available snapshots should be displayed. Here, enter the number of days to be displayed.

• Now enter the number of the original snapshot of the first interval.

• Then select the number of the final snapshot of the first interval.

• Enter the SQL_ID of the SQL statement that is to be analyzed.

• If required, enter an alternative report name.

• Now an AWR SQL report is generated in the current directory, which then can be displayed, depending on the format, with a Web browser or a text editor.

8. Analyzing an SQL statement using the SWL Tuning Advisor

• Change into the rdbms/admin directory below the Oracle home directory.

• Log on to the database with "sqlplus / as sysdba".

• Execute the SQLTRPT script for creating a report by entering: @sqltrpt

• Enter the SQL_ID of the SQL statement to be analyzed.

• You now receive an output containing analysis details and recommendations.

9. Analyzing an SQL statement using the SQL Access Advisor

              You can use the SQL Access Advisor to analyze many SQL statementswith regards to index design. There is no simple SQLPLUS interface available for this function. You must use the DBMS_ADVISOR functions instead. Furthermore, the SQL Access Advisor often does not display any additional information in comparison with the SQL Tuning Advisor. Therefore, a more precise description of this Advisor is omitted at this point.

10. Manual display of Advisor results

              You can access results of Advisor runs directly using different DBA views:

• DBA_ADVISOR_FINDINGS: Results

• DBA_ADVISOR_RECOMMENDATIONS: Recommendations

• DBA_ADVISOR_OBJECTS: Affected objects

• DBA_ADVISOR_ACTIONS: Recommended actions

              You can use the following queries to determine results and recommendations of the last hour:

SELECT
  TO_CHAR(AT.EXECUTION_END, 'dd.mm.yyyy hh24:mi:ss') "DATE",
  AF.MESSAGE FINDING,
  AA.MESSAGE ACTION
FROM
  DBA_ADVISOR_TASKS AT,
  DBA_ADVISOR_FINDINGS AF,
  DBA_ADVISOR_RECOMMENDATIONS AR,
  DBA_ADVISOR_ACTIONS AA
WHERE
  AT.OWNER = AF.OWNER AND
  AT.TASK_ID = AF.TASK_ID AND
  AF.OWNER = AR.OWNER (+) AND
  AF.TASK_ID = AR.TASK_ID (+) AND
  AF.FINDING_ID = AR.FINDING_ID (+) AND
  AR.OWNER = AA.OWNER (+) AND
  AR.TASK_ID = AA.TASK_ID (+) AND
  AR.REC_ID = AA.REC_ID (+) AND
  AT.EXECUTION_END > SYSDATE - 1 / 24
ORDER BY AT.EXECUTION_END DESC, AF.FINDING_ID, AR.REC_ID;

Header Data

Release Status:	Released for Customer
Released on:	05.03.2009  08:19:34
Master Language:	German
Priority:	Recommendations/additional info
Category:	Performance
Primary Component:	BC-DB-ORA Oracle

Affected Releases

Release-Independent

Related Notes

	1462435 - Composite SAP note: Variations between ASH and AWR
	927813 - Oracle 10g: Using Oracle Segment Advisor to optimize space
	766349 - FAQ: Oracle SQL optimization
	740897 - Info about the Oracle license scope; Required Oracle options
	717484 - FAQs: Oracle statspack
	619188 - FAQ: Oracle wait events
	618868 - FAQ: Oracle performance
Print Selected Notes (PDF)