Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g

This article describes several methods for producing reports from the Automatic Database Diagnostic Monitor (ADDM) in Oracle 10g.

• Overview
• Enterprise Manager
• addmrpt.sql Script
• DBMS_ADVISOR
• Related Views

Overview

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

There are several ways to produce reports from the ADDM analysis which will be explained later, but all follow the same format. The findings (problems) are listed in order of potential impact on database performance, along with recommendations to resolve the issue and the symptoms which lead to it’s discovery. An example from my test instance is shown below.

FINDING 1: 59% impact (944 seconds)
-----------------------------------
The buffer cache was undersized causing significant additional read I/O.

   RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)
      ACTION: Increase SGA target size by increasing the value of parameter
         "sga_target" by 28 M.

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

The recommendations may include:

• Hardware changes
• Database configuration changes
• Schema changes
• Application changes
• Using other advisors

The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microsoconds. The parameter can be set using the following.

EXECUTE DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);

Enterprise Manager

The obvious place to start viewing ADDM reports is Enterprise Manager. The “Performance Analysis” section on the “Home” page is a list of the top five findings from the last ADDM analysis task.

Specific reports can be produced by clicking on the “Advisor Central” link, then the “ADDM” link. The resulting page allows you to select a start and end snapshot, create an ADDM task and display the resulting report by clicking on a few links.

addmrpt.sql Script

The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows.

-- UNIX
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql

-- Windows
@d:oracleproduct10.1.0db_1rdbmsadminaddmrpt.sql

It then lists all available snapshots and prompts you to enter the start and end snapshot along with the report name.

DBMS_ADVISOR

The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including ADDM tasks. The following example shows how it is used to create, execute and display a typical ADDM report.

BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
    task_name         => '970_1032_AWR_SNAPSHOT',
    task_desc         => 'Advisor for snapshots 970 to 1032.');

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => '970_1032_AWR_SNAPSHOT',
    parameter => 'START_SNAPSHOT',
    value     => 970);

  DBMS_ADVISOR.set_task_parameter (
    task_name => '970_1032_AWR_SNAPSHOT',
    parameter => 'END_SNAPSHOT',
    value     => 1032);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');
END;
/

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM   dual;
SET PAGESIZE 24

The value for the SET LONG command should be adjusted to allow the whole report to be displayed.

The relevant AWR snapshots can be identified using the DBA_HIST_SNAPSHOT view.

Related Views

The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function.

• DBA_ADVISOR_TASKS – Basic information about existing tasks.
• DBA_ADVISOR_LOG – Status information about existing tasks.
• DBA_ADVISOR_FINDINGS – Findings identified for an existing task.
• DBA_ADVISOR_RECOMMENDATIONS – Recommendations for the problems identified by an existing task.

Automatic SQL Tuning in Oracle Database 10g

This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g:

• Overview
• SQL Tuning Advisor
• Managing SQL Profiles
• SQL Tuning Sets
• Useful Views

Overview

In its normal mode the query optimizer needs to make decisions about execution plans in a very short time. As a result it may not always be able to obtain enough information to make the best decision. Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.

In tuning mode the optimizer performs the following analysis:

• Statistics Analysis – The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
• SQL Profiling – The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it’s sensible to update profiles periodically. The SQL profiling is not performed when the tuining optimizer is run in limited mode.
• Access Path Analysis – The optimizer investigates the effect of new or modified indexes on the access path. It’s index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
• SQL Structure Analysis – The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.

The automatic SQL tuning features are accessible from Enterprise Manager on the “Advisor Central” page these or from PL/SQL using the DBMS_SQLTUNE package. This article will focus on the PL/SQL API as the Enterprise Manager interface is reasonably intuative.

SQL Tuning Advisor

In order to access the SQL tuning advisor API a user must be granted the ADVISOR privilege:

CONN sys/password AS SYSDBA
GRANT ADVISOR TO scott;
CONN scott/tiger

The first step when using the SQL tuning advisor is to create a new tuning task using the CREATE_TUNING_TASK function. The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning set or specified manually:

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from an SQL tuning set.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'scott',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

If the TASK_NAME parameter is specified it’s value is returned as the SQL tune task identifier. If ommitted a system generated name like “TASK_1478″ is returned. If the SCOPE parameter is set to scope_limited the SQL profiling analysis is omitted. The TIME_LIMIT parameter simply restricts the time the optimizer can spend compiling the recommendations.

The following examples will reference the last tuning set as it has no external dependancies other than the SCOTT schema. The NVL in the SQL statement was put in to provoke a reaction from the optimizer. In addition we can delete the statistics from one of the tables to provoke it even more:

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');

