Performance Tuning Using Advisors and Manageability Features: AWR, ASH, ADDM (文档 ID 276103.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
10.1.0.2 to 10.2.0.5 [Release: 10.1 to 10.2]

PURPOSE

Purpose: 
The purpose of this article is to illustrate how to use Automatic Workload Repository (AWR) manageability features to diagnose and resolve performance problems in the Oracle Database. 
From Oracle10g onwards, the  Automatic Workload Repository has provided powerful tools to help the DBA identify and resolve performance issues without the hassle of analyzing complex statistical data and extensive reports. 

To take greater advantage of these features, and to simplify the visualization of the information provided, use of the Enterprise Manager console is strongly recommended to

SCOPE

This document is intended for DBAs.

DETAILS

Performance Tuning Approach: Pre-Oracle10g

In Oracle releases previous to Oracle10g (Oracle9i and before), the process of tuning the database was complex and time consuming. Generally speaking, when a performance problem occured a DBA could access Oracle performance statistics using tools such as STATSPACK. This data would be examined to identify where the time is being  spent in the database (Comparing data for the time that sessions are waiting versus the time that sessions are processing). Depending on the top "event" identified, additional  drill down analysis is typically required to understand the underlying causes of the problem and eventually identify the root cause.

Document 94224.1 FAQ- Statspack Complete Reference
Document  394937.1 Statistics Package (STATSPACK) Guide

Document 149113.1  Installing and Configuring StatsPack Package
Document 149121.1 Gathering a StatsPack snapshot
Document 228913.1 Systemwide Tuning using STATSPACK Reports

http://www.oracle.com/technetwork/database/focus-areas/performance/statspack-opm4-134117.pdf

There are several reasons why analyzing performance problems manually using statspack is complex and time consuming:

 

• The Oracle database has hundreds of wait events, latches, enqueues, buffer busy classes, statistics and metrics that are used to report the time spent by a transaction. Certain wait events may appear for different operations and in different situations. Broad technical knowledge and expertise is required to identify what is the most probable cause for the database to be waiting on a particular event.
• The most common way to store historical information regarding the performance of the database is by using the Statspack repository which is a time interval based tool. Therefore, while analyzing a poor performance situation the DBA is restricted to the information gathered (lets say for illustration 60 minutes between snapshots). In a very transactional environment with thousands of users working simultaneously there is a high probability of data skew where a performance peak may not be visible.
• Once the possible problem(s) are identified, it is very common that the initial information gathered using the Statspack repository is not sufficient to confirm and resolve the problem. Therefore, the DBA must prepare a set of additional gathering tools and scripts so the next time the problem reproduces it can be confirmed and resolved. Chances are that this step must be repeated a couple of times before the issue is confirmed or to move one-step further in the performance analysis.

The following example shows the pre-Oracle10g approach to tune the database:
"

The Statspack report shows the "db file scattered read" wait event on top of the list of time consumers. There may be 3 or 4 possible causes for that problem (e.g. SQL Tuning issues, lack of indexes, IO contention, etc). Using the STATSPACK report the DBA verifies that the Avg. Read Time for the data files and tablespaces is acceptable (between 20 and 30 ms) but he/she noticed that there are some queries with high number of Buffer Gets and Physical Reads. Using the information available, the next step would be to understand why the query is requesting the large amount of buffers. Once the application that launches the query is identified (some times difficult), and once it is clear that the query(s) are permanently causing problems (as opposed of isolated queries that may not worth the tuning effort immediately), there may be some possible cause(s) for this scenario that can be investigated:

• The statistics of the tables and indexes involved have not been refreshed recently
• There are missing indexes causing unnecessary full scans
• The query must be tuned to eliminate or reduce full scan operations

The first one can be easily identified by looking at the LAST_ANALYZED column in the dictionary views USER_TABLES and DBA_TABLES. However, the sampling volume used may have some effects in the optimization. The second one can be tackled using Tkprof to identify in what part of the execution plan the higher number of buffers are being requested and apply corrective actions like creating a new index (not that easy as it sounds). If the first two options don't result in the expected performance, query tuning is required. Query tuning is not only complex and hard to achieve, but the implementation in existing applications is sometimes not feasible and requires testing. Thus, even when there is an improved query the implementation can take months

"

Performance Tuning Approach: Oracle10g Onwards

In Oracle 10g and onwards, the database by default takes Automatic Workload Repository (AWR) snapshots of the system every 60 minutes (using a mix of SQL and C-coded kernel calls). These automatic snapshots include the results of the execution of the Automatic Database Diagnostic Monitor (ADDM), and historical data of the sessions that waited for non-idle wait events or on the CPU during the last 60 minutes (Active Session History - ASH). When executed, ADDM uses statistical data captured by AWR. This includes wait events, latches, enqueues, statistics, the NEW TIME MODEL information, the auto-generated metrics, and ASH data  to identify the most probable root causes of the problems identified rather than just identifying the symptoms. In addition ADDM provides recommendations and qualifies expected benefits.

If we take the previous example once again, but now within the AWR infrastructure, ADDM would find out SQL statements that are requesting large amounts of buffers and performing a lot of IO requests, and would then suggest executing the SQL Tuning Advisor (STA) for that statement, which in turn will identify whether the problem is caused by the lack of statistics or by the lack of indexes. If required, this advisor will provide an alternate and improved execution plan that can be saved in the data dictionary using SQL profiles. Therefore, the application doesn't have to be modified and the next time the statement is executed the new execution plan is used on the fly. Since Oracle maintains historical data, fewer analysis cycles may be required to improve the performance reducing the time and complexity of the analysis.

Additionally, with the default settings, Oracle automatically gathers statistics on any objects that have important changes in size or structure. this makes it less likely that objects will be missing statistics.

Current Infrastructure (AWR, Time Model, ASH) (Post 10g)

With AWR a rich and complex set of database performance statistics are capture which can then be used by performance advisors and the performance engine of ADDM. Oracle recommends using ADDM to perform the performance analysis for you rather than analyzing every new statistic and metric available manually.

NOTE: 

To enable most of the new statistical gathering and advisors, ensure that the parameter STATISTICS_LEVEL is set to TYPICAL (recommended) or ALL.
Statistics_level =ALL should not be set,specially on production box. It may cause high cpu utilzation and slowness. It should only be set on test boxes, under recommedation of Oracle support, or be set for specific session to debug specific issues. 
See:

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 5 Automatic Performance Statistics
http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#i35168

Document 250655.1 How to use the Automatic Database Diagnostic Monitor

Use of the Automatic Workload Repository and related features requires licenses for Oracle Diagnostics Pack which provides automatic performance diagnostic and advanced system monitoring functionality.  See:

Oracle® Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 2 Options and Packs
Oracle Diagnostics Pack 
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC165

Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) is an infrastructure that provides information to different manageability components. AWR consists of two components: in-memory statistics accessible through V$ dynamic views, and AWR snapshots saved in the database that represent the persistent and historical portion. Some important things must be highlighted:

