Automatic Workload Repository | Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)

The Oracle Database kernel keeps numerous statistics that can be used to identify performance problems. Unfortunately, many database statistics are not stored on the disk. This missing history data imposes a great challenge to performance practitioners to identify and fix the problem that occurred in the past. Historical data and statistics are very important for database performance monitoring and capacity planning.

Oracle Database 10 g enhances the data collection mechanism with the introduction of the Automatic Workload Repository and Active Session History, which replace the previous performance data gathering tools such as Statspack and utlbstat /utlestat. The major differences between the current repositories from previous such tools include the following:

Earlier tools had no automated way of interpreting the collected data and results produced by supplied reports . The raw data and the formatted output from the Statspack tool required manual interpretation.
There is no proactive monitoring in the earlier tools. The DBA could tune the database or solve the problem only after its occurrence. The Automatic Database Diagnostics Monitor (ADDM) tool in Oracle Database 10 g , detects a problem before it strikes and advises possible solutions.
ADDM works at a fine-grained level to detect problems at the database segment level. For example, if there is a hot block or hot object causing performance problems, ADDM identifies the object and provides tuning advice, while this information would not be directly captured in Statspack report.
Problems such as excessive logins and logoffs, ITL waits, and RAC- related service issues were not captured in the Statspack report, but ADDM captures this information and offers tuning advice.

The Automatic Workload Repository (AWR) captures all the data that Statspack captured in the earlier versions of the Oracle RDBMS. In addition, it captures the new statistical data described in the preceding sections. AWR is the performance data warehouse of Oracle Database 10 g . This data resides in the SGA and is also stored on disk.

The captured data is displayed via available views and AWR report, similar to that generated by Statspack. However, accessing this information is much simpler and easier with Oracle Enterprise Manager (EM).

Repository Snapshots

By default, out-of-box, AWR goes to work. Using MMON, the new background process, it takes snapshots of database statistics every 60 minutes. The data is stored in a set of tables under the SYS schema in the new mandatory tablespace, SYSAUX. Like Statspack, a unique identifier called SNAP_ID identifies each snapshot. By default, data older than seven days is purged. Many of these tables are partitioned by range, using the DBID, the unique database identifier, and the SNAP_ID column as their partitioning key. This partitioning strategy is very helpful in data access and maintenance operations.

Note ‚

Even if you do not have Oracle Partitioning option installed, Oracle Database 10 g will create required partitioned tables and indexes for its internal use. You will still need to license and install Oracle Partitioning for your use.

In Oracle Database 10 g Release 1 there are 140 tables that store the AWR data, and they are accessible via 67 views. The table names are in the format WRI$_*, WRH$_* and WRM$_*. ‚“WR ‚½ stands for ‚“Workload Repository, ‚½ ‚“I ‚½ stands for ‚“internal, ‚½ ‚“H ‚½ stands for ‚“historical ‚½ data, and ‚“M ‚½ stands for ‚“metadata. ‚½ There are several views that are built upon these base tables. The view names are in the format DBA_HIST_*; the asterisk (*) represents what statistics the view or table shows. For example, the view DBA_HIST_LATCH shows latch -related statistics for historical snapshots. This view is based on the base table WRH$_LATCH. You can use the following SQL code to list the names of these tables and views:

 select table_name 
 from   dba_tables 
 where  owner = SYS 
 and    table_name like WR%; 
 
 select view_name 
 from   dba_views 
 where  owner = SYS 
 and    view_name like DBA\_HIST\_% escape \;

Snapshot Baselines

AWR allows creation of snapshot baselines to capture performance statistics during a particular time frame. Generally, you will create a baseline for snapshots taken when a typical workload was processed and the performance was acceptable. When the same workload is processed later and the performance is not acceptable, a new baseline for this time frame can be created to compare against the previous baseline. The difference in the performance statistics can shed light on the cause of slow performance. The baseline snapshots are also called Preserved Snapshot Sets.

Using EM to Manage AWR

