Introducing the AWR

Introducing the AWR

The Automatic Workload Repository (AWR) is a new infrastructure component that is at the heart of the collection and processing of the vast amount of performance data in Oracle Database 10g. The data collected by AWR forms the basis for most of the problem-detection and self-tuning mechanisms that Oracle Database 10g provides. In fact, the performance-alert mechanisms rely on this data, as do many of the performance advisors. The Automatic Database Diagnostic Monitor (ADDM) uses this data as well, performing scheduled analysis of the data collected by AWR.

AWR consists of two components: in-memory performance statistics, accessible through V$ views, and snapshots of these V$ views "persisted" in the database that record the historical values.

AWR Collection

AWR consists of the following collections:

• Active Session History (ASH)

• High-load SQL statements

• Time model statistics at the database level as well as at the session level for CPU usage and wait classifications

• Object statistics that record usage as well as access counts for segments such as tables, indexes, and other database objects

• Traditional V$SESSTAT, V$SYSSTAT, V$SYSTEM_EVENT, and V$SESSION_EVENT data

Licensing Information

As per the terms laid out in the Oracle Database 10g Licensing Information manual, AWR, ADDM, performance monitoring, and alerting are part of the Oracle Diagnostic Pack, which in itself is part of the Oracle Management Packs. Note that these management packs can be purchased only with the Enterprise Edition of the Oracle Database 10g software. Also note that AWR requires the use of the Partitioning option, which is also available only with the Enterprise Edition.

In this list, the collection for all but the last item is new.

Unlike other Oracle background system processes such as SMON (System Monitor) or PMON (Process Monitor), the death of these performance-related background processes does not stop or abort the database. The health of these background processes does not affect the main system because these processes are not really deemed essential from an operational point of view. When these processes die, an appropriate message is posted in the alert.log and processing continues. Performance data, however, stops being collected when these processes die for any reason.

So how does all of this happen? The Oracle kernel allocates a small but distinct portion of the System Global Area (SGA) to buffers that are dedicated to holding session history and other AWR-related information. These in-memory buffers are updated by the MMNL and MMON background processes via sampling of session information and counters. The Memory Monitor Light (MMNL) process, new to Oracle Database 10g, performs tasks such as session history capture and metrics computation and stores this information in these buffers. It also persists the statistics in these buffers to disk as needed in the form of AWR tables. The Memory Monitor (MMON) process performs various background tasks, such as issuing alerts whenever a given metric violates its threshold value and taking snapshots by spawning additional process (MMON slaves), among others. Together, they are responsible for the statistics, alerts, and other information maintained by AWR. These statistics are made permanent in the AWR, which consists of a number of tables.

By default, these background jobs automatically generate snapshots of the performance data once every half hour and flush the statistics to the workload repository. The Automatic Database Diagnostic Monitor (ADDM) then kicks in to analyze the data from the immediately prior and current snapshots, and to highlight any performance issues or problems. You can perform all this manually as well, and you will see how to do that later in this chapter.

Ultimately, because SQL executing in a user or background session produces database work and hence load, AWR works to compare the difference between snapshots to determine which SQL statements should be captured based on their effect on the system load. You saw examples of such SQL in the previous chapter when looking at how OEM uses this data. This reduces the number of SQL statements that need to be captured over time, while still capturing the essential ones.

Out of the box, AWR serves as a performance warehouse for the Oracle Database 10g, generating, maintaining, and reporting these performance statistics. AWR purges its own data in a scheduled manner, thus self managing itself.

Memory Allocated to AWR Buffers

There is no clear documentation as to how much memory is used by AWR. There are some clues, however, that can be seen from the V$SGASTAT view, which lists the various subsections of memory used within the System Global Area (SGA). We were able to see two distinct areas, both within the Shared Pool: event statistics per sess and ASH buffers. Each consumed about 1.5% of the combined SGA, and seemed to be dependent on other parameters such as sessions. We will mention a MetaLink Note later on that lists how ASH buffers can be determined, but there is no information about sizing for the AWR buffers.

Comparison to STATSPACK

In many ways, AWR rose from the foundation laid by STATSPACK. The concepts are similar: Capture snapshots of required internal V$ views, store them, and analyze them. There are some major differences between AWR and STATSPACK, however. We hinted at this in the previous chapter, and it is worth looking at both the similarities and differences here:

• Both STATSPACK and AWR use snapshots to store current performance data exposed in selected V$ views. Both types of snapshots are uniquely identified via the SNAP_ID column. Similarly named tables store the same type of data in both environments.

• Both STATSPACK and AWR produce similar reports using packaged SQL*Plus scripts. STATSPACK uses spreport.sql, while AWR uses a number of AWR-specific scripts detailed later on in this chapter.

• While STATSPACK needs to be installed manually, AWR is installed, configured, and managed by default in a standardized manner.

• STATSPACK snapshots impose a reasonable load during collection. However, AWR collections occur continually and are offloaded to selected background processes, allowing for smoother, less perceptible, and less disruptive progress.

• STATSPACK analysis is complex and needs a skilled eye and an adequate level of experience to detect problems. AWR, along with ADDM, runs continually, generates alerts, and performs analysis automatically.

