In my previous article, titled, “An Oracle Instructor’s Guide to Oracle 10g,” I presented a high-level overview of some of the new features in Oracle’s latest release. I described the first article as “a view from 30,000 feet.” I also promised that we would begin to focus a little more deeply into some of the new features in future articles. Consider this article to be the first of many in-depth discussions of Oracle10g's new features.

Most of the fanfare surrounding this release centers on the grid capabilities of Oracle’s latest offering. But as with Oracle9i, Oracle10g contains enhancements in virtually all areas of the database server. Since you will undoubtedly be inundated with information on Oracle10g’s grid capabilities, let’s be different and start our analysis of Oracle10g with a look at Oracle10g's manageability features. We’ll begin this series by looking at the data warehouse of the Oracle10g database, the Automatic Workload Repository.

The Problem: Too Much Time Spent on Database Management Activities

Oracle states that DBAs spend more than 50 percent of their time performing on-going systems-management activities. These activities include:

• Application and SQL Performance Management — Reviewing response times, SQL access path analysis and tuning, schema object performance analysis, index creation, and cost-based optimizer statistics collection.
• System Resource Management — Database memory pool sizing, CPU utilization, and process management.
• Space Management — Growth trend analysis, fragmentation analysis, disk configuration, segment space utilization monitoring, datafile sizing, and Oracle data object segment sizing.
• Storage Management — Disk configuration, determining stripe sizes, and I/O bandwidth monitoring.
• Backup and Recovery Management — Backup monitoring, disaster recovery planning, backup strategy planning, writing operating system scripts to perform database backups, and monitoring the database Mean Time To Recover (MTTR).

Oracle10g assumes that some of these aforementioned activities reduce the amount of time administrators spend performing daily management tasks. Oracle10g’s new manageability architecture is designed to automatically inform DBAs of any performance, space management, or resource allocation issues. In addition, the Oracle10g database can provide administrators with suggestions on how to resolve these types of problems. The database provides these benefits through its Common Manageability Infrastructure.

The Resolution — The Common Manageability Infrastructure

The Common Manageability Infrastructure provides the Oracle10g database with self-tuning capabilities. The database collects self-tuning and problem detection statistics that are used as input to automated and DBA-initiated performance advisories. The captured statistics are also used as input to Oracle10g’s server-generated early warning alert system.

The Common Manageability Infrastructure (refer to Image 1) can be divided into the following four main components:

• Automatic Workload Repository (AWR) — The center of the Common Manageability Infrastructure provides services to collect, store, maintain, process and provide access to statistics for self-tuning and problem detection.
• Server-Generated Alerts — Oracle10g provides early warning mechanisms to alert administrators of possible error conditions. The database now collects numerous metrics that were previously collected by Oracle Enterprise Manager. Administrators are able to select from 161 server-generated alerts.
• Advisory Framework — The Advisory Framework consists of various server components that provide feedback and recommendations about database resource utilization and performance. These feedback advisories can be initiated by the DBA or by the database server. Oracle10g’s Automatic Database Diagnostic Monitor (ADDM) automatically identifies performance bottlenecks and makes recommendations to resolve them.
• Automated Tasks — A sophisticated job scheduler allows administrators to schedule routine administrative tasks such as cost-based optimizer statistics gathering. Administrators are also able to create complex job streams that execute PL/SQL, Java and C programs.

The remainder of this article will focus on the Automated Workload Repository. (In our next article we’ll continue our discussion of the Common Manageability Infrastructure by taking an in-depth look at the Advisory Framework, the Automated Task System, and Server-Generated Alerts.)

Automatic Workload Repository

Oracle describes the Automatic Workload Repository as the “data warehouse of the Oracle10g database.” It is used as the statistics data source for all other components of the Common Manageability Infrastructure (refer to figure 2).

The Automatic Workload Repository consists of two main components: In-memory Statistics and Repository Snapshots.

AWR In-memory Statistics

Oracle10g provides a collection facility to collect base statistics and store them in memory. Some of the statistics collected by AWR’s collection facility are as follows:

• Object Statistics — Object access and usage statistics of application data segments including read and write activity and data waits.
• Time-Model Statistics — Connection management, SQL statement parse, PL/SQL compilation and SQL and PL/SQL execution.
• OS Statistics — CPU and memory utilization.
• Wait Classes — I/O, CPU concurrency, COMMIT, and scheduler waits.
• SQL Statistics — CPU and elapsed SQL execution times, wait-class times, and PL/SQL Java times.
• System and Session Statistics — Collected and stored in the V$SYSSTAT and V$SESSTAT dynamic performance views.
• Current Session Activity — The Active Session History component of the Automatic Database Diagnostic Monitor samples recent session activity.

Active Session History

Since the AWR, by default, takes snapshots every 30 minutes (discussed below), performance information could be up to 30 minutes old. As a result, snapshots do not contain enough information to allow administrators to perform analysis on the active workload currently being performed in the database system. Oracle10g contains a new internal utility, called Active Session History, to provide administrators with access to current performance information.

Active Session History samples data from the V$SESSION dynamic performance table every second and stores the information in V$ACTIVE_SESSON_HISTORY. The information contains the events for which current sessions are waiting. The information pertains to active sessions only; information from inactive sessions is not recorded. The view contains one row per active session for each one-second sample. Administrators are able to access V$ACTIVE_SESSON_HISTORY as they would any other V$ dynamic performance table.