Oracle EM is the primary tool to interact with AWR. However, you can also use Oracle-supplied package procedures to manage AWR functionality. The manual procedures are described in the next section.

You can access AWR from the Database Control home page of Oracle Enterprise Manager. From this page, first click the Administration link to access the Administration page and then click the Automatic Workload Repository link under the Workload heading toward the bottom of the page.

The Automatic Workload Repository page is shown in Figure 9-1. On this page you can view, edit, and manage AWR snapshots.

Figure 9-1: Workload Repository home page

The General information block shows current snapshot settings and the next snapshot time. The Edit button takes you to a new page where you can change the snapshot interval and snapshot retention and also drill down to change the statistics collection level.

Under the Manage Snapshots and Preserved Snapshot Sets heading you see the total number of available snapshots and preserved snapshots that are used in baselines and the date and time of the earliest and latest available snapshot.

Clicking the number shown after Snapshots takes you to the Snapshots home page, where you can view information about snapshots. Figure 9-2 shows the home page for Snapshots.

Figure 9-2: Snapshots home page

Using the pull-down Actions menu you can perform following tasks :

Create Preserved Snapshot Set (baseline creation)
View Report (by comparing statistics from two snapshots)
Create ADDM task (to perform analysis against a set of snapshots)
Delete Snapshot Range
Compare Timelines (using two sets of snapshots from two time periods)
Create SQL Tuning Set (to track and tune SQL statements)

You can click the Create button to manually create a new snapshot. The Delete button deletes the specified snapshot. The Go button initiates the selected task from the pull-down Actions menu. We found the Go, Create, and Delete buttons on this page a bit confusing at first, perhaps because of their positions on the page. The column Within a Preserved Snapshot Set shows a checkmark for the snapshot IDs that are part of a baseline or the Preserved Snapshot Set.

Back on the AWR home page (Figure 9-1), clicking the number shown next to Preserved Snapshot Sets takes you to the home page for Preserved Snapshots Sets, as shown in Figure 9-3. This page lists the details of the Preserved Snapshot Sets, or the baselines, established in the AWR. The pull-down Actions menu offers you various tasks that can be performed against these baseline snapshots. Please note that the Preserved Snapshot Set ID is different from the snapshot ID. The former applies to the baselines or the preserved AWR snapshots only.

Figure 9-3: Preserved Snapshot Sets (baselines) home page

Manually Managing AWR

The Oracle-supplied package DBMS_WORKLOAD_REPOSITORY provides several routines, procedures, and functions to manually interact with the AWR. These routines allow you to create snapshots on demand, drop a range of snapshots, create a baseline snapshot, drop a baseline snapshot, and ‚ as we discussed in the preceding sections ‚ change snapshot intervals and data retention period, and so on.

Modifying Snapshot Settings

The snapshot frequency, or interval, and the data retention can be changed as shown next, where the interval is changed to 30 minutes and data retention to 15 days:

 begin 
 dbms_workload_repository.modify_snapshot_settings ( 
 interval => 30, 
 retention => 15 * 1440); 
 end; 
 / 
 PL/SQL procedure successfully completed.

Note ‚

Both the values interval and retention must be specified in terms of minutes. That ‚ s why the retention time of 15 days is multiplied by 1440 minutes per day.

The value specified for interval must range from 10 minutes to 52560000 minutes (100 years ). Oracle will generate an error (ORA-13511) if the value is outside this range. However, the value zero has a special meaning: when the interval is set to zero, Oracle will disable the mechanism of taking automatic and manual snapshots. In this case, Oracle simply generates a very large number (40150 days) for this parameter.

Similarly, the retention parameter value must range from 1400 minutes (1 day) to 52560000 minutes (100 years). Oracle will generate an error (ORA-13510) if the value is outside this range. The value zero has a special meaning here, also. When the retention is set to zero, Oracle will retain the snapshot forever. In this case, Oracle uses a larger number (40150 days) for this parameter.

