Automatic Workload Repository (AWR)
The AWR collect and stores database statistics relating to problem detection and tuning. AWR is a replacement for the statspack utility which helps gather database performance statistics. AWR generates snapshots of the key performance data, such as system and session statistics, segment-usage statistics, time model statistics, high load statistics and stores in the sysaux.
AWR provides statistics in 2 formats
· Temporary – in memory collection of statistics in the SGA, accessible via the V$ views.
· Persistent – type of performance data in the form of regular AWR snapshots which you access via DBA_views.
The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespaces.
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.
Some of the Important Views and tables regarding to AWR
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.
- V$SYS_TIME_MODEL – time model stats (db time, java execution time, pl/sql execution time,etc.,)
- V$OSSTAT – operating system stats(avg busy tick, avg idle ticks, etc)
- V$SERVICE_STATS - wait statistics(db cpu, app wait time, user commits, etc)
- V$SESSTAT – session stats
- V$SYSSTAT – system stats
- 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.
Generating, Modifying And Droping The Snapshots
Creating of Snapshots
Extra snapshots can be taken and existing snapshots can be removed using:
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
Modifying an existing snapshot
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:
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.
Droping The Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/
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.
Sample output for AWR reports
OUTPUT
-----------------------------------------------------------------
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ------- ------
DBDABR 37933856 dbdabr 1 10.1.0.2.0 NO Host1
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2900 19-Aug-04 11:00:29 18 5.2
End Snap: 2911 19-Aug-04 22:00:16 18 4.6
Elapsed: 659.78 (mins)
DB Time: 10.08 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 48M Std Block Size: 8K
Shared Pool Size: 56M Log Buffer: 256K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
-------------- -----------
Redo size: 1,766.20 18,526.31
Logical reads: 39.21 411.30
Block changes: 11.11 116.54
Physical reads: 0.38 3.95
Physical writes: 0.38 3.96
User calls: 0.06 0.64
Parses: 2.04 21.37
Hard parses: 0.14 1.45
Sorts: 1.02 10.72
Logons: 0.02 0.21
Executes: 4.19 43.91
About Load Profile in AWR Reports
This section gives a glimpse of the database workload activity that occurred within the snapshot interval. For example, the load profile below shows that an average transaction generates about 18K of redo data, and the database produces about 1.8K redo per second.
Load Profile~~~~~~~~~~~~ Per Second Per Transaction -------------- --------------- Redo size: 1,766.20 18,526.31 Logical reads: 39.21 411.30 Block changes: 11.11 116.54 Physical reads: 0.38 3.95 Physical writes: 0.38 3.96 User calls: 0.06 0.64 Parses: 2.04 21.37 Hard parses: 0.14 1.45 Sorts: 1.02 10.72 Logons: 0.02 0.21 Executes: 4.19 43.91
The above statistics give an idea about the workload the database experienced during the time observed. However, they do not indicate what in the database is not working properly. For example, if there are a high number of physical reads per second, this does not mean that the SQLs are poorly tuned.
Perhaps this AWR report was built for a time period when large DSS batch jobs ran on the database. This workload information is intended to be used along with information from other sections of the AWR report in order to learn the details about the nature of the applications running on the system. The goal is to get a correct picture of database performance.
The following list includes detailed descriptions for particular statistics:
The amount of redo generated during this report.
Calculated as (Consistent Gets + DB Block Gets = Logical Reads).
The number of blocks modified during the sample interval.
The number of requests for a block that caused a physical I/O operation.
Number of physical writes performed.
Number of user queries generated.
: The total of all parses; both hard and soft.
The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.
: Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes far fewer resources.
: All self-explanatory.
Parse activity statistics should be checked carefully because they can immediately indicate a problem within the application. For example, a database has been running several days with a fixed set of applications, it should, within a course of time, parse most SQLs issued by the applications, and these statistics should be near zero.
If there are high values of Soft Parses or especially Hard Parses statistics, such values should be taken as an indication that the applications make little use of bind variables and produce large numbers of unique SQLs. However, if the database serves developmental purposes, high vales of these statistics are not bad.
The following information is also available in the workload section:
% Blocks changed per Read: 4.85 Recursive Call %: 89.89
Rollback per transaction %: 8.56 Rows per Sort: 13.39
The % Blocks changed per Read statistic indicates that only 4.85 percent of all blocks are retrieved for update, and in this example, the Recursive Call % statistic is extremely high with about 90 percent. However, this fact does not mean that nearly all SQL statements executed by the database are caused by parsing activity, data dictionary management, space management, and so on.
Remember, Oracle considers all SQL statements executed within PL/SQL programs to be recursive. If there are applications making use of a large number of stored PL/SQL programs, this is good for performance. However, applications that do not widely use PL/SQL may indicate the need to further investigate the cause of this high recursive activity.
It is also useful to check the value of the Rollback per transaction % statistic. This statistic reports the percent of transactions rolled back. In a production system, this value should be low. If the output indicates a high percentage of transactions rolled back, the database expends a considerable amount of work to roll back changes made. This should be further investigated in order to see why the applications roll back so often.
About Cache Sizes in AWR Reports
This report section contains the cache sizes at the end of the snapshot period. Remember, if you are using Automatic Memory Management, AMM may adjust your size between snapshots. This information is valid for the time specific to the end snapshot of the snapshot interval observed. This is essential because the Automatic Memory Management activity could resize caches during the snapshot interval time or the DBA can do it manually.
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 48M Std Block Size: 8K
Shared Pool Size: 56M Log Buffer: 256K
One interesting feature in AWR is the use of begin values and end values for those parameters that are changed dynamically or via the
Automatic Memory Manager (AMM) facility in Oracle10g:
PARAMETER NAME | BEGIN VALUE | END VALUE (IF DIFFERENT) |
background_dump_dest | u01/app/DBDABR/ DUMP |
|
compatible | 10.1.0.2.0 |
|
db_block_size | 8192 |
|
db_cache_size | 364904448 | 455210668 |
Differences between AWR and STATSPACK
http://vinu3012dba.blogspot.com/2010/08/difference-or-advantage-between-awr-and.html