With the tuning task defined the next step is to execute it using the EXECUTE_TUNING_TASK procedure:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:

-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME                      STATUS
------------------------------ -----------
emp_dept_tuning_task           COMPLETED

1 row selected.

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24

In this case the output looks like this:

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : emp_dept_tuning_task
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/06/2004 09:29:13
Completed at       : 05/06/2004 09:29:15

-------------------------------------------------------------------------------
SQL ID  : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM   emp e JOIN dept d ON e.deptno = d.deptno
          WHERE  NVL(empno, '0') = :empno

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."EMP" and its indices were not analyzed.

  Recommendation
  --------------
    Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'EMP', 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.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
  contains an expression on indexed column "EMPNO". This expression prevents
  the optimizer from selecting indices on table "SCOTT"."EMP".

  Recommendation
  --------------
    Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

  Rationale
  ---------
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1863486531

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |   107 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |   107 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

-------------------------------------------------------------------------------

1 row selected.

Once the tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure:

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');
END;
/

Managing SQL Profiles

To manage SQL profiles a user needs the following privileges:

CONN sys/password AS SYSDBA
GRANT CREATE ANY SQL PROFILE TO scott;
GRANT DROP ANY SQL PROFILE TO scott;
GRANT ALTER ANY SQL PROFILE TO scott;
CONN scott/tiger

If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure:

SET SERVEROUTPUT ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(20);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                          task_name => 'emp_dept_tuning_task',
                          name      => 'emp_dept_profile');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

The NAME parameter is used to specify a name for the profile. If it is not specified a system generated name will be used.

The STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an SQL profile can be altered using the ALTER_SQL_PROFILE procedure:

BEGIN
  DBMS_SQLTUNE.alter_sql_profile (
    name            => 'emp_dept_profile',
    attribute_name  => 'STATUS',
    value           => 'DISABLED');
END;
/

Existing SQL profiles can be dropped using the DROP_SQL_PROFILE procedure:

BEGIN
  DBMS_SQLTUNE.drop_sql_profile (
    name   => 'emp_dept_profile',
    ignore => TRUE);
END;
/

The IGNORE parameter prevents errors being reported if the specified profile does not exist.

SQL Tuning Sets

An SQL tuning set is a group of statements along with their execution context. These can be created automatically via Enterprise Manager or manually provided you have the necessary privileges:

CONN sys/password AS SYSDBA
GRANT ADMINISTER ANY SQL TUNING SET TO scott;
CONN scott/tiger

An SQL tuning set is created using the CREATE_SQLSET procedure:

BEGIN
  DBMS_SQLTUNE.create_sqlset (
    sqlset_name  => 'test_sql_tuning_set',
    description  => 'A test SQL tuning set.');
END;
/

Statements are added to the set using the LOAD_SQLSET procedure which accepts a REF CURSOR of statements retrieved using one of the following pipelined functions:

• SELECT_WORKLOAD_REPOSITORY – Retrieves statements from the Automatic Workload Repository (AWR).
• SELECT_CURSOR_CACHE – Retrieves statements from the cursor cache.
• SELECT_SQLSET – Retrieves statements from another SQL tuning set.

The following are examples of their usage:

-- Load the SQL set from the Automatic Workload Repository (AWR).
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (
                    765,  -- begin_snap
                    766,  -- end_snap
                    NULL, -- basic_filter
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    10)   -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name     => 'test_sql_tuning_set',
    populate_cursor => l_cursor);
END;
/

-- Load the SQL set from the cursor cache.
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.select_cursor_cache (
                    NULL, -- basic_filter
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    1)    -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name     => 'test_sql_tuning_set',
    populate_cursor => l_cursor);
END;
/

-- Create a new set and load it from the existing one.
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  DBMS_SQLTUNE.create_sqlset(
    sqlset_name  => 'test_sql_tuning_set_2',
    description  => 'Another test SQL tuning set.');

  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.select_sqlset (
                    'test_sql_tuning_set', -- sqlset_name
                    NULL,                  -- basic_filter
                    NULL,                  -- object_filter
                    NULL,                  -- ranking_measure1
                    NULL,                  -- ranking_measure2
                    NULL,                  -- ranking_measure3
                    NULL,                  -- result_percentage
                    NULL)                  -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name     => 'test_sql_tuning_set_2',
    populate_cursor => l_cursor);
END;
/

The contents of an SQL tuning set can be displayed using the SELECT_SQLSET function:

SELECT *
FROM   TABLE(DBMS_SQLTUNE.select_sqlset ('test_sql_tuning_set'));

