|
|
|
|
In Oracle Database 10g Oracle replaced statspack with the Advanced Workload Repository (AWR). The job of AWR is to collect database statistics (by default every hour) and this data is maintained for a week and then purged. You can then run reports against these statistics to performance tune your database. Other Oracle features such as ADDM and the database advisors use the database statistics to monitor and analyze the database looking for performance problems.
When you create an Oracle database, AWR is automatically installed and enabled. Statistics collection is automated, and the statistics collected by AWR are stored in the database. In order to properly collect database statistics, the parameter STATISTICS_LEVEL should be set to TYPICAL (the default) or ALL. If STATISTICS_LEVEL is set to BASIC then the AWR will be disabled.
The Oracle database uses AWR for problem detection and analysis as well as for self-tuning. A number of different statistics are collected by the AWR including wait events, time model statistics, active session history statistics, various system and session level statistics, object usage statistics and information on the most resource intensive SQL statements. Other Oracle Database 10g features use the AWR, including ADDM and the other advisors in Oracle Database 10g.
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 typically stored in the SYSAUX tablespace (currently no method exists to move these objects to another tablespace). All AWR table names start 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:
Most of the AWR table names are pretty self explanatory such as WRM$_SNAPSHOT or WRH$_ACTIVE_SESSION_HISTORY. In some Oracle technical documents you will see the AWR tables also refereed to as the select workload repository (SWRF) tables.
Oracle Database 10g also 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.
While AWR is meant to be automatic, provisions for manual operations
impacting the AWR are available. You can modify the snapshot collection interval
and retention criteria, create snapshots and remove snapshots from the AWR.
These topics are described in more detail below.
Manual Snapshot Collection and Retention
Modify the snapshot collection interval using the dbms_workload_repository package. The procedure dbms_workload_repository.modify_snapshot_settings is used in this example to modify the snapshot collection so that it occurs every fifteen minutes and that retention of snapshot data is fixed at 20160 minutes:
-- This causes the repository to refresh every 15 minutes
-- and retain all data for 2 weeks.
Exec dbms_workload_repository.modify_snapshot_settings
(retention=>20160, interval=> 15);
Setting the interval parameter to 0 will disable all statistics collection.
To view current retention and interval settings of the AWR use the DBA_HIST_WR_CONTROL view. Here is an example of using this view:
SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION
---------- -------------------- --------------------
2139184330 +00000 01:00:00.0 +00007 00:00:00.0
In this example, we see that the snapshot interval is every hour (the default), and the retention is set for 7 days.
Creating or Removing Snapshots
Use the dbms_workload_repository package to create or remove snapshots. The dbms_workload_repository.create_snapshot procedure creates a manual snapshot in the AWR as seen in this example:
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, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1;
SNAP_ID END_INTERVAL_TIME
---------- -------------------------
1107 03-OCT-04 01.24.04.449 AM
1108 03-OCT-04 02.00.54.717 AM
1109 03-OCT-04 03.00.23.138 AM
1110 03-OCT-04 10.58.40.235 PM
Each snapshot is assigned a unique snapshot ID that is reflected in the SNAP_ID column. If you have two snapshots, the earlier snapshot will always have a smaller SNAP_ID than the later snapshot. 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);
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');