• AWR data is flushed from memory to disk using internal kernel calls and a dedicated background process (MMON).
• Data collecting doesn't require manual configuration, like Statspack snapshots in previous releases.
• AWR provides automatic purging of data (7 days by default)
• AWR snapshots can be preserved so that they are not purged. (This can be used to store baselines for further comparisons)

AWR snapshots can be generated at will using the following syntax: 

EXECUTE dbms_workload_repository.create_snapshot();

Time Model

In Oracle10g and onwards, the database introduces a different way of store statistics and metrics regarding the time consumed by the different sessions connected. This data is key in a speedy analysis of performance problems, providing a different perspective on time usage within the database.  

The following describes these characteristics in detail:

• Time Model
  
  With the new time model the time of every operation is stored in a bucket of time. Thus, one can identify either at database level or at session level how the time distribution looks. Examples of these buckets are: "DB CPU", "DB Time", "background elapsed time", "sql execute elapsed time", etc. See the following views for more details: 
  
  
  V$SYS_TIME_MODEL 
  V$SESS_TIME_MODEL
• Wait Classes
  
  To enable easier high-level analysis of the wait events and allow a more accurate diagnosis, the time events are grouped now by "wait classes" based on the solution space that applies to fixing a problem with that wait event. See the following views for more details:
  
  
  V$SYSTEM_WAIT_CLASS
  V$SESSION_WAIT_CLASS
• Metrics
  
  Metrics are automatically calculated based on basic statistics to track the rates of changes in the activity of the Database. Most of the metrics are calculated every 60 seconds. A good example of metrics is the frequently used "cache hit ratios". In releases of Oracle previous of 10g, cache hit ratios had to be manually calculated using formulas that eventually may change from release to release. Cache hit ratios are now calculated and stored in memory. See the following views for more details: 
  
  
  V$SYSMETRIC
  V$SESSMETRIC
  V$FILEMETRIC
  V$EVENTMETRIC
  V$WAITCLASSMETRIC
  V$METRICNAME
