How to run Oracle AWR report for snapshot analysis

To investigate performance problems with Oracle queries, create the report AWR (Automatic Workload Repository) from the snapshots. Connect in sqlplus with sys as sysdba and run:

@?/rdbms/admin/awrrpt 

Select report type (html or text), select the snapshot start id and end id, then a report in html or txt format is created.

Example:

SQL> @?/rdbms/admin/awrrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1273096611 ORCL                1 orcl


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1273096611        1 ORCL         orcl         nc118067

Using 1273096611 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL             13638 17 Jul 2013 00:00      1
                              13639 17 Jul 2013 01:00      1
                              13640 17 Jul 2013 02:00      1
                              13641 17 Jul 2013 03:00      1
                              13642 17 Jul 2013 04:00      1
                              13643 17 Jul 2013 05:00      1
                              13644 17 Jul 2013 06:01      1
                              13645 17 Jul 2013 07:00      1
                              13646 17 Jul 2013 08:00      1
                              13647 17 Jul 2013 09:00      1
                              13648 17 Jul 2013 10:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 13645
Begin Snapshot Id specified: 13645

Enter value for end_snap: 13648
End   Snapshot Id specified: 13648



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_13645_13648.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_13645_13648.html

<... here the report out is displayed ...>

Report written to awrrpt_1_13645_13648.html

Open the report and starts looking for the slower statement with higher number of executions. Check sections named "SQL ordered by Elapsed Time" and "SQL ordered by CPU Time" like in this example:

Identify in the AWR report, the statement with the worst times, then prepare a tuning task for this specific statement. In this example:

SQL> SET SERVEROUTPUT ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
              begin_snap  => 13645,
              end_snap    => 13648,
              sql_id      => 'acqfd7qpz44fp',
              scope       => DBMS_SQLTUNE.scope_comprehensive,
              time_limit  => 60,
              task_name   => 'acqfd7qpz44fp_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;
/

PL/SQL procedure successfully completed.

Specify the sql id related to the statement of the report. You can check the status of the task with a query:


SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name like 'acqfd7qpz44fp%'

TASK_NAME                      STATUS

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

acqfd7qpz44fp_AWR_tuning_task  INITIAL

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'acqfd7qpz44fp_AWR_tuning_task');

TASK_NAME                      STATUS

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

acqfd7qpz44fp_AWR_tuning_task  COMPLETED

SQL>SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('acqfd7qpz44fp_AWR_tuning_task') AS recommendations FROM dual;

The output shows access plan and the recommendation (for example new indexes) with estimated benefit:

  Recommendation (estimated benefit: 54.02%)

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

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

After the execution you can drop the task:


SQL>EXEC DBMS_SQLTUNE.drop_tuning_task (task_name => 'acqfd7qpz44fp_AWR_tuning_task');