AWR ADDM ASH and Statspack Tuning

AWR ASH and ADDM

Performance Tuning Using  AWR, ASH, ADDM and Sql Tuning Advisor

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.

Use Enterprise Manager to view AWR 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:

EXECUTE dbms_workload_repository.create_snapshot();

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

Queries:

• 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

AWR Reports

• 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.

ASH Views

V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY

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

Performance Tuning Advisors

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

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Advisor Views

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

$ORACLE_HOME/rdbms/admin/addmrpt.sql

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])

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.

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.

SQL Tuning Advisor looks into following things to generate the best execution plan:

• Detect stale or missing statistics and generate auxiliary statistics to compensate for missing stats.
• Determine a new execution plan and create SQL profile.
• Detect missing indexes and suggest new indexes and materialized views.
• Restructure SQL to generate a better execution plan.

SQL Profiles

• SQL Profiles are Auxiliary Statistics specific to a SQL statement and are stored in the data dictionary.
• SQL profiles guides the query optimizer to use a better plan for a SQL statement.
• 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.

SQL Tuning Advisor can be started using

1. OEM
2. DBMS_SQLTUNE package.
3. Specify the SQL_ID of the sql you want to analyze using SQL Tuning Advisor

Sample SQL Tuning Advisor Output:

——————–
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.

SQL Access Advisor

SQL Access Advisor evaluates an entire workload of SQL statements and recommend indexes, partitions, materialized views that will improve the performance of the SQL workload.

The SQL Access Advisor identifies possible access paths to the data using indexes or materialized views 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 takes 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

Use OEM to start SQL Access Advisor

Time Model

In Oracle10g and onwards, the database introduces a different way of storing 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_MODE
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

Queries for practice

select * from V$OSSTAT

select * from V$SYS_TIME_MODEL

select * from V$SESS_TIME_MODEL

select * from V$SYSTEM_WAIT_CLASS

select * from V$SESSION_WAIT_CLASS

select * from V$SYSMETRIC

select * from V$SESSMETRIC

select * from V$FILEMETRIC

select * from V$EVENTMETRIC

select * from V$METRICNAME

select * from V$WAITCLASSMETRIC

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 as AWR and ADDM reports and Oracle Advisors were not available.

Previous to Oracle 10g, whenever a performance problem occurred 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

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.
• Only a expert DBAs were able to analyze the Statspack report to find the root cause and a fix for a performance issue.
• Statspack captures Database performance statistics at specified interval (e.g 60 minutes). If any performance issue occurred for a short duration of 5 minutes it will be difficult to track using Statspack report.
• Most of the time the information gathered by Statspack is not sufficient to confirm and resolve a problem. DBA has to create their own scripts to deal with such Performance issues.

Steps to configure STATSPACK 

Note: STATSPACK is not longer used by DBAs in 10g/11g database. AWR and ADDM reports are used in 10g/11g

STATSPACK scripts are present at $ORACLE_HOME/rdbms/admin

spcreate.sql  – To create Statspack user PERFSTAT

spauto.sql  – To run Statspack snapshot every hours

spreport.sql – To generate Statspack report.

cd $ORACLE_HOME/rdbms/admin

$ sqlplus / as sysdba

SQL>  @spcreate.sql

SQL> @spauto.sql

SQL> connect perfstat/perfstat

SQL> @spreport.sql

It will ask for Begin and End Snapshots to generate report.

Source: Keplerinfotech.com