Using STATSPACK and AWR for SQL tuning Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

In the previous section, it was explained how dynamic performance views provide a means of identifying problem areas within the database.  It is definitely worth paying attention to this method, but perhaps a simpler solution is to use STATSPACK.

Oracle 8.1.6 introduced STATSPACK as a replacement for the utlbstat.sql/utlestat.sql scripts.  Along with additional reporting capabilities, STATSPACK enabled the storage of snapshots of system statistics for the first time, allowing greater accuracy and flexibility in performance monitoring.  STATSPACK reports contain lots of information relevant to instance tuning, but it also contains several sections related to resource intensive SQL and PL/SQL including:

• SQL ordered by Gets

• SQL ordered by Reads

• SQL ordered by Executions

• SQL ordered by Parse Calls

Regularly checking STATSPACK reports allows the early identification of problem SQL and PL/SQL, enabling tuning efforts to be focused on those areas that will yield the greatest returns.

STATSPACK is not installed by default, but all the necessary scripts are located in the $ORACLE_HOME/rdbms/admin/ directory, including the spdoc.txt script that contains information about the installation and usage of STATSPACK.  The installation of STATSPACK creates a new user called perfstat, which owns all the schema objects associated with STATSPACK, including the STATSPACK package.  The installation is initiated by running the spcreate.sql script as the SYS user.

Once the installation is complete, a snapshot of the system statistics can be taken by connecting to the perfstat user and executing the snap procedure.

SQL> CONN perfstat/perfstat
Connected.

SQL> EXEC STATSPACK.snap; 

After a period of time another system snapshot can be taken, giving a potential start and end point for the analysis.  The advantage over the utlbstat.sql/utlestat .sql scripts is that there is no set start or end point for the analysis. Multiple snapshots can be taken and used for a start and end point.

The collection of system snapshots can be automated with the DBMS_JOB or dbms_scheduler packages.  The spauto.sql script can be used to schedule system snapshot collections on the hour, every hour.

The sppurge.sql script is used to delete a range of snapshots by prompting for a start and end snapshot, allowing the cleanup of snapshots that are no longer needed.

Once two or more snapshots are present, the spreport.sql script can be used to generate a STATSPACK report that displays change in the statistics over the analysis period.  The script prompts for the start and end snapshots along with a filename for the output report.

The next section will explain the use of the AWR reports in Oracle 10g.

Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) was introduced in Oracle 10g Enterprise Edition Performance Pack, and consists of a collection of performance statistics including:

• Wait events used to identify performance problems.

• Time model statistics indicating the amount of DB time associated with a process from the v$sess_time_model and v$sys_time_model views.

• Active Session History (ASH) statistics from the v$active_session_history view.

• Some system and session statistics from the v$sysstat and v$sesstat views.

• Object usage statistics.

• Resource intensive SQL and PL/SQL.

The resource intensive SQL and PL/SQL section of the report can be used to focus tuning efforts on those areas that will yield the greatest returns.  The statements are ordered by several criteria including:

• SQL ordered by Elapsed Time

• SQL ordered by CPU Time

• SQL ordered by Gets

• SQL ordered by Reads

• SQL ordered by Executions

• SQL ordered by Parse Calls

• SQL ordered by Sharable Memory

Several of the automatic database tuning features require information from the AWR to function correctly, including:

• Automatic Database Diagnostic Monitor

• SQL Tuning Advisor

• Undo Advisor

• Segment Advisor

Access to the AWR configuration and contents is available via the dbms_workload_repository package.  By default snapshots of the relevant data are taken every hour and retained for seven days.  The default values for these settings can be altered using modify_snapshot_settings settings procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);   -- Minutes. Current value retained if NULL.
END;
/ 

Any changes to the configuration settings are reflected in the dba_hist_wr_control view.  Typically the retention period should capture at least one complete workload cycle, therefore if the system has monthly archive and loads, a one-month retention time would be more beneficial than the default of seven days.  An interval of "0" switches off the snapshot collection, which in turn stops much of the self-tuning functionality, and is not recommended.  Automatic collection is only possible if the statistics_level parameter is set to TYPICAL or ALL.  If the value is set to BASIC, manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using the create_snapshot and drop_snapshot_range procedures shown below.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; 

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22,
    high_snap_id => 32);
END;
/

Identification of existing snapshots is possible using the dba_hist_snapshot view.

A baseline is a pair of snapshots that represents a specific period of usage.  Once baselines are defined they can be used to compare current performance against similar periods in the past.  You may wish to create a baseline to represent a period of batch processing.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 210,
    end_snap_id   => 220,
    baseline_name => 'batch baseline');
END;
/

The snapshots associated with a baseline are retained until the baseline is explicitly deleted.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the dba_hist_baseline view.

The contents of the AWR can be queried using a number of views including:

• v$active_session_history - Displays the active session history (ASH) sampled every second.

• v$metric - Displays metric information.

• v$metricname - Displays the metrics associated with each metric group.

• v$metric_history - Displays historical metrics.

• v$metricgroup - Displays all metrics groups.

• dba_hist_active_sess_history - Displays the history contents of the active session history.

• dba_hist_baseline - Displays baseline information.

• dba_hist_database_instance - Displays database environment information.

• dba_hist_snapshot - Displays snapshot information.

• dba_hist_sql_plan - Displays SQL execution plans.

• dba_hist_wr_control - Displays AWR settings.

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql).  They are similar in format to STATSPACK reports and give the option of HTML or plain text formats.  The two reports give essentially the same output, but the awrrpti.sql allows the selection a single instance in RAC environments.  The scripts prompt for the report format (html or text); the start snapshot id, end snapshot id and the report filename.  The resulting report can be opened in a browser or text editor accordingly.

The automated workload repository administration tasks have been included in Enterprise Manager.  The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section.  The page allows AWR settings to be modified or snapshots to be managed without using the PL/SQL APIs.

The following section shows how ADDM reports are easier to use than AWR reports.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.