References can be added to a set to indicate its usage by a client using the ADD_SQLSET_REFERENCE function. The resulting reference ID can be used to remove it using the REMOVE_SQLSET_REFERENCE procedure:

DECLARE
  l_ref_id  NUMBER;
BEGIN
  -- Add a reference to a set.
  l_ref_id := DBMS_SQLTUNE.add_sqlset_reference (
    sqlset_name => 'test_sql_tuning_set',
    reference   => 'Used for manual tuning by SQL*Plus.');

  -- Delete the reference.
  DBMS_SQLTUNE.remove_sqlset_reference (
    sqlset_name  => 'test_sql_tuning_set',
    reference_id => l_ref_id);
END;
/

The UPDATE_SQLSET procedure is used to update specific string (MODULE and ACTION) and number (PRIORITY and PARSING_SCHEMA_ID) attributes of specific statements within a set:

BEGIN
  DBMS_SQLTUNE.update_sqlset (
    sqlset_name     => 'test_sql_tuning_set',
    sql_id          => '19v5guvsgcd1v',
    attribute_name  => 'ACTION',
    attribute_value => 'INSERT');
END;
/

The contents of a set can be trimmed down or deleted completely using the DELETE_SQLSET procedure:

BEGIN
  -- Delete statements with less than 50 executions.
  DBMS_SQLTUNE.delete_sqlset (
    sqlset_name  => 'test_sql_tuning_set',
    basic_filter => 'executions < 50');

  -- Delete all statements.
  DBMS_SQLTUNE.delete_sqlset (
    sqlset_name  => 'test_sql_tuning_set');
END;
/

Tuning sets can be dropped using the DROP_SQLSET procedure:

BEGIN
  DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set');
  DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set_2');
END;
/

Useful Views

Useful views related to automatic SQL tuning include:

• DBA_ADVISOR_TASKS
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_RECOMMENDATIONS
• DBA_ADVISOR_RATIONALE
• DBA_SQLTUNE_STATISTICS
• DBA_SQLTUNE_BINDS
• DBA_SQLTUNE_PLANS
• DBA_SQLSET
• DBA_SQLSET_BINDS
• DBA_SQLSET_STATEMENTS
• DBA_SQLSET_REFERENCES
• DBA_SQL_PROFILES
• V$SQL
• V$SQLAREA
• V$ACTIVE_SESSION_HISTORY

Automatic Workload Repository (AWR) in Oracle Database 10g

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

• AWR Features
• Snapshots
• Baselines
• Workload Repository Views
• Workload Repository Reports
• Enterprise Manager

Related articles.

• AWR Baseline Enhancements in Oracle Database 11g Release 1

AWR Features

The AWR is used to collect 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 statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following 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;
/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of “0″ switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this 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, as shown below.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

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

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

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 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 pair of 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.

Workload Repository Views

The following workload repository views are available:

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

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Enterprise Manager

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 you to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Introduction

Oracle has a strict read-consistency model coupled with high concurrency that sets it apart from other database products such as Microsoft’s SQL Server. You can update rows in a table while I query those very same rows. Your updates will not get lost, my query results will not be corrupted, and neither of us will block the other from doing their work. The net result is that with Oracle we can run batch jobs, data loads, and reports all at the same time and never worry about things like “dirty reads” or “read locks.” However, this great functionality comes at a cost to performance—Oracle has to do more work to ensure read-consistency if one user is updating a table while another user is querying it. This raises the question: Just how much slower will a report run if a batch update job is running at the same time?

In this paper, we will first discuss briefly what is meant by read-consistency and how Oracle maintains it in a multi-user environment. Then we will briefly look at how Oracle’s read-consistency model might impact performance from a theoretical standpoint. Next, we’ll spend the bulk of this paper discussing in practical terms how to detect and measure performance degradation caused by concurrent activities that make Oracle work harder to maintain read-consistency. We will look at reproducible examples, TKPROF reports, and v$ views in order to measure how much slower a query runs when the tables being read are undergoing concurrent updates.

Would it be faster to run the update jobs and the report jobs sequentially instead of at the same time? What can we do if both jobs must run at the same time? How can we determine if the two are interacting with each other in a way that degrades performance? You will have the tools to answer questions like these after reading this paper.

A quick note about scope before we get started: This paper looks at the performance implications of Oracle’s read-consistency and concurrency mechanisms. We will not discuss the theoretical correctness of Oracle’s interpretation of the ANSI standards for transaction isolation levels or transaction serializability. Coding practices that developers must use in certain situations to maintain accurate results in a multi-user environment (such as SELECT FOR UPDATE) are also outside the scope of this paper.