Performance Tuning Using AWR, ASH and ADDM
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.
Performance Tuning for Oracle 10g/11g database
In Oracle 10g and onwards, the database by default takes Automatic Workload Repository (AWR) snapshots of the system every 60 minutes.
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).
ADDM uses data captured by AWR. e.g wait events, latches, locking, statistics and ASH data to identify the root causes of the problems.
ADDM also provides recommendations and tells expected benefits.
ADDM uses AWR snapshots to 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.
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.
AWR & ADDM
AWR captures rich and complex set of database performance statistics which is used by performance advisors and ADDM.
Oracle recommends using ADDM to perform the performance analysis for you rather than analyzing every new statistic and metric available manually.
- To enable new statistics gathering and advisors, ensure that the parameter STATISTICS_LEVEL is set to TYPICAL (recommended/default) or ALL.
- Statistics_level =ALL should not be set, especially on production database.
- It may cause high CPU utilization and slowness.
- It should only be set on test boxes, under recommendation of Oracle support, or be set for specific session to debug specific issues.
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 a dedicated background process (MMON).
- AWR snapshots collection is automatic.
- AWR automatically deletes old AWR snapshots (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:
Automatic Workload Repository Views
Use OEM or AWR reports to view AWR data.
DBA can also view the statistics with the following views:
- V$ACTIVE_SESSION_HISTORY – view displays active database session activity, sampled once every second.
- DBA_HIST views – contain historical data stored in the database.
This group of views includes:
- DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history for recent system activity.
- DBA_HIST_SNAPSHOT displays information on snapshots in the system
- DBA_HIST_BASELINE displays information about the baselines AWR snapshots captured.
- DBA_HIST_DATABASE_INSTANCE displays information about the database environment
- DBA_HIST_SQL_PLAN displays the SQL execution plans
- DBA_HIST_WR_CONTROL displays the settings for controlling AWR
- select * from V$ACTIVE_SESSION_HISTORY
- select * from DBA_HIST_ACTIVE_SESS_HISTORY
- select * from DBA_HIST_SNAPSHOT
- select * from DBA_HIST_BASELINE
- select * from DBA_HIST_DATABASE_INSTANCE
- select * from DBA_HIST_SQL_PLAN
- select * from DBA_HIST_WR_CONTROL
- Use OEM or TOAD to generate AWR reports.
- You can also generate AWR reports by running following SQL scripts present in $ORACLE_HOME/rdbms/admin folder
- The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids. If you have 8 node RAC database you need to run awrrpt.sql 8 times, once for each RAC instance.
- The awrgrpt.sql (AWR Global Report) SQL script generates an HTML or text report that displays statistics for all RAC nodes/instances in one Report for a range of snapshot Ids. This report is very useful for RAC environments as it gives a summarized information for all RAC instances.
- The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
- The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
Generate AWR reports by running
$ sqlplus / as sysdba
- SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
- SQL> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
- SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql
- SQL > @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
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).
1 out of10 samples of the ASH data are saved permanently in AWR repository. This information provides ADDM with the data to drill-down on problems identified.
If database has excessive “db file scattered read” , the ASH data helps the advisor identify the specific blocks and files that are read most when the wait event “db file scattered read” was present.
Use Active Session History (ASH) reports to perform analysis of Recent/Transient performance problems that typically last for a few minutes
You can view ASH reports using Enterprise Manager or by running the following SQL scripts:
- The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration.
- 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.
Generate ASH reports by running
$ sqlplus / as sysdba
- SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
- SQL> @$ORACLE_HOME/rdbms/admin/ashrpti.sql
Automatic Database Diagnostic Monitor (ADDM)
ADDM is a powerful self-diagnostic and Tuning engine built directly into Oracle database.
Using the AWR infrastructure, ADDM is able to analyze the system, identify the major problem in the system and recommend corrective action
ADDM if required tells DBAs to run other Oracle Advisors like Sql Tuning Advisors or Sql Access Advisors.
ADDM is invoked automatically every time a new AWR snapshot is generated (by default every 60 minutes)
ADDM analysis results are stored in the advisory framework tagged with the snapshot id.
DBA can find historical executions of the advisor and its recommendations.
ADDM can be invoked manually for a different set of snapshots by running addmrpt.sql script and entering any 2 AWR Snapshots.
$ sqlplus / as sysdba
select * from dba_objects where object_name like ‘%DBA_ADVISOR%’
select * from DBA_ADVISOR_TASKS
select * from DBA_ADVISOR_LOG
select * from DBA_ADVISOR_FINDINGS
select * from DBA_ADVISOR_RECOMMENDATIONS
select * from DBA_ADVISOR_ACTIONS
select * from DBA_ADVISOR_RATIONALE
Sample 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])
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.
SQL Tuning Advisor
SQL Tuning Advisor analyzes individual SQL statements, and recommend SQL profiles, statistics, indexes, and restructured SQL to SQL performance.
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 Bad Sql Plan.
In addition to this “normal” mode of the optimizer, it can also be run 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.