AWR Reports

AWR Reports

Introduction
Workload Repository Reports
AWR Snapshots and Baselines
Moving AWR Information
Reading the AWR Report
Useful Queries
Remove and Disable AWR
Links with AWR Analyzer


Introduction
AWR periodically gathers and stores system activity and workload data which is then analyzed by ADDM. Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). AWR runs by default and Oracle states that it does not add a noticeable level of overhead. A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository. MMON also provides Oracle10G with a server initiated alert feature, which notifies database administrators of potential problems (out of space, max extents reached, performance thresholds, etc.). The information is stored in the sysaux tablespace under the SYS Schema. This information is the basis for all self-management decisions. For example, it is thus possible to identify the SQL statements that have the
    * largest CPU consumption
    * most buffer gets
    * disk reads
    * most parse calls
    * shared memory

To access Automatic Workload Repository through Oracle Enterprise Manager Database Control:
    On the Administration page, select the Workload Repository link under Workload. From the Automatic Workload Repository page, you can manage snapshots or modify AWR settings.
          o To manage snapshots, click the link next to Snapshots or Preserved Snapshot Sets. On the Snapshots or Preserved Snapshot Sets pages, you can:
                + View information about snapshots or preserved snapshot sets (baselines).
                + Perform a variety of tasks through the pull-down Actions menu, including creating additional snapshots, preserved snapshot sets from an existing range of snapshots, or an ADDM task to perform analysis on a range of snapshots or a set of preserved snapshots.
          o To modify AWR settings, click the Edit button. On the Edit Settings page, you can set the Snapshot Retention period and Snapshot Collection interval.


Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:
select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL       RETENTION
------------------- -------------------
+00000 01:00:00.0   +00007 00:00:00.0

or
select dbms_stats.get_stats_history_availability from dual;
select dbms_stats.get_stats_history_retention from dual;

This SQL shows that the snapshots are taken every hour and the collections are retained for 7 days

If you want to extend that retention period you can execute:

execute dbms_workload_repository.modify_snapshot_settings(
      interval => 60,        -- In Minutes. Current value retained if NULL.
      retention => 43200);   -- In Minutes (= 30 Days). Current value retained if NULL

In this example the retention period is specified as 30 days (43200 min) and the interval between each snapshot is 60 min.

Difference or Advantage between AWR and STATSPACK report

1)The AWR is the next evolution of the STATSPACK utility.

2)The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not.

3)STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view.

4)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary.

5)The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.

6)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.

7)Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. Administrators can manually adjust the snapshot interval if so desired.

8)ADDM captures a much greater depth and breadth of statistics than Statspack does. During snapshot processing, MMON transfers an in-memory version of the statistics to the permanent statistics tables.

9)Statspack snapshot purges must be scheduled manually. When the Statspack tablespace runs out of space, Statspack quits working. AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week's worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

10)AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.


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. This script looks like Statspack; it shows all the AWR snapshots available and asks for two specific ones as interval boundaries. It produces two types of output: text format, similar to that of the Statspack report but from the AWR repository, and the default HTML format, complete with hyperlinks to sections and subsections, providing quite a user-friendly report. Run the script and take a look at the report now to get an idea about capabilities of the AWR.

If you want to explore the AWR repository, feel free to do so. The AWR consists of a number of tables owned by the SYS schema and stored in the SYSAUX tablespace. All AWR table names starts with the identifier “WR.” Following WR is a mnemonic that identifies the type designation of the table followed by a dollar sign ($). AWR tables come with three different type designations:

• Metadata (WRM$)
• Historical data (WRH$)
• AWR tables related to advisor functions (WRI$)

Most of the AWR table names are pretty self-explanatory, such as WRM$_SNAPSHOT or WRH$_ACTIVE_SESSION_HISTORY.
Also Oracle Database 10g offers several DBA tables that allow you to query the AWR repository. The tables all start with DBA_HIST, followed by a name that describes the table. These include tables such as DBA_HIST_FILESTATS, DBA_HIST_DATAFILE, or DBA_HIST_SNAPSHOT. The AWR history tables capture a lot more information than Statspack, including tablespace usage, filesystem usage, even operating system statistics. A complete list of these tables can be seen from the data dictionary through:
select view_name from user_views
where view_name like 'DBA\_HIST\_%' escape '\';



AWR Snapshots
You can create a snapshot manually using:
EXEC dbms_workload_repository.create_snapshot;

You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:
SELECT snap_id, to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,
       to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval
FROM dba_hist_snapshot
ORDER BY 1;

   SNAP_ID BEGIN_INTERVAL  END_INTERVAL
