Workload repository also ensures that historical data will be available for baseline comparisons. Before AWR, collecting this type of data required manual collection and management using Statspack or custom code. Workload repository data is owned by SYS user and is stored in SYSAUX tablespace. The data is stored in a collection of tables, all of which are named beginning with WR.
SQL> select table_name from dba_tables where tablespace_name=’SYSAUX’
and substr(table_name, 1, 2) = ‘WR’
and rownum<=20 order by 1;
Once in repository, the statistics can be accessed using data dictionary views.
Enabling AWR
To enable AWR, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. If it is set to BASIC, AWR statistics will not be gathered automatically, but they can be gathered manually using procedures in the built-in DBMS_WORKLOAD_REPOSITORY package. Note that manually gathered statistics will not be as complete as statistics gathered automatically through AWR. The workload repository is created automatically at the database creation time. No manual action is required.
AWR Space Considerations
A rough guideline is that an average system with an average of 10 concurrent active sessions will generate 200MB to 300MB of AWR data. This estimate assumes the default retention period of 7 days. The space used is determined by the number of active sessions, the snapshot interval, and the retention period. Space consumption can be reduced by either increasing the snapshot interval (resulting in less snapshots) or decreasing the retention period. Technically, you can also decrease your active sessions, but undoubtedly your users would not appreciate it. By reducing the available statistics, the accuracy and validity of the following components may be reduced as well:
- ADDM
- SQL Tuning Advisor
- Undo Advisor
- Segment Advisor
Active Session History
In order to provide statistics on current session activity, Oracle 10g has introduced ASH.
Sizing ASH
ASH is actually a FIFO buffer in memory that collects statistics on current session activity. These statistics are gathered by extracting sampled from V$SESSION every second. Because this kind of frequent gathering could quickly overwhelm the system, ASH continually ages out old statistics to make room for new ones. ASH resides in SGA and its size is fixed for the lifetime of the instance. Its size is calculated by using: The lesser of:
- Total number of CPUs * 2MB of memory
- 5 percent of shared pool size
Increase number of CPUs
Increase the shared pool size ASH Statistics
The following types of data are sampled by ASH:
SQL_ID
SID
Client ID, Service ID
Program, module, action
Object, file, block
Wait event number, actual wait time (if session is waiting) NOTE: SQL_ID is a hash value that uniquely identifies a SQL statement in the database. SQL_ID is new to 10g ASH Views
The statistics in ASH can be viewed using the V$ACTIVE_SESSION_HISTORY fixed view. ASH and AWR Because the data in ASH represents a unique set of statistics, Oracle captures some of the ASH statistics to the workload repository for persistent storage. This process is handled in two ways:
- Every 30 minutes, MMON process flushed ASH buffer of all data. In the process, it filters some of the data into the AWR. Due to the high volume of data, MMON process doesn’t filter all of the ASH data into AWR.
- If the ASH buffer fills in less than 30 minutes, MMNL (Memory Monitor Light) process will flush out a portion of the buffer (to make room for new statistics) and filter a portion of data to the AWR. Using AWR The primary interface for AWR is through Oracle EM Database Control. The link to access AWR can be found in Administration page. Under Workload, click Workload Repository link. From this page, you can manage AWR settings and snapshots. From this page, you can manage AWR settings and snapshots.
Oracle also provides DBMS_WORKLOAD_REPOSITORY package. Procedures in this package include:
CREATE_SNAPSHOT -> create manual snapshots
DROP_SNAPSHOT_RANGE ->Drops a range of snapshots at once
CREATE_BASELINE -> Creates a single baseline
DROP_BASELINE -> Drops a single baseline
MODIFY_SNAPSHOT_SETTINGS -> Changes the RETENTION and INTERVAL settings AWR Snapshots AWR collects performance statistics by taking snapshots of the system at regular intervals. Using Snapshots The snapshot pulls information from fixed tables that hold performance statistics in memory. By default, AWR generates performance data snapshots once every hour. This is known as snapshot interval. It also retains the snapshot statistics for seven days before automatically purging them. This is known as retention period. The data from these snapshots is analyzed by the ADDM for problem-detection and self-tuning.
To view, the current AWR settings, you can use the DBA_HIST_WR_CONTROL view, as shown here:
SQL> select snap_interval, retention from dba_hist_wr_control;
Each snapshot is assigned a unique snapshot ID, which is a sequence number guaranteed to be unique within the repository. The only exception to this is when using RAC. In an RAC environment, AWR snapshots will query every node within the cluster. In this situation, the snapshots for all nodes will share a snapshot ID. Instead they can be differentiated by the instance ID.
Creating Snapshots
To create a snapshot manually,
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;
Modifying Snapshot Frequency
To make changes, use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.
Ex:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;
Dropping Snapshots
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 316, HIGH_SNAP_ID => 320);
NOTE: This procedure can also be used to drop individual snapshots by using the same snapshot ID for both LOW_SNAP_ID and HIGH_SNAP_ID parameters.
AWR Baselines
A baseline is defined as a pair of snapshots that denote a significant workload period. This baseline can be retained for comparison to current system performance.
Using Baselines
Baselines can also be used to define threshold settings for Oracle’s server-generated alerts facility. AWR baselines also make an excellent tool for application performance and scalability testing.
Creating Baselines
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
START_SNAP_ID => 42, END_SNAP_ID => 43,
BASELINE_NAME => ‘REPORTS’);
END;
Dropping Baselines
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(‘REPORTS’, FALSE);
DROP_BASELINE procedure parameters
BASELINE_NAME Name of the baseline to be dropped
CASCADE Boolean to determine whether associated snapshots will be dropped
DBID Optional database ID
Using AWR Views
DBA_HIST_ACTIVE_SESS_HISTORY -> displays the session statistics gathered from ASH
DBA_HIST_BASELINE -> displays information on baselines in the repository
DBA_HIST_DATABASE_INSTANCE -> displays database environment data
DBA_HIST_SQL_PLAN -> displays SQL execution path data
DBA_HIST_WR_CONTROL -> displays current AWR settings
DBA_HIST_SNAPSHOT -> displays information regarding snapshots stored in AWR
SQL> select snap_id, begin_interval_time, end_interval_time from
dba_hist_snapshot order by 1;
Using AWR Reports
Oracle offers a standard summary report that can be run at any time against the statistics stored in AWR. This report provides an analysis of system performance over a specified period of time. This report is run through one of the two SQL*Plus scripts:
awrrpt.sql, which generates text file report
awrrpti.sql, which generates an HTML version of report
Granting Privileges needed to use AWR
GRANT SELECT ON SYS.V_$DATABASE TO ...
GRANT SELECT ON SYS.V_$INSTANCE TO ...
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ...
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ...
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ...
GRANT ADVISOR TO ...