You can see the current values of these parameters as shown next. The column data is displayed in the days and hours format (+DDDDD HH:MI:SS.S):

 col snap_interval for a20 
 col retention for a20 
 select snap_interval, 
 retention 
 from   dba_hist_wr_control; 
 
 SNAP_INTERVAL        RETENTION 
 -------------------- -------------------- 
 +00000 00:30:00.0    +00015 00:00:00.0

Note ‚

The AWR tables reside in the SYSAUX tablespace. AWR purges snapshot data daily once the retention time has been reached. If AWR detects that the SYSAUX tablespace is running short on available space, it will perform an ‚“emergency purge, ‚½ effectively reducing the retention and will write relevant messages to both the trace file and the alert log. However, the space layer code will initiate a server-generated alert because of low space, probably long before an emergency purge is required.

Creating and Dropping Snapshots

You may need to create manual snapshots when the automatic snapshot interval is too large for diagnosing performance issues with a particularly smaller workload or job. In this case, you may want to take manual snapshots before running the job and take another one after it completes.

You can use the CREATE_SNAPSHOT routine to create snapshots on demand. This routine can be executed as a procedure or as a function. When it is executed as a function, it returns the SNAP_ID of the snapshot just created. The optional parameter flush_level controls the level of the statistics that is captured in, or flushed to, the repository tables for this snapshot. The default is TYPICAL. But if all of the detailed statistics are required, you must set the flush_level to ALL.

The following examples show the use of CREATE_SNAPSHOT as a procedure and a function:

 REM  As a Procedure 
 begin 
 dbms_workload_repository.create_snapshot(); 
 end; 
 / 
 PL/SQL procedure successfully completed. 
 
 REM  As a Function 
 select dbms_workload_repository.create_snapshot('ALL') from dual; 
 
 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL') 
 ----------------------------------------------- 
 1931

On the other hand, the DROP_SNAPSHOT_RANGE procedure can be used to permanently drop a range of snapshots. You must know the beginning and ending SNAP_ID for this range, though. The procedure has two required parameters, low_snap_id and high_snap_id . The optional parameter, dbid , defaults to the local database ID. In the following example, the SNAP_ID range from 1981 to 2004 is dropped:

 begin 
 dbms_workload_repository.drop_snapshot_range ( 
 low_snap_id => 1981, 
 high_snap_id => 2004); 
 end; 
 / 
 PL/SQL procedure successfully completed.

As shown next, the DBA_HIST_SNAPSHOT view will list the information on available snapshots. In this example, we selected only the columns needed for the procedure just discussed:

 col snap_id for 999999 
 col begin_interval_time for a26 
 col end_interval_time for a26 
 select snap_id, dbid, begin_interval_time, end_interval_time 
 from  dba_hist_snapshot 
 order by 1 desc; 
 
 SNAP_ID       DBID BEGIN_INTERVAL_TIME        END_INTERVAL_TIME 
 ------- ---------- -------------------------- ------------------------- 
 2397 2847681843 29-MAR-04 06.00.47.194 PM  29-MAR-04 06.30.33.990 PM 
 2396 2847681843 29-MAR-04 05.30.58.107 PM  29-MAR-04 06.00.47.194 PM 
 2395 2847681843 29-MAR-04 05.00.07.715 PM  29-MAR-04 05.30.58.107 PM 
 2394 2847681843 29-MAR-04 04.30.21.575 PM  29-MAR-04 05.00.07.715 PM 
 2393 2847681843 29-MAR-04 04.00.32.191 PM  29-MAR-04 04.30.21.575 PM 
 2392 2847681843 29-MAR-04 03.30.43.423 PM  29-MAR-04 04.00.32.191 PM 
 2391 2847681843 29-MAR-04 03.00.57.354 PM  29-MAR-04 03.30.43.423 PM 
 2390 2847681843 29-MAR-04 02.30.06.223 PM  29-MAR-04 03.00.57.354 PM 
 2389 2847681843 29-MAR-04 02.00.17.503 PM  29-MAR-04 02.30.06.223 PM

