Get more out of your Automatic Workload Repository (AWR)

Monday, March 11, 2013


Get more out of your Automatic Workload Repository (AWR)


Automatic Workload Repository (AWR) is one of the best features Oracle came up with.  Oracle has gone a long way since the days of BSTAT and ESTAT to get key performance data.

AWR is built in the Oracle kernel and thus starts working when the database is installed and the instance is running. However in order to use it you must purchase the DIAGNOSTIC PACK licencse.  The usage includes the AWR (Automatic Workload Repository) – MMON flushes by default metrics each hour to the repository, the dbms_workload_repository package, the awrrpt.sql and awrrpti.sql reports, any of the wrh$_ tables and  dba_hist_ and v$active_session_history views, ADDM – Automatic Database Diagnostic Monitor, and all dba_advisor views.


You can use OEM (Enterprise manager) or a third party tool like Toad here however we will discuss how to use AWR in sqlplus. Although AWR collects all sorts for metrics we will for the scope of this blog concentrate on SQL statistics only, the complete list is however:

·         Base statistics that are also part of the v$SYSSTAT and V$SESSTAT views

·         SQL statistics

·         Database object-usage statistics

·         Time-model statistics

·         Wait statistics

·         ASH (active session history) statistics

·         Operating system statistics

To generate an AWR report in sqlpus  (html or text) run:

·         SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql  (which calls @@awrrpti.sql)

To generate an AWR report using PL/SQL interface  (html  or text) run  (using the snap_id used further in this blog) :

·         SQL> select output from table(dbms_workload_repository.awr_report_text( (select dbid from v$database), 1, 1493, 1494 ))

Selecting a begin and end snapshot should not be a problem.  However, say you want to go back further in time or increase the frequency when the snapshots are taken or the number of Top Sql that is being investigated. For this you can use the dbms_workload_repository package, see illustration below:

·         SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 15, retention => 108000 , topnsql=> 200);

- Interval is in minutes  – Retention is in minutes  – topnsql is number of top sql.  To check the settings you query the DBA_HIST_ dictionary view:

·         SQL>  select * from from DBA_HIST_WR_CONTROL;


   DB Id             SNAP_INTERVAL                      RETENTION                             TOPNSQL
———–      ———————————- ———————————-      ———-
   93581573     +00000 01:00:00.0                  +00014 00:00:00.0               DEFAULT
4018902398 +00000 00:15:00.0                  +00075 00:00:00.0               200


So you see that orignially the default values where 1 hour interval, 14 days retention and TOPNSQL DEFAULT.  The latter depends on your setting for the spfile parameter statistics_level, whereby TYPICAL denotes a DEFAULT of 30 and ALL a DEFAULT of 100.

Please note that each statement has an unique sql_id. By statement we mean also the exact spelling, format and layout.  Beware that a statement that may look the same and may do the same thing but when any difference even a space or tab makes it for Oracle another sql_id.  By managing the AWR settings you can effectively build a permanent repository.  Usefull views are DBA_HIST_SNAPSHOT , DBA_HIST_SQL_PLAN , DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT.

The secret lies in the fact that you can query directly in which snapshot a particular sql_id was captured.  This could be important when you compare Prod with QA or Test environments. When you know the sql_id you can then check what plan it had either 1) previously 2) on the other environment.  I illustrate with an example. Say we have an sql_id from v$sql , what info can we get from the Workload Repository ?

·         SQL> select s.snap_id, t.sql_id , t.sql_text
            from dba_hist_sqltext t, dba_hist_sqlstat s
            where s.snap_id > 300
            and s.sql_id = t.sql_id
            and s.sql_id = ’002bt997z8dkk’;


Snap Id SQL_ID        SQL_TEXT
——— ————- ——————————————————-



When was that snapshot ?

·         SQL> select snap_id , begin_interval_time, end_interval_time, snap_level from dba_hist_snapshot where snap_id = ’1493′


———   ———————————-             ——————————-           —————
     1493  11-OCT-12 PM      11-OCT-12 PM           2



How to get an explain plan from AWR ?

·         SQL> select * from table(dbms_xplan.display_awr(’002bt997z8dkk’,NULL,NULL,’ALL’));


SQL_ID 002bt997z8dkk

Plan hash value: 3628823899

| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | INSERT STATEMENT |                  |       |       |    20 (100)|          |
|   1 |  INDEX FULL SCAN | S_SRM_REQUEST_M6 |  3129 | 78225 |    20   (0)| 00:00:01 |


I hope this illustrates the point how you can manage your AWR repository to capture more info and retain longer and how usefull that is for capturing sql statements and their respective sql_id’s.