---------- --------------- ---------------
       954 30/NOV/05 03:01 30/NOV/05 04:00
       955 30/NOV/05 04:00 30/NOV/05 05:00
       956 30/NOV/05 05:00 30/NOV/05 06:00
       957 30/NOV/05 06:00 30/NOV/05 07:00
       958 30/NOV/05 07:00 30/NOV/05 08:00
       959 30/NOV/05 08:00 30/NOV/05 09:00

Each snapshot is assigned a unique snapshot ID that is reflected in the SNAP_ID column. The END_INTERVAL_TIME column displays the time that the actual snapshot was taken.

Sometimes you might want to drop snapshots manually. The dbms_workload_repository.drop_snapshot_range procedure can be used to remove a range of snapshots from the AWR. This procedure takes two parameters, low_snap_id and high_snap_id, as seen in this example:
EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>1107, high_snap_id=>1108);

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.

Finally , you can use the following query to identify the occupants of the SYSAUX Tablespace
select substr(occupant_name,1,40), space_usage_kbytes
   from v$sysaux_occupants;

AWR Automated Snapshots

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically, when you create a new Oracle database under Oracle Database 10g. To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:
SELECT a.job_name, a.enabled, c.window_name, c.schedule_name, c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
  And a.schedule_name=b.window_group_name
  And b.window_name=c.window_name;

You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable('GATHER_STATS_JOB');

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable('GATHER_STATS_JOB');

AWR Baselines
It is frequently a good idea to create a baseline in the AWR. A baseline is defined as a range of snapshots that can be used to compare to other pairs of snapshots. The Oracle database server will exempt the snapshots assigned to a specific baseline from the automated purge routine. Thus, the main purpose of a baseline is to preserve typical runtime statistics in the AWR repository, allowing you to run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR. This allows you to compare current performance (and configuration) to established baseline performance, which can assist in determining database performance problems.

Creating baselines
You can use the create_baseline procedure contained in the dbms_workload_repository stored PL/SQL package to create a baseline as seen in this example:
EXEC dbms_workload_repository.create_baseline (start_snap_id=>1109, end_snap_id=>1111, baseline_name=>'EOM Baseline');

Baselines can be seen using the DBA_HIST_BASELINE view as seen in the following example:
SELECT baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   START_SNAP_ID END_SNAP_ID
----------- --------------- ------------- -----------
          1 EOM Baseline             1109        1111

In this case, the column BASELINE_ID identifies each individual baseline that has been defined. The name assigned to the baseline is listed, as are the beginning and ending snapshot IDs.

Removing baselines
The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted. You can remove a baseline using the dbms_workload_repository.drop_baseline procedure as seen in this example that drops the “EOM Baseline” that we just created.
EXEC dbms_workload_repository.drop_baseline (baseline_name=>'EOM Baseline', Cascade=>FALSE);

Note that the cascade parameter will cause all associated snapshots to be removed if it is set to TRUE; otherwise, the snapshots will be cleaned up automatically by the AWR automated processes.

Moving AWR information
10G R2's Enterprise Manager allows administrators to transfer Automatic Workload Repository snapshots to other 10G R2 workload repositories for offline analysis. This is accomplished by the administrator specifying a snapshot range and extracting the AWR data to a flat file. The flat file is then loaded into a user-specified staging schema in the target repository. To complete the transfer, the data is copied from the staging schema into the target repository's SYS schema. The data in the SYS schema is then used as the source for the ADDM analysis.
If the snapshot range already exists in the SYS or staging schemas, the data being imported is ignored. All data in snapshot ranges that does not conflict with existing data is loaded. 10G R2 contains a new package DBMS_SWRF_INTERNAL to provide AWR snapshot export and import functionality.
The example below exports a snapshot range starting with 100 and ending at 105 to the output dump file 'awr_wmprod1_101_105' in the directory '/opt/oracle/admin/awrdump/wmprod1':
BEGIN
DBMS_SWR_INTERNAL.AWR_EXTRACT(
DMPFILE =>'awr_export_wmprod1_101_105',
DMPDIR => '/opt/oracle/admin/awrdump/wmprod1',
BID => 101,
EID => 105)
We then use the AWR_LOAD procedure to load the data into our target repository staging schema:
BEGIN
DBMS_SWR_INTERNAL.AWR_LOAD(
SCHNAME => 'foot',
DMPFILE =>'awr_export_wmprod1_101_105',
DMPDIR => '/opt/oracle/admin/awrdump/wmprod1')
The last step is to transfer the data from our staging schema (FOOT) to the SYS schema for analysis:
BEGIN
DBMS_SWR_INTERNAL.MOVE_TO_AWR(SCHNAME => 'foot',)


Reading the AWR Report
This section contains detailed guidance for evaluating each section of an AWR report. The main sections in an AWR report include:

Report Summary Section:
This gives an overall summary of the instance during the snapshot period, and it contains important aggregate summary information.
- Cache Sizes: This shows the size of each SGA region after AMM has changed them.  This information can be compared to the original init.ora parameters at the end of the AWR report.
- Load Profile: This section shows important rates expressed in units of per second and transactions per second.
- Instance Efficiency Percentages: With a target of 100%, these are high-level ratios for activity in the SGA.
- Shared Pool Statistics: This is a good summary of changes to the shared pool during the snapshot period.
- Top 5 Timed Events: This is the most important section in the AWR report.  It shows the top wait events and can quickly show the overall database bottleneck.

Wait Events Statistics Section
This section shows a breakdown of the main wait events in the database including foreground and background database wait events as well as time model, operating system, service, and wait classes statistics.
- Time Model Statistics: Time mode statistics report how database-processing time is spent. This section contains detailed timing information on particular components participating in database processing.
- Wait Class:
- Wait Events: This AWR report section provides more detailed wait event information for foreground user processes which includes Top 5 wait events and many other wait events that occurred during the snapshot interval.
- Background Wait Events: This section is relevant to the background process wait events.
- Operating System Statistics: The stress on the Oracle server is important, and this section shows the main external resources including I/O, CPU, memory, and network usage.
- Service Statistics: The service statistics section gives information about how particular services configured in the database are operating.
- Service Wait Class Stats:

SQL Statistics Section
This section displays top SQL, ordered by important SQL execution metrics.
- SQL Ordered by Elapsed Time: Includes SQL statements that took significant execution time during processing.
- SQL Ordered by CPU Time: Includes SQL statements that consumed significant CPU time during its processing.
- SQL Ordered by Gets: These SQLs performed a high number of logical reads while retrieving data.
- SQL Ordered by Reads: These SQLs performed a high number of physical disk reads while retrieving data.
- SQL Ordered by Executions:
- SQL Ordered by Parse Calls: These SQLs experienced a high number of reparsing operations.
- SQL Ordered by Sharable Memory: Includes SQL statements cursors which consumed a large amount of SGA shared pool memory.
- SQL Ordered by Version Count: These SQLs have a large number of versions in shared pool for some reason.
- Complete List of SQL Text:

Instance Activity Stats
This section contains statistical information describing how the database operated during the snapshot period.
- Instance Activity Stats - Absolute Values: This section contains statistics that have absolute values not derived from end and start snapshots.
- Instance Activity Stats - Thread Activity: This report section reports a log switch activity statistic.

I/O Stats Section
This section shows the all important I/O activity for the instance and shows I/O activity by tablespace, data file, and includes buffer pool statistics.
- Tablespace IO Stats
- File IO Stats

Buffer Pool Statistics Section

Advisory Statistics Section
This section show details of the advisories for the buffer, shared pool, PGA and Java pool.
- Instance Recovery Stats:
- Buffer Pool Advisory:
- PGA Aggr Summary: PGA Aggr Target Stats; PGA Aggr Target Histogram; and PGA Memory Advisory.
- Shared Pool Advisory:
- SGA Target Advisory
- Stream Spool Advisory
- Java Pool Advisory

Wait Statistics Section
- Buffer Wait Statistics: This important section shows buffer cache waits statistics.
- Enqueue Activity: This important section shows how enqueue operates in the database. Enqueues are special internal structures which provide concurrent access to various database resources.

Undo Statistics Section
- Undo Segment Summary: This section gives a summary about how undo segments are used by the database.
- Undo Segment Stats: This section shows detailed history information about undo segment activity.

Latch Statistics Section:
This section shows details about latch statistics. Latches are a lightweight serialization mechanism that is used to single-thread access to internal Oracle structures.
- Latch Activity
- Latch Sleep Breakdown
- Latch Miss Sources
- Parent Latch Statistics
- Child Latch Statistics

Segment Statistics Section:
This report section provides details about hot segments using the following criteria:
- Segments by Logical Reads: Includes top segments which experienced high number of logical reads.
- Segments by Physical Reads: Includes top segments which experienced high number of disk physical reads.
- Segments by Row Lock Waits: Includes segments that had a large number of row locks on their data.
- Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.
- Segments by Buffer Busy Waits: These segments have the largest number of buffer waits caused by their data blocks.

Dictionary Cache Stats Section
This section exposes details about how the data dictionary cache is operating.

Library Cache Section
Includes library cache statistics describing how shared library objects are managed by Oracle.

Memory Statistics Section
- Process Memory Summary
- SGA Memory Summary: This section provides summary information about various SGA regions.
- SGA Breakdown difference:

Streams Statistics Section
- Streams CPU/IO Usage
- Streams Capture
- Streams Apply
- Buffered Queues
- Buffered Subscribers
- Rule Set

Resource Limit Stats Section

init.ora Parameters Section


Useful Queries