• STATSPACK is not accessible via a GUI such as OEM for viewing or management, whereas AWR is accessible both via the OEM GUI as well as via SQL and PL/SQL for viewing and management.

• The way high-impact or high-load SQL is captured in AWR is quite different from STATSPACK. While STATSPACK scans V$SQL for high-load SQL based on a certain set of defaulted lower limits, such as on number of logical and physical I/Os per stored SQL statement, AWR recognizes high-load SQL as it occurs. This enables accurate capture of the right SQL data as it occurs, rather than collecting high-load SQL from V$SQL, which may capture SQL that occurred prior to, and thus outside of, the snapshot period. This was a major weakness in STATSPACK.

• STATSPACK data is stored in the PERFSTAT schema in any designated tablespace, while AWR data is stored in the SYS schema in the new SYSAUX tablespace.

These similarities have prompted the renaming of the AWR/ADDM combination to "STATSPACK on Steroids"a steroid we would heartily recommend!

STATSPACK in Oracle Database 10g

Curiously enough, STATSPACK is also available for Oracle Database 10g! The PERFSTAT schema is installed by default, although no STATSPACK related objects are created. The standard sp*.sql scripts are available in the RDBMS\ADMIN directory.

AWR Storage and Reporting

All the AWR objects (tables and their indexes) start with one of the following prefixes:

• WRH$ tables, which store workload repository history. Some of these tables are range-partitioned.

• WRI$ tables, which store workload repository internal details.

• WRM$ tables, which store workload repository metadata.

All these tables reside in the SYSAUX tablespace. In fact, in Oracle Database 10g Release 1, there are 140 of these tables, and many of the larger ones are range-partitioned by DB_ID and SNAP_ID along with their indexes, which helps during purging of older snapshot information. The table names have suffixes that are the same or similar to their equivalents in STATSPACK's PERFSTAT schema. For example, the statistical contents and use of the STATS$FILESTATXS table of STATSPACK and the WRH$FILESTATXS table of AWR are the same; they store snapshot detail information from V$FILESTAT. Hence, understanding their use is simpler if you already know of their use in STATSPACK.

A number of views can access some of these tables. Generally, these views start with the DBA_HIST prefix, and some of them are detailed in Table 11.1. You looked at some of the base views that are captured in the snapshot by AWR in the previous chapter. This list is not comprehensive, but is indicative of every type of view.

Table 11.1. Some AWR Views

View Name	Description
DBA_HIST_DATABASE_INSTANCE	Displays constant database instance information such as the database and instance ID and names as well as startup time and the last ASH sample ID. You can also use this view to determine shutdown/startup times.
DBA_HIST_SNAPSHOT	Displays the details of currently available AWR snapshot information, including the start/end time, the elapsed time for flushing the data to disk, the snap level, and the error count. For error details, see the DBA_HIST_SNAP_ERROR view.
DBA_HIST_WR_CONTROL	Displays the current snapshot interval and retention period.
DBA_HIGH_WATER_MARK_STATISTICS	Displays high water marks and last value for object statistics, such as the number of user tables, indexes and partitions, user sessions, and so on. Similar to the V$LICENSE view of previous versions.
DBA_HIST_SEG_STAT	Displays snapshot-wise object statistics including the number of logical and physical reads and writes, ITL waits, and so on. The current-at-snapshot value as well as the delta difference for the snapshot period is displayed as well.
DBA_HIST_JAVA_POOL_ADVICE	Displays the historical Java Pool advisory details and exposes the V$JAVA_POOL_ADVICE view.

The amount of space occupied by these AWR objects depends on the following:

• The snapshot interval determines the frequency at which snapshots are captured and stored. A smaller snapshot interval increases the frequency, which in turn increases the volume of data collected and stored by AWR.

• The historical data retention period determines how long AWR data is retained before being purged. Obviously, a longer retention period increases the space required by AWR.

• Because ASH session information is also stored in the AWR, the number of active sessions in the system at any given time also determines the space required.

By default, snapshots are taken every half hour and retained for a period of seven days. You will see later in this chapter, in the section titled "Customizing the AWR," how these intervals can be changed. The space usage can be seen using the query shown in Listing 11.1.

Listing 11.1. AWR Data from the V$SYSAUX_OCCUPANTS View

 SQL> select occupant_name, occupant_desc, space_usage_kbytes   2  from v$sysaux_occupants   3  where occupant_name like 'SM%'; OCCUPANT_NAME   OCCUPANT_DESC                                           SPACE_KB --------------- ------------------------------------------------------- -------- SM/AWR          Server Manageability - Automatic Workload Repository      83,072 SM/ADVISOR      Server Manageability - Advisor Framework                   8,512 SM/OPTSTAT      Server Manageability - Optimizer Statistics History       21,120 SM/OTHER        Server Manageability - Other Components                    8,704 

V$SYSAUX_OCCUPANTS