• OS Statistics
  
  CPU and memory statistics are gathered by default. This helps ADDM determine how the database activity is related to the bottleneck found. See the following views for more details: 
  
  
  V$OSSTAT
• Active Session History (ASH)
  
  One of the components of the AWR repository is the Active Session History (ASH) which 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). When saved in the AWR repository by default, 1 in 10 samples of the ASH data are saved persistently. This information provides ADDM with the data to drill-down on problems identified. In the "db file scattered read" example provided, the ASH data helps the advisor identify the specific blocks and files that are referenced the most when the wait event was present.
  
  
  V$ACTIVE_SESSION_HISTORY
  DBA_HIST_ACTIVE_SESS_HISTORY

 

Performance Tuning Advisors

Automatic Database Diagnostic Monitor (ADDM)

ADDM is a powerful self-diagnostic engine built directly into the kernel of the Oracle database. Using the AWR infrastructure, ADDM is able to holistically analyze the system, identify the major problem in the system (not the symptoms),  and recommend corrective action (which some times may require use of a separate Oracle10g advisor). To focus the attention on the actual problems, ADDM also shows the areas of the system that have no problems.

ADDM is invoked automatically every time that a new AWR snapshot is generated (by default every 60 minutes) and the results of the analysis are stored in the advisory framework tagged with the snapshot id. Therefore, the DBA can find historical executions of the advisor and its recommendations. ADDM can be invoked manually for a different set of snapshots by using the ADDM report generator bundled with the software. To generate a new ADDM report, execute the following script and pick the sample interval for the analysis:

$ORACLE_HOME/rdbms/admin/addmrpt.sql

The following is an excerpt of an ADDM report:

FINDING 1: 28% impact (97 seconds)
----------------------------------
Individual database segments responsible for significant user I/O wait were found.

   RECOMMENDATION 1: Segment Tuning, 28% benefit (97 seconds)
      ACTION: Run "Segment Advisor" on TABLE "SCOTT.T" with object id 51924.
         RELEVANT OBJECT: database object with id 51924
      ACTION: Investigate application logic involving I/O on TABLE "SCOTT.T" with object id 51924.
         RELEVANT OBJECT: database object with id 51924
      RATIONALE: The SQL statement with SQL_ID "5328shb1qxs6u" spent
         significant time waiting for User I/O on the hot object.
         RELEVANT OBJECT: SQL statement with SQL_ID 5328shb1qxs6u
         insert into a select * from t

   SYMPTOMS THAT LED TO THE FINDING:
      Wait class "User I/O" was consuming significant database time. (30%  impact [105 seconds])

ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.

See:

Document 250655.1  How to use the Automatic Database Diagnostic Monitor 

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 6 Automatic Performance Diagnostics
http://docs.oracle.com/cd/E11882_01/server.112/e16638/diag.htm

SQL Tuning Advisor

The optimizer uses the current object's statistics and the SQL structure to generate the execution plan for a SQL statement. It is important that the optimizer determines the execution plan in a timely manner and that the execution of the plan for the statement performs well. Therefore, if the statistics don't reflect the real characteristics of the objects, or the query is poorly written, or there are access structures missing (e.g. indexes) the optimizer may generate a suboptimal plan.

In addition to this "normal" mode of the optimizer, it can also be called in "tuning mode" using the SQL Tuning Advisor (STA). In the tuning mode the optimizer has plenty of time and resources to identify the BEST plan available. When statistics are not available, or when access path cannot be used because lack of indexes defined, the STA is able to collect auxiliary statistics on the objects involved or simulate new execution plan assuming the existence of indexes.  Specifically the STA verify the following to generate the best execution plan:

• Detect stale or missing statistics (Statistics check optimization mode): Verify validity of statistics and generate auxiliary statistics as needed to compensate deviations.
• Determine a new execution plan and create a SQL profile (Plan Tuning optimization mode): Verify cost, selectivity and cardinality using dynamic sampling. This ensures that actual execution reflects the calculations made by the optimizer.
• Detect missing indexes(Access analysis optimization mode):  Determine the need to create, modify or eliminate access structures (indexes, materialized views)
• Restructure SQL(SQL analysis optimization mode): Identify SQL structure modifications that may lead to a better execution plan.