Creating and Dropping Baseline (Preserved Snapshot Set)

The baseline, or the Preserved Snapshot Set, can be created using the CRATE_BASELINE routine in the DBMS_WORKLOAD_REPOSITORY package. The routine can be executed as a function or a procedure. The required parameters are the start_snap_id , end_snap_id , and baseline_name . The optional parameter, dbid, defaults to the local database ID. When executed as a function, this routine returns the baseline ID or the Preserved Snapshot Set ID. The following examples show the use of CREATE_BASELINE routine as a function and a procedure:

 REM  As a Procedure 
 begin 
 dbms_workload_repository.create_baseline( 
 start_snap_id => 2305, 
 end_snap_id => 2310, 
 baseline_name => 'Good Nightly Batch'); 
 end; 
 / 
 PL/SQL procedure successfully completed. 
 
 REM  As a Function 
 REM  Using 2200 for start_snap_id, 2205 for the end_snap_id and 
 REM  Good Special Batch for the baseline_name. 
 
 select dbms_workload_repository.create_baseline( 
 2200, 2205, 'Good Special Batch') 
 from dual; 
 
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(2200,2205,'GOODSPECIALBATCH') 
 ---------------------------------------------------------------------- 
 3

Oracle assigns a unique baseline ID to the newly created baseline. The snapshots that fall in the range of the start and end snap IDs used for the baseline will be preserved as long as the as baseline exists in the AWR. Those snapshots can only be purged when the baseline is dropped.

Oracle generates an error (ORA-13506) when the supplied snapshots IDs are not valid. Also, the supplied baseline name must be unique, or an error (ORA-13528) will be reported .

The procedure DROP_BASELINE drops the specified baseline. You must supply the baseline_name . The procedure has an option to either retain the associated snapshots or drop them. When cascade is set to true , all the snapshot IDs for the baseline are dropped. The default value for this parameter is false , meaning associated snapshot IDs will be preserved and only the baseline will be dropped. The optional parameter, dbid, defaults to the local database ID. In the following example, the baseline titled ‚Routine Jobs ‚ is dropped along with its associated snapshot IDs:

 begin 
 dbms_workload_repository.drop_baseline('Routine Jobs', true); 
 end; 
 / 
 PL/SQL procedure successfully completed.

AWR Reports

AWR contains a couple of SQL scripts to produce Workload Repository reports that resemble the Statspack report. However, the Workload Repository report contains much more information than the Statspack report did, since it reports all the new database statistics we discussed in the preceding sections; for example, the Time Model Statistics and Operating System Statistics. In addition, the report contains statistics at Service Name and Module Name levels.

The Oracle-supplied scripts awrrpt.sql and awrrpti.sql generate reports either in HTML or text format for the specified range of snapshot IDs. The output report from these scripts is essentially the same, but the latter script allows you to specify a particular instance in a multi-instance database environment. You need DBA privilege to run these scripts. These scripts are in the $ORACLE_HOME/rdbms/admin directory.

AWR Views

You can view the AWR statistics using various views. Table 9-2 lists a few of these views and briefly describes their contents.

Table 9-2: AWR Views (Not a Complete List)
View Name	Description
DBA_HIST_ACTIVE_SESS_HISTORY	Displays history of the contents of the V$ACTIVE_SESSION_HISOTRY view.
DBA_HIST_BASELINE	Displays information on the baselines stored in AWR.
DBA_HIST_DATABASE_INSTANCE	Displays database and instance information.
DBA_HIST_SNAPSHOT	Displays information on AWR snapshots.
DBA_HIST_SQL_PLAN	Displays information on SQL execution plans.
DBA_HIST_WR_CONTROL	Displays parameter settings that control AWR properties.

In addition to storing performance statistics data in the AWR tables in SYSAUX tablespace, Oracle Database 10 g also takes frequent snapshots of the active sessions and stores that information for historical analysis. This mechanism is called Active Session History.