It is important to note that, like V$SQLAREA, V$ACTIVE_SESSION_HISTORY is a rolling buffer in memory. Oracle’s internal documentation states that current workload analysis most often requires detailed performance statistics on activity within the last five- to ten-minute time period. As new information is added, earlier information contained in the view will be removed. Active Session History consumes two megabytes of memory per CPU and is fixed for the lifetime of the instance.

AWR Snapshots

AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined WR schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.

Storing statistics generated by AWR snapshots in the database allows administrators to analyze problems that occurred in the past. This historical information will help administrators finally answer questions like, “My program ran long two days ago; can you fix it?”

The stored statistics also allow the database to compare its current performance to a stored baseline. The database is then able to initiate its own early-warning performance alerts when baseline measurements are exceeded. Automatic Database Diagnostic Monitor (to be discussed in the next article in this series) uses the snapshot information to automatically identify performance problems and make recommendations to correct them. In addition, administrators are able to use the historical data to perform performance trending analysis.

The snapshot mechanism can be loosely compared to its Statspack predecessor. Statspack collected database statistics and stored them in the PERFSTAT schema. The major differences between Statspack and AWR snapshots are:

• AWR snapshots are scheduled every 30 minutes by default. A new background server process called MMON is responsible for initiating the snapshot mechanism. Administrators can manually adjust the snapshot interval by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the INTERVAL parameter input variable. In addition, administrators can manually initiate AWR snapshots by invoking a PL/SQL stored procedure.
• The depth and breadth of statistics collected (discussed previously in this article). During snapshot processing, MMON transfers the in-memory version of the statistics to the permanent statistics tables.
• The AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week’s worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

Metrics vs. Base Statistics

Base statistics are the raw data points collected by AWR. An example of a base statistic would be the number of reads per second performed on a particular database data object. Metrics are computed statistics derived from base statistics. The MMON server process uses the base statistics as input to calculate their corresponding metrics. These computed statistics are used by internal components for system health monitoring, early warning problem detection, and self-tuning. An example of a metric would be the average number of physical reads per second performed on a database data object within the last 30-minute time period. Pre-computing metrics allows internal components to quickly compute the rate of change of system statistics.

In the past, administrators who wanted to determine the impact a specific workload had on the database used Statspack to capture baseline statistics before the run, ran the workload being evaluated, ran Statspack again to capture statistics after the run, then manually computed the rate of change for the particular base statistic. With pre-computed metrics, administrators only need to run the workload, then select the desired metric values from the new V$ metric tables.

Statistics Levels

Administrators are able to control the breadth and depth of statistics to capture by setting the STATISTICS_LEVEL initialization parameter. The parameter has three different possible values:

• Basic — The collection of AWR statistics and computation of metrics is deactivated.
• Typical (default value) — Partial statistics are collected. AWR collects only those statistics required to provide base-level monitoring capabilities. This is the setting currently recommended by Oracle.
• All — All possible statistics are captured. The additional statistics are used for manual SQL diagnosis. The ALL setting does incur additional overhead.

AWR Snapshot Baselines

Administrators create snapshot baselines to capture and measure performance statistics for a particular time period. The baseline data can then be used to compare current system performance against a time period when database performance was determined to be optimal. Threshold-based alerts can be set up to notify administrators when current system performance deviates from the statistics and metrics stored in the baseline snapshot.

The baseline snapshot is defined on a pair of SNAP_IDs. The CREATE_BASELINE stored procedure accepts the START_SNAP_ID and END_SNAP_ID variables as input to create the snapshot baseline. Administrators use the BASELINE_NAME variable to uniquely identify the baseline snapshot. AWR does not delete snapshots defined as baseline snapshots.

AWR Report Generation

The AWR contains a reporting tool that can also be loosely compared to a Statspack report. The report summarizes information on the statistics stored in the workload repository for a given time period. Administrators run one of the two following SQL statements to create the AWR reports:

• SWRFRPT.SQL — Creates a text report that describes overall system performance over the time period selected.
• SWRFRPTH.SQL — Provides the same information as SWRFRPT.SQL in HTML format with navigational links.

Administrators are required to connect to the database using the AS SYSDBA privilege to run the above SQL statements. The script prompts the user for the number of days of snapshots to choose from. The script then prompts the user for the beginning and ending SNAP_IDs and the output file name. Oracle10g also contains numerous DBA_views to provide direct access to AWR base statistics and metrics.

Database Feature Usage and General Database Statistics

AWR also tracks database feature usage statistics and high-water marks of certain database statistics. The MMON process records this information once a week by sampling information from the data dictionary. Administrators can access this information by selecting from the DBA_FEATURE_USAGE_STATISTICS and DBA_HIGH_WATER_MARK_STATISTICS data dictionary views.

Conclusion

I hope you enjoyed our first in-depth discussion of Oracle10g. The Automatic Workload Repository may not be as exciting as the server-generated early warning alerts and automatic tuning recommendations provided by ADDM, but it is important for us to understand the information used by these new features. In Part 2, we’ll cover all of the utilities that use AWR information as input. Thanks for reading, and I’ll see you in class!

--

Christopher Foot has been involved in database management for over 18 years, serving as a database administrator, database architect, trainer, speaker, and writer. Currently, Chris is employed as a Senior Database Architect at RemoteDBA Experts, a remote database services provider. Chris is the author of over forty articles for a variety of magazines and is a frequent lecturer on the database circuit having given over a dozen speeches to local, national and international Oracle User Groups. His book titled OCP Instructors Guide for DBA Certification, can be found at http://www.dba-oracle.com/bp/bp_book14_OCP.htm.