8.1. Using the Automatic Workload Repository (AWR)
The Automatic Workload Repository (AWR) is the cornerstone of the CMI. AWR provides services to collect, process, maintain, and access database performance statistics and metrics for the database.
This section discusses the two elements that make up AWR:
The statistics collection facility, which is charged with the collection of dynamic performance statistics
The workload repository, where the statistics are stored for persistence
We'll also describe the Active Session History (ASH) buffer and how the Manageability Monitor (MMON) process captures statistics from ASH for inclusion in the workload repository.
Next, you'll learn how to use AWR to manage snapshots and baselines. Snapshots represent AWR's method of capturing statistics. Baselines define a range of snapshots that represent a particular workload. These baselines can be saved for comparison to monitor workload performance.
Finally, we'll show you the AWR views and even an HTML report you can run to see AWR data.
8.1.1. AWR Statistics Collection Facility
The statistics collection facility of AWR resides in memory and is responsible for the collection of dynamic performance statistics. All of the collected statistics are stored in fixed tables that are only accessible through fixed views. The statistics are also stored in the workload repository element of the AWR for persistent storage.
In the following sections, you'll learn what database performance statistics are and how they differ from optimizer statistics. You'll also look at how the MMON process calculates metrics from the statistics. Lastly, you'll see a few of the new performance statistics added in Oracle 10g.
|
8.1.1.1. Database Performance Statistics
Unlike optimizer statistics, database performance statistics are not utilized by the query optimizer. Instead, they are used to measure the performance of the database over time. By analyzing these statistics, performance problems can be identified and resolved.
Database performance statistics are dynamic in nature, meaning that they are initialized at instance startup and are lost at instance shutdown. Unlike optimizer statistics, they are not stored in the data dictionary. Instead, they are stored in dynamic performance tables, more commonly known as fixed tables. Fixed tables are memory structures that emulate tables in that they can be queried and can be the object of views.
Database performance statistics fall into one of three categories:
Cumulative values
Metrics
Sampled data
Cumulative value statistics, as the name implies, are statistics that accumulate over a period of time through continuous updating. These statistics are captured for the system as a whole, for individual sessions, for individual SQL statements, for segments, and for services (the 5 Ss, if you will). For example, the VSSYSSTAT view shows cumulative statistics for the system since startup. The VSSESSTAT view shows cumulative statistics for individual sessions.
Historically, cumulative statistics would exist only until instance shutdown, at which time they were lost. As you will see later in this chapter, the AWR allows many cumulative statistics to persist—even through database shutdowns—to provide cumulative statistics covering the database since its inception (or, at least, since migration to Oracle 10g).
Metrics are statistics that represent the rate of change in a cumulative statistics category. They are generally measured against a fixed unit type such as time, database calls, transactions, and so on. For example, the number of transactions per second would be one type of metric. Metrics are computed by the MMON process at varying intervals. Some are computed as often as once a second while others may be computed only once every 30 minutes. They represent delta values between snapshot periods.
Sampled data represents a sampling of the current state of all active sessions. These statistics are collected by the ASH sampler, which is covered later in this section.
8.1.1.2. New Statistics in Oracle 10g
Oracle has added many new statistics types in the new 10g release. The following list, though not exhaustive, contains many of the most important ones and their associated V$ view (these statistics are also maintained in the AWR):
Time model statistics, which can be viewed with the V$SYS_TIME_MODEL view:
DB CPU
DB TIME
JAVA EXECUTION ELAPSED TIME
PL/SQL COMPILATION ELAPSED TIME
PL/SQL EXECUTION ELAPSED TIME
BACKGROUND CPU TIME
BACKGROUND ELAPSED TIME
CONNECTION MANAGEMENT CALL ELAPSED TIME
FAILED PARSE (OUT OF SHARED MEMORY) ELAPSED TIME
FAILED PARSE ELAPSED TIME
HARD PARSE (BIND MISMATCH) ELAPSED TIME
HARD PARSE (SHARING CRITERIA) ELAPSED TIME
HARD PARSE ELAPSED TIME
INBOUND PL/SQL RPC ELAPSED TIME
PARSE TIME ELAPSED
SEQUENCE LOAD ELAPSED TIME
SQL EXECUTE ELAPSED TIME
Operating system statistics, which can be viewed with the VSOSSTAT view:
AVG_BUSY_TICKS
AVG_IDLE_TICKS
AVG_IN_BYTES
AVG_OUT_BYTES
AVG_SYS_TICKS
AVG_USER_TICKS
BUSY_TICKS
IDLE_TICKS
IN_BYTES
NUM_CPUS
OUT_BYTES
RSRC_MGR_CPU_WAIT_TIME
SYS_TICKS
USER_TICKS
Wait statistics, which can be viewed with the V$SERVICE_STATS view:
APPLICATION WAIT TIME
DB CPU
CONCURRENCY WAIT TIME
USER COMMITS
USER I/O WAIT TIME
CLUSTER WAIT TIME
While AWR collects the majority of base statistics (statistics collected in memory), not all legacy statistics have been converted to work with AWR. The following list shows the statistics that AWR collects and processes:
Object statistics that determine both access and usage statistics of database segments
Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
Some of the system and session statistics collected in the VSSYSSTAT and VSSESSTAT views
Some of the Oracle optimizer statistics, including those used by Oracle for self-learning and self-tuning
Most expensive SQL statements (those that are producing the highest load on the system), based on criteria such as CPU time and elapsed time
ASH statistics, capturing activity from recent sessions
8.1.2. Workload Repository
The workload repository element of AWR adds persistence to the statistics collection facility by providing a set of tables where performance data can be stored and accessed by other CMI components. It also acts as a repository for other CMI components to store persistent data.
NOTE
The AWR consists of both the statistics collection facility and the workload repository. For simplicity, the general term AWR will be used when referring to the workload repository element.
In the following sections, you'll learn about the workload repository, how to enable it, and disk space considerations relating to it.
8.1.2.1. An Overview of the Workload Repository
The AWR adds persistence to the statistics collection facility. On a regular basis, the MMON process transfers cumulative statistics in memory to the workload repository tables on disk. This ensures that statistics can survive through instance crashes, or aren't lost when they are replaced by newer statistics.
The workload repository also ensures that historical data will be available for baseline comparisons, trend analysis, and to troubleshoot intermittently occurring problems. Before AWR, collecting this type of data required manual collection and management using Statspack or custom code.
Workload repository data is owned by the SYS user and is stored in the SYSAUX tablespace. In fact, the repository is one of the main clients of the SYSAUX tablespace. The data is stored in a collection of tables, all of which are named beginning with WR. By listing the table names, it is easy to get an idea of the types of data stored in the repository, as shown in this partial listing:
SQL> select table_name from dba_tables where tablespace_name = 'SYSAUX' and substr(table_name, 1,2) = 'WR' and rownum <= 20 order by 1;
TABLE_NAME WRH$_ACTIVE_SESSION_HISTORY_BL WRH$_BG_EVENT_SUMMARY WRH$_BUFFER_POOL_STATISTICS WRH$_CLASS_CACHE_TRANSFER_BL WRH$_CR_BLOCK_SERVER WRH$_CURRENT_BLOCK_SERVER WRH$_DATAFILE WRH$_DB_CACHE_ADVICE_BL WRH$_DLM_MISC_BL WRH$_ENQUEUE_STAT WRI$_OPTSTAT_HISTGRM_HISTORY WRI$_OPTSTAT_HISTHEAD_HISTORY WRI$_OPTSTAT_IND_HISTORY WRI$_OPTSTAT_OPR WRI$_OPTSTAT_TAB_HISTORY WRI$_SCH_CONTROL WRI$_SCH_VOTES WRI$_SQLSET_BINDS WRI$_SQLSET_DEFINITIONS WRM$_BASELINE
Once in the repository, the statistics can be accessed using data dictionary views. This makes them available to other Oracle manageability resources such as ADDM. They are also available to the users, and easily accessible should a third party wish to design their own monitoring tools.
8.1.2.2. 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 by 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 database creation time. No manual action is required.
8.1.2.3. AWR Space Considerations
So how much disk space will be consumed by AWR using the default snapshot interval and retention period? 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 frequent snapshots) or decreasing the retention period. Technically, you could also decrease your active sessions, but undoubtedly your users would not appreciate it. Any of these changes will reduce the disk space required, but they could also have a negative effect on other aspects of the system. 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
It is the responsibility of the MMON process to purge data from the repository when it has reached the end of the retention period. This is accomplished on a nightly basis through an automated purge task defined in the Scheduler. The MMON process will use best efforts to complete the task in the designated management window.
Snapshots are removed in chronological order, for the most part. The exception is snapshots that belong to a baseline. These are retained (even past the retention period) until their associated baselines are removed.
8.1.3. Active Session History
In order to provide statistics on current session activity, Oracle 10g has introduced Active Session History (ASH). dIn the following sections, you'll look at ASH and how it interacts with the AWR. You'll learn about the types of statistics that ASH captures and how to calculate ASH memory usage size. Lastly, you will be introduced to ASH views and look at how ASH data is stored in the AWR.
8.1.3.1. Sizing ASH
ASH is actually a first-in, first-out (FIFO) buffer in memory that collects statistics on current session activity. These statistics are gathered by extracting samples from VSSESSION 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 the System Global Area (SGA) and its size is fixed for the lifetime of the instance. Its size is calculated using the following calculation:
The lesser of:
Total number of CPUs × 2MB of memory
5 percent of the Shared Pool size
So, on a 16-processor system, with a Shared Pool size of 500MB, the size of the ASH buffer could be calculated as follows:
ASH desired size: 16 × 2MB = 32MB
5 percent of Shared Pool size: 500MB × 5% = 25MB
Final ASH size: 25MB
Because the desired ASH size exceeded 5 percent of the Shared Pool size, Oracle will choose the lesser size. There are, therefore, only two ways to increase the ASH buffer size:
Increase the number of CPUs.
Increase the Shared Pool size.
8.1.3.2. ASH Statistics
As stated previously, ASH samples statistics from all active sessions once every second. It can accomplish this effectively by directly accessing Oracle's internal memory structures (the V_$SESSION fixed table) so that no I/O is required. ASH, as the name implies, is only interested in active sessions. It does not sample inactive sessions.
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 the session is waiting)
NOTE
SQL_ID is a hash value that uniquely identifies a SQL statement in the data base. SQL_ID is new in Oracle 10g.
8.1.3.3. ASH Views
The statistics in ASH can be viewed using the V$ACTIVE_SESSION_HISTORY fixed view. One row will be displayed for each active session per sample.
8.1.3.4. ASH and AWR
Because the data in the 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 one of two ways.
Every 30 minutes, the MMON process flushes the ASH buffer of all data. In the process, it filters some of the data into the AWR. Due to the high volume of data, the MMON process doesn't filter all of the ASH data into the AWR. Instead, it filters out much of the data and retains only key elements.
If the ASH buffer fills in less than 30 minutes, the Memory Monitor Light (MMNL) process will flush out a portion of the buffer (to make room for the new statistics) and filter a portion of the flushed data to the AWR.
8.1.3.5. Using AWR
The primary interface for AWR is through Oracle Enterprise Manager (EM) Database Control. The link to access AWR can be found on the Administration page. Under Workload, click the Workload Repository link. From this page, you can manage AWR settings and snapshots.
Oracle also provides the DBMS_WORKLOAD_REPOSITORY package. This Application Program Interface (API) allows management of all AWR functionality, just as Database Control provides.
NOTE
Examples shown in this chapter will focus primarily on the use of DBMS_WORKLOAD_REPOSITORY.
The DBMS_WORKLOAD_REPOSITORY package consists of the procedures listed in Table 8.1.
| Name | Description |
|---|---|
| CREATE_SNAPSHOT | Creates 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 |
8.1.4. AWR Snapshots
AWR collects performance statistics by taking snapshots of the system at specific intervals. In the following sections, we'll teach you how to determine the frequency of these snapshots. You'll also look at how you can create, modify, and drop snapshots, as well as the implications of each.
8.1.4.1. Using Snapshots
The snapshot pulls information from the fixed tables that hold performance statistics in memory, so, like ASH sampling, snapshot collection is not resource-intensive.
By default, AWR generates performance data snapshots once every hour. This is known as the snapshot interval. It also retains the snapshot statistics for seven days before automatically purging them. This is known as the 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; SNAP_INTERVAL RETENTION +00000 01:00:00.0 +00007 00:00:00:0
As the query results show, the snapshot interval is set to one hour, and the retention period is set at seven days (the default values).
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 Real Application Clusters (RACs). 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.
8.1.4.2. Creating Snapshots
There may be times when you want to collect snapshots that don't fall neatly into a schedule. For example, you may want to take snapshots during heavy loads, but the loads don't occur on a strict schedule. In that situation, you may want to create a snapshot manually. Here's an example of how to do that:
BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END;
As you can see, the CREATE_SNAPSHOT function requires no parameters. It simply takes a snapshot of the system in its current state.
8.1.4.3. Modifying Snapshot Frequency
As mentioned earlier in this chapter, the snapshot interval and retention period can be modified from their default settings, though the risk of impacting Oracle's diagnostic tools needs to be weighed carefully.
NOTE
If you are unsure of the current AWR settings, you can query the DBA_HIST_WR_CONTROL view, as shown earlier in the "Using Snapshots" section.
To make changes, use the procedure DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPHOT_SETTINGS. The parameters of this procedure are listed in Table 8.2.
Both parameters RETENTION and INTERVAL are measured in minutes, so in the following example, you are setting the retention period to 10 days (14,400 minutes) and the snapshot interval to 45 minutes. The MODIFY_SNAPSHOT_SETTINGS procedure, as well as several others in this package, will also accept an optional database identifier. This parameter defaults to the local database identifier, so it won't be included in the examples:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( RETENTION => 14400, INTERVAL => 45); END;
8.1.4.4. Dropping Snapshots
To remove snapshots from the AWR, the DROP_SNAPSHOT_RANGE procedure is used. This procedure is designed to drop a range of snapshots, but it can also be used to drop individual snapshots by using the same snapshot ID for both the HIGH_SNAP_ID and LOW_SNAP_ID parameters. This is useful because no procedure exists to drop an individual snapshot. The parameters for this procedure are listed in Table 8.3.
| Parameter | Description |
|---|---|
| LOW_SNAP_ID | The lowest snapshot ID of range to drop |
| HIGH_SNAP_ID | The highest snapshot ID of range to drop |
| DBID | Optional database ID |
In the following example, snapshots 316 through 320 would be dropped from the repository:
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID =>316, HIGH_SNAP_ID =>320);
8.1.5. AWR Baselines
A baseline is the definition of a pair of snapshots that denote a significant workload period. For example, snapshots could be taken at the start and end of a normal payroll processing job. These snapshots could then be used to define a baseline to compute performance deltas for a normal payroll processing job. This baseline can be retained for comparison to current system performance.
In this section, you'll learn how baselines are used and how to create and delete them.
8.1.5.1. Using Baselines
Baselines (and their associated snapshots) are not aged out of the workload repository using the RETENTION setting. Instead, they are kept for historical comparisons to current system behavior. The baseline must be dropped manually, at which time the related snapshots will also be removed.
Besides their analytical value, baselines can also be used to define threshold settings for Oracle's server-generated alerts facility. This functionality allows you to be notified whenever a threshold has been exceeded (for example, when a disk is 97 percent full).
AWR baselines also make an excellent tool for application performance and scalability testing. For example, a payroll application may be baselined with a small amount of test data to begin with. Then, as the test bed is increased, comparisons can be made to determine how well the application is scaling.
8.1.5.2. Creating Baselines
To create a baseline, the CREATE_BASELINE procedure is used. As you should remember, a baseline requires a beginning and ending snapshot ID, as well as a name. You can also specify an optional database identifier (DBID).
Table 8.4 lists the parameters for the CREATE_BASELINE procedure.
| Parameter | Description |
|---|---|
| START_SNAP_ID | Lowest snapshot ID in the range |
| END_SNAP_ID | Highest snapshot ID in the range |
| BASELINE_NAME | Unique name for the baseline |
| DBID | Optional database ID |
For example, if you want to baseline the job that creates nightly reports, you could use the following example, assuming that snapshots 42 and 43 mark the beginning and ending of the job:
BEGIN
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID =>42,
END_SNAP_ID =>43,
BASELINE_NAME => 'REPORTS'),
END8.1.5.3. Dropping Baselines
When a baseline is no longer needed, the DROP_BASELINE procedure can be used. This procedure removes the baseline and if specified, cascades to remove the associated snapshots.
Table 8.5 lists the parameters for the DROP_BASELINE procedure.
In this example, the baseline REPORTS will be dropped, but the associated snapshots will remain in the repository:
Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE('REPORTS',FALSE);| Parameter | Description |
|---|---|
| BASELINE_NAME | Name of the baseline to be dropped |
| CASCADE | Boolean to determine whether the associated snapshots will be dropped (default = FALSE) |
| DBID | Optional database ID |
8.1.6. Using AWR Views
AWR information can be viewed through a collection of data dictionary views provided by Oracle. These views provide a comprehensive overview of both AWR configuration settings and views of the various types of data stored inside AWR.
The following views are available:
DBA_HIST_ACTIVE_SESS_HISTORY Displays 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 the AWR.
For example, if you want to create a baseline for a given period of time, you can query the DBA_HIST_SNAPSHOT view to determine which SNAP_ID range corresponds to that period of time, as shown here:
SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1; SNAP_ID BE6IN_INTERVAL_TIME END_INTERVAL_TIME 380 14-DUN-04 11.00.37.796 AM 14-DUN-04 12.00.11.515 PM 381 14-DUN-04 12.00.11.51S PM 14-DUN-04 01.00.54.109 PM 382 14-DUN-04 01.00.54.109 PM 14-DUN-04 02.00.34.906 PM 383 14-DUN-04 02.00.34.906 PM 14-DUN-04 03.01.07.062 PM 384 14-DUN-04 03.01.07.062 PM 14-DUN-04 04.00.41.343 PM 38S 14-DUN-04 04.00.41.343 PM 14-DUN-04 05.00.19.546 PM
386 14-DUN-04 05.00.19.546 PM 14-DUN-04 06.00.57.187 PM 387 14-DUN-04 06.00.57.187 PM 14-DUN-04 07.00.35.734 PM 388 14-DUN-04 07.00.35.734 PM 14-DUN-04 08.00.13.734 PM 389 14-DUN-04 08.00.13.734 PM 14-DUN-04 09.00.51.812 PM 390 14-DUN-04 09.00.51.812 PM 14-DUN-04 10.00.30.234 PM
8.1.7. Using AWR Reports
Oracle offers a standard summary report that can be run at any time against the statistics stored in the AWR. This report provides an analysis of system performance over a specified period of time.
The report is run through one of two SQL*Plus scripts:
awrrpt.sql, which generates a text file report
awrrpti.sql, which generates an HTML version of the report
These scripts reside in $ORACLE_HOME/rdbms/admin (on Unix systems) or %ORACLE_HOME% dbmsadmin (on Windows systems).
The scripts will prompt for several options:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html
At this prompt, you can enter the type of report you want (either text for plain text or html for HTML output. The default is HTML. For this example, html is specified:
Enter value for report_type: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ---------- ---------- ----------- * 268115148 1 LNX1 " lnxl " peabody Enter value for dbid: 268115148 Using 268115148 for database Id Enter value for inst_num: 1 Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent (n) days of
snapshots being listed. Pressing <return> without specifying a number
lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
--------- ------------- ------------ ------------- -----
lnx1 LNX1 290 31 Jul 2004 00:00 1
291 31 Jul 2004 01:00 1
292 31 Jul 2004 02:00 1
293 31 Jul 2004 03:00 1
294 31 Jul 2004 04:00 1
295 31 Jul 2004 05:00 1
Specify the Begin and End Snapshot IdsThe report now prompts you for a range of snapshots to include in the report. For the example, the range specified is 290 through 295:
Enter value for begin_snap: 290 Begin Snapshot Id specified: 290 Enter value for end_snap: 295 End" Snapshot Id specified: 295 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_290_345.html. To use this name, press <return> to continue, otherwise enter an alternative.
Lastly, you are prompted for a report name. You can accept the default shown earlier in this example or type the name you wish to call it:
Enter value for report_name: report.html Using the report_name report.html
The AWR report provides a detailed overview of system performance. Figure 8.1 shows a section of the report in HTML format.
Figure 8.1. The AWR report in HTML format