As a result the SQL Tuning Advisor may provide suggestions is terms of: refreshing object statistics or creating access objects. It can also suggest creating a SQL Profile for the statement when a better plan is identified. Once the SQL profile is created and "applied" to the system this new plan will be used AUTOMATICALLY every time that the statement is executed without having to modify the application.

Note: Use of the SQL Tuning Advisor requires the Oracle Tuning Pack License:

Oracle® Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 2 Options and Packs
Oracle Tuning Pack
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC170

The easiest way to take advantage of SQL Tuning Advisor is using the Enterprise Manager console, however it can also be invoked using the DBMS_SQLTUNE package. The following is an excerpt of the results provided by the SQL Tuning Advisor:

--------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task10
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/09/2004 21:30:22
Completed at       : 06/09/2004 21:30:23

-------------------------------------------------------------------------------
SQL ID  : b296symum0xk1
SQL Text: SELECT /*+ ORDERED */ * FROM employees e, locations l, departments
          d WHERE e.department_id = d.department_id AND l.location_id =
          d.location_id AND e.employee_id < 10

-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
  Table "CLASS6"."DEPARTMENTS" and its indices were not analyzed.

  Recommendation
  --------------
    Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'CLASS6', tabname =>
            'DEPARTMENTS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.
....
4- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 87.1%)
  -----------------------------------------
    Consider accepting the recommended SQL profile.
    execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>
                             'my_sql_tuning_task10')

5- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.

  Recommendation
  --------------
    Consider removing the "ORDERED" hint.

  Rationale
  ---------
    The "ORDERED" hint might force the optimizer to generate a cartesian
    product. A cartesian product should be avoided whenever possible because
    it is an expensive operation and might produce a large amount of data.

See:

Document 262687.1  How to use the Sql Tuning Advisor.
Document 271196.1  Automatic SQL Tuning - SQL Profiles.

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 17 Automatic SQL Tuning
http://docs.oracle.com/cd/E11882_01/server.112/e16638/sql_tune.htm#i22019

SQL Access Advisor

The SQL Access Advisor identifies possible access paths to the data using indexes or materialized viewes to improve the performance of access to the data. The SQL Access Advisor takes an actual workload input (or it can derive an hypothetical one) and recommends the access structures needed for a faster execution. To identify SQL statements that can benefit from having better access paths the SQL Access Advisor can take the workload directly from the SQL Cache (V$SQL), or from user-defined workloads (in the form of input tables or SQL Tuning Sets). This advisor take the following into consideration:

• Simultaneous effect of using indexes, materialized views, or the combination of both
• Storage creation parameters
• Combination of single indexes into one index
• Dropping unused indexes
• Modifying existing indexes

See:

Document 259188.1 Oracle10g: Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 18 SQL Access Advisor
http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm 

Note: Use of the SQL Access Advisor requires the Oracle Tuning Pack License:

Oracle® Database Licensing Information
11g Release 2 (11.2)
Part Number E10594-18
Chapter 2 Options and Packs
Oracle Tuning Pack License
http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#DBLIC170

Reporting

If reporting is still required then you should use AWR reports instead of statspack

Under some circumstances understanding the underlying statistics may be desirable and the reports bundled with the database can be used for that purpose. AWR provides a comprehensive report similar to the Statspack report, but with additional information not available in Statspack. STATSPACK is supported and still available in Oracle 10g. In addition, the AWR report has the following advantages over Statspack:

• The repository is created and maintained automatically
• The report can be generated in text or html mode
• The report displays additional information regarding wait classes, metrics, OS stats, etc.

To generate an AWR report simply execute the following script and pick the two snapshots you want to use for the sample (Oracle recommends using snapshots whose timestamps are no more than 60 minutes apart from each other): 

$ORACLE_HOME/rdbms/admin/awrrpt.sql

You will also be asked for the format of the report (text or html) along with the report name.
See:

Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point

 

REFERENCES

NOTE:762185.1 - Oracle University Offerings Related to Database Manageability
NOTE:748642.1 - How to Generate an AWR Report and Create Baselines
NOTE:1361401.1 - Where to Find Information About Performance Related Features
NOTE:250655.1 - How to use the Automatic Database Diagnostic Monitor
NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor
NOTE:259188.1 - Oracle10g: Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository
NOTE:265831.1 - Automatic Checkpoint Tuning in Oracle 10g
NOTE:271196.1 - Automatic SQL Tuning - SQL Profiles

相关内容

产品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS

关键字

AWR; DATABASE DIAGNOSTIC MONITOR; MONITOR; STATSPACK