Standard reports
The SQL*Plus scripts are located in $ORACLE_HOME/rdbms/admin directory, the output in either text or HTML (default) format
ASH report (ashrpt.sql)
        Helps answer questions about “What’s going on right now and who is doing it?” for a specified time period
AWR report (awrrpt.sql)
        Breakdown of what was consuming “DB Time” for a specified time period
AWR “diff” report (awrddrpt.sql)
        Compares and highlights what changed between two specified time periods
AWR “SQL” report (awrsqrpt.sql)
        Displays all recorded information about a specific SQL during a specified time period. Good when you want to focus on a particular SQL statement.


To get a quick report of any SQL statement’s execution plans within the past 7 days, if you have the statement’s SQL ID value:
select * from table(dbms_xplan.display_awr(‘sqlid’)) ;

If you don’t have the SQL statement’s SQL ID, it can be found in:
- A standard AWR report or EM DB Console or Grid Control
- or Query the V$SQL or DBA_HIST_SQLTEXT view:
select sql_id, sql_text from v$sql where lower(sql_text) like ‘%phrase%’ ;
select sql_id, sql_text from dba_hist_sqltext where lower(sql_text) like ‘%phrase%’ ;


Removing and Disabling AWR Information
There may be times when a DBA might desire to disable Oracle 10g’s and 11g’s AWR (Automatic Workload Repository).
One reason might be to avoid licensing issues, because AWR isn’t part of the standard or even enterprise database – as it requires the optional (extra cost) Oracle Enterprise Manager (OEM) Diagnostic pack. So even though your 10g/11g database automatically collects AWR data every sixty minutes and retains it for a week – you cannot legally use the Oracle supplied PL/SQL packages (i.e. DBMS_WORKLOAD_REPOSITORY), the OEM screens for AWR, ADMM and ASH, or even the AWR data dictionary views (i.e. DBA_HIST_*) if you’re not licensed.
If you query the DBA_HIST_* data dictionary views, you better have purchased the OEM Diagnostic pack! For those of you who prefer to directly access the SYS data dictionary tables – that means don’t even select from tables with names like WRM$*, WRH$* or WRI$*!
So assuming that you prefer to disable AWR so as not to accidentally (or purposefully) violate your Oracle licensing agreement, here are some ways to disable AWR for a given database (you’ll need to do one of these to every database you manage):
Many Ways to Disable AWR:
   1. Download Meta-Link script dbms_awr.plb, compile this package, then execute the PL/SQL package dbms_awr.disable_awr() [Metalink Note 436386.1].
   2. Set your init.ora parameter STATISTICS_LEVEL = BASIC
   3. Execute the Oracle provided PL/SQL package: dbms_workload_repository.modify_snapshot_settings(interval=>0)
   4. Execute the Oracle provided PL/SQL package: dbms_scheduler.disable('GATHER_STATS_JOB')
   5. You can use Toad for #3: Main Menu->Database->Monitor->ADDM/AWR Reports screen, choose the Snapshot Management tab, set the interval to all zeroes, and then press the green checkmark in upper left corner to commit the change.
   6. You can use Toad for #4: Main Menu->Schema Browser, choose the Sched. Job tab and disable the GATHER_STATS_JOB job.
   7. You can use OEM for #4: Main Menu->Workload->Automatic Workload Repository, select the “Edit” button and then select the last radio group item labeled: Turn off Snapshot Collection, finally press OK
   8. You can use OEM for #5: Main Menu->Scheduler->Jobs, select the data grid row for GATHER_STATS_JOB, choose the disable drop-down action, then  finally press OK
   9. Create your own database creation scripts (i.e. do not use DBCA) and make sure not to run the CATAWRTB.sql script [Note – Oracle upgrade process may undo this]
  10. Run the $ORACLE_HOME\rdbms\admin\catnoawr.sql script to drop the AWR Repository tables [Note – Oracle upgrade process may undo this]

If you want to rebuild the AWR Repository Tables later, you need to perform the following:
- Execute (again) the script $ORACLE_HOME/rdbms/admin/catnoawr.sql
- Execute the script $ORACLE_HOME/rdbms/admin/catawrtb.sql
- Bounce the database.
- On re-start of the database instance, the AWR tables will be populated with the required data.



Links with AWR Analyzer
http://www.oraperf.com 
http://www.txmemsys.com/statspack-reg.htm (Statspack Analyzer)
http://www.dbapool.com/dbanalyzer.php (Analyze your AWR or Statspack)
http://www.softpedia.com/get/Internet/Servers/Database-Utils/spReporter.shtml  (Download Tool to Analyze AWR or Statspack Reports)
http://www.ondatafine.com/  (web based application. It processes plain-text statspack or AWR)
http://www.spviewer.com/index.html (STATSPACK and AWR Viewer software)
Scripts: http://www.evdbt.com/tools.htm