This new view allows you to view the details of the special occupants of the new SYSAUX tablespace. The occupant name, a short description, the schema owner, the space used, as well the procedure that should be used to move this occupant out to another tablespace is listed. Note that the server manageability objects in Listing 11.1 cannot be moved to another tablespace. Only other objects such as those related to LogMiner, Oracle Spatial, and Oracle Text can be moved using the specified procedure listed under the MOVE_PROCEDURE column. For more details, see MetaLink Note #243246.1, "10G: SYSAUX Tablespace."

A more detailed report of AWR objects, usage, and the like, along with information on Active Session History (ASH), can be produced using awrinfo.sql. This is found in the $ORACLE_HOME/rdbms/admin directory in UNIX or the equivalent %ORACLE_HOME%\RDBMS\ADMIN directory in Windows. A large amount of information is reported under the headings shown in Listing 11.2.

Listing 11.2. Various Heads Under Which AWR Information Is Reported

 (I) AWR Snapshots Information (1a) SYSAUX usage - Schema breakdown (1b) SYSAUX usage - SYS Schema (2) Size estimates for AWR snapshots (3a) Space usage by AWR components (per database) (3b) Space usage within AWR Components (> 500K) (4) Space usage by non-AWR components (> 500K) (5a) AWR snapshots - last 50 (5b) AWR snapshots with errors or invalid (5c) AWR snapshots -- OLDEST Non-Baselined snapshot (6) AWR Control Settings - interval, retention (7a) AWR Contents - row counts for each snapshot (7b) AWR Contents - average row counts per snapshot (7c) AWR total item counts - names, text, plan (II) ASH Usage Info (1a) ASH histogram (past 3 days) (1b) ASH histogram (past 1 day) (2a) ASH details (past 3 days) (2b) ASH details (past 1 day) (2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC) 

Of these, Size estimates for AWR snapshots is quite useful because it enables you to see the effects of increasing or decreasing the snapshot frequency or manipulating the retention periods.

Also note that storage of objects related to ASH is also detailed here. Of interest is the section ASH histogram (past 3 days) (or even ASH histogram past 1 day). This histogram gives a fair indication of the number of sessions active at any point of time during that period.

The SWRF Acronym

If you looked at some of the AWR statistics related control mechanisms such as latches and other elements, you will come across the acronym SWRF. There seem to be no direct published references, but it seems to expand into Statistics Workload Repository Facilities, as per a related MetaLink Note #240052.1. This was probably what AWR was called before the "A" (for Automatic) was prefixed to the name.

Accessing the AWR Snapshots

The AWR snapshots can be accessed using either the OEM screens or directly from a SQL aware tool such as SQL*Plus or iSQL*Plus. When using OEM, you can use either of the following routes:

• Database Control Home (page) -> Administration (breadcrumb menu) -> Automatic Workload Repository (link) -> Snapshots (link)

• Database Control Home (page) -> Performance (breadcrumb menu) -> Snapshots (link) in Additional Monitoring Links (collection of links)

Figure 11.1 shows the Snapshots page. Note that you can use the drop-down menu next to the Go button to perform any of the following tasks:

• Create a preserved snapshot set (also known as a baseline)

• Create an SQL tuning set

• View a report

• Create an ADDM task

• Delete a snapshot range

• Compare timelines

Figure 11.1. AWR snapshots and their details.

You can also use the Create button to create an on-demand snapshot.

The AWR can be accessed from SQL using calls to the DBMS_WORKLOAD_REPOSITORY built-in PL/SQL package to perform required processes. To generate reports, you can use the following SQLs found in the $ORACLE_HOME/rdbms/admin directory in UNIX (%ORACLE_HOME%\RDBMS\ADMIN directory in Windows):

• awrinfo.sql. Displays general AWR information, as described earlier.

• awrinput.sql. Sets up variable information for subsequent AWR reports.

• awrrpt.sql. Generates an AWR report in either HTML or plain text from the instance on which it is run and calls awrrpti.sql when input specification is complete.

• awrrpti.sql. Generates an AWR report in either HTML or plain text from any other instance for which the data is held in this instance. This script finally calls the DBMS_ WORKLOAD_REPOSITORY built-in PL/SQL package, whose source is wrapped and is thus hidden.

You will notice that awrrpt.sql produces a report that is similar to STATSPACK, but has more sections that cover the new views that Oracle Database 10g provides. Some of the new sections are shown in Listing 11.3.

Listing 11.3. Various New Sections in the AWR Report

[View full width]

 Time Model Statistics Operating System Statistics Service Statistics and Service Wait Class Stats SQL ordered by Elapsed Time, CPU Time ( in addition to the SQL ordered by Gets, Reads and  Executions, Parse calls, Sharable Memory and Version count that was previously available) Instance Activity Stats  by Absolute values as well as Derived values Instance Activity Stats - Thread Activity Other advisories for the Buffer Pool, PGA Memory, Shared Pool, Java Pool turned on by default Enqueue Activity Top Segments by Buffer Busy Waits, Row Lock Waits and ITL Waits 

Note that if you don't specify otherwise, invoking the awrrpt.sql script displays only the last three days of snapshot settings. Use awrinput.sql followed by the awrrpt.sql script to report on previous snapshots. This is a little cumbersome, but it does avoid displaying the list of all the previous snapshots as is done with a STATSPACK report.