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