Automatic Statistics Collection Fundamentals

Automatic diagnosis and correction of existing and potential performance problems is an important feature in Oracle Database 10g. This is mostly accomplished by automatic statistics collection and retention and reporting using ADDM. The automatic statistics collection is done by the Automatic Workload Repository (AWR) as explained in Chapter 1, "Exploring Oracle Database 10g Architecture."

Database statistics are effective in diagnosing various performance problems within the database. When you analyze a performance problem, you look at the cumulative difference (delta) in statistics values between the starting point and the end. These cumulative statistics are available through dynamic performance views like V$SESSTAT and V$SYSSTAT, from the database starting time. When you shut down a database, these cumulative values in the dynamic views will be reset. The AWR automatically collects and retains these cumulative and delta values for most of the database statistics, except for the session level. AWR collects the performance data, processes it, and maintains performance statistics for use by ADDM and other advisors.

In order to learn more about the statistics collection and use of metrics for database performance improvement, let's review the fundamentals on metrics, base statistics, and statistics levels. We mentioned the use of metrics by the database in the section titled "Performance Management Using AWR " in Chapter 1.

Oracle Database 10g also collects another type of statistical data called sampled data using the Active Session History (ASH) sampling mechanism. ASH samples the current state of all active sessions into memory, which is viewed using V$ACTIVE_SESSION_HISTORY view. These samples are also written out of memory by AWR snapshot processing.

Types of Database Statistics

The important database statistics include the following:

• Wait events

• Time-model statistics

• System and session statistics

• Operating-system statistics

Wait Events

Wait events are statistics on server processes that wait for an event to complete before continuing processing. Examples of wait events include latch contention, buffer contention, and so on. The wait events are grouped into different classes (administrative, application, cluster, configuration, network, and so on) for easier analysis.

For analyzing wait-event statistics, use the following views:

• V$ACTIVE_SESSION_HISTORY. This shows active database session activity sampled every second.

• V$SESSION_WAIT. This shows resources or events for which active sessions are waiting.

• V$SESSION. This shows the same wait statistics as V$SESSION_WAIT along with session/connection information.

• V$SESSION_EVENT. This gives a summary of all the events the session has waited for since starting.

• V$SESSION_WAIT_CLASS. This gives the number of waits and the time spent in each class of wait events for every session.

• V$SESSION_WAIT_HISTORY. This shows the last 10 wait events for each active session.

• V$SYSTEM_EVENT. This gives a summary of all the event waits on the instance after start.

• V$EVENT_HISTOGRAM. This has a histogram of all waits, the maximum wait, and total wait time on an event basis.

• V$FILE_HISTOGRAM. This shows a histogram of times waited during single-block reads for each file.

• V$TEMP_HISTOGRAM. This has a histogram of wait times for single-block reads for each temporary file.

• V$SYSTEM_WAIT_CLASS. This gives time totals for the number of waits and the time spent in each class of wait events in the instance.

Time-Model Statistics

Each Oracle component has its own set of statistics. For analyzing the database as a whole, most Oracle advisors and reports compute statistics in reference to time as a common factor. The V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views provide time-model statistics.

• V$SESS_TIME_MODEL. This contains time-model statistics including the total time spent in database calls at the session level. Time reported is microseconds.

• V$SYS_TIME_MODEL. This contains time-model statistics including the total time spent in database calls at the database level.

DB time is the most important statistic on the total time spent in database calls as a factor of instance workload. It is an aggregate of all CPU and wait times of all non-idle user sessions.

System and Session Statistics

Cumulative database statistics at the system and session level are available through the V$SYSSTAT and V$SESSTAT views. System statistics are available in the following views:

• V$ACTIVE_SESSION_HISTORY. This shows active database session activity sampled every second.

• V$SYSSTAT. This has overall statistics including rollback, physical I/O, logical I/O, and parse data.

• V$FILESTAT. This has detailed file I/O statistics for each file and average read time.

• V$ROLLSTAT/V$UNDOSTAT. These have details on rollback and undo segment statistics for each segment.

• V$ENQUEUE_STAT. This has enqueue-related statistics for each enqueue including the wait time and total number of waits.

• V$LATCH. This gives detailed latch usage statistics for each latch.

You will learn more about these views and their utilization with examples in Chapter 10, "Adopting a New Approach to Tuning."

Operating-System Statistics

Operating-system statistics give information about the usage and performance of hardware components and the operating system. OS statistics are comprised of CPU statistics, virtual-memory statistics, disk statistics, and network statistics. Using commonly available UNIX tools, you can measure the operating-system statistics as follows:

• CPU: iostat, vmstat, mpstat, sar

• Memory: vmstat, sar

• Disk: iostat, sar

• Network: netstat

Database Metrics Revisited

Base statistics represent the raw data that is collected from different database components. Metrics are the secondary statistical data, which are derived from base statistics. Oracle Database 10g has different metrics for session, system, file, and wait-event statistics.

Oracle defines metrics as a set of statistics for certain system attributes. These statistics are calculated and stored by the Automatic Workload Repository (AWR). Each metric has threshold values associated with them. Thresholds are the boundary values against which the metric values are compared. When a metric value reaches its threshold, an alert is generated. Alerts are also generated when a significant change is noted by the clearing of a previous alert, a change in availability of a monitored service, or when a specific error condition or database action occurs.

Most Oracle Database 10g metrics are used to track the rates of change of activities in the database, which in turn can be used for detailed performance analysis. They are used by the various internal components for monitoring system health, detecting problems, and self-tuning.

With metrics, the Oracle database has ready data availability for a component to compute the rate of change of activities. Until Oracle 9i, you had to run statistics before and after a database job to calculate the rate of change for base statistics. Using metrics, you can select these values directly after running the job.

Statistics Levels and AWR Snapshots

If you set values for any of the parametersDB_CACHE_ADVICE, TIMED_STATISTICS, or TIMED_OS_STATISTICSin the database initialization parameter file or by using ALTER SYSTEM or ALTER SESSION commands, the new value(s) will override any corresponding values derived from the STATISTICS_LEVEL parameter.

You just learned that base statistics represent the raw data collected from different database components. The statistics levels are captured in the database by using the STATISTICS_LEVEL initialization parameter.

This parameter can take three values: BASIC, TYPICAL, or ALL. BASIC is the computation of AWR statistics; metrics are turned off. TYPICAL, which is the default value, collects only part of the statistics and represents values typically needed for Oracle Database 10g administration. Setting the STATISTICS_LEVEL parameter to ALL will collect all the database statistics.

You will learn more details about the snapshots in the section "Customizing the AWR" in Chapter 11, "Effectively Using the Automatic Workload Repository."

We mentioned that Oracle database makes a snapshot of all its vital statistics and workload information and stores them in AWR. These snapshots are made every 60 minutes and stored in the AWR for a period of seven days by default. You can change the snapshot frequency as well as data-retention period. The INTERVAL parameter determines the frequency of snapshot generation. The default interval is 60 minutes and the minimum value is 10 minutes. Setting the interval to 0 will disable the automatic capturing of snapshots and is therefore not recommended.

A baseline is uniquely defined on a pair of snapshots to tag data sets for important time periods. A baseline is usually identified by a user-created name or a system-generated identifier. You can also name a baseline manually by running the CREATE_BASELINE procedure with a name and a pair of snapshot identifiers. Each newly created baseline is assigned a baseline identifier, which is unique for the life of the database. Baselines are used to retain snapshot data for comparison with current system behavior and setting up threshold-based alerts. Creation and maintenance of baselines is discussed in Chapter 11 in the section titled "Performing Baselining."

We discussed in Chapter 1 that AWR gets the necessary statistics with the help of the MMON process. MMON writes the required statistics for AWR to disk on a regularly scheduled basis. MMON also purges all older snapshots (in chronological order) on a nightly basis to make room for new database statistics. All snapshots pertaining to baselines are retained until the baselines are removed. MMON performs this purge operation in the management window set by the DBA.

The MODIFY_SNAPSHOT_SETTINGS procedure is used to control the behavior of snapshots. The RETENTION parameter specifies the amount of AWR information retained by the database. The default data-retention period is seven days (10080, for 24x7x60 minutes) and the minimum is one day. The space consumption depends mainly on the number of active sessions in the database. If the RETENTION parameter is set to 0, it will disable the automatic data purging. If AWR detects that the SYSAUX tablespace is out of space, it will automatically delete the oldest set of snapshots and reuse the space.

More details on MODIFY_SNAPSHOT_SETTINGS procedure, AWR data retention, and AWR data purging are given in Chapter 11.

Creating and Dropping Snapshots and Baselines

The DBMS_WORKLOAD_REPOSITORY procedures are useful to manually create, drop, or modify the snapshots and baselines used by the ADDM. The database user should have DBA privileges to execute the procedures.

You can manually create a snapshot using the CREATE_SNAPSHOT option of the DBMS_WORKLOAD_REPOSITORY procedure. This procedure will capture statistics that are at times different from those from automatically generated snapshots. Similarly, you can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. This is discussed in greater detail in Chapter 11 under the sections "Accessing the AWR Snapshots" and "Using the SQL Interface to AWR."

You can also modify the interval, retention, and number of top SQL statements captured in a snapshot generation for a specified database. The INTERVAL parameter (in minutes) affects how often snapshots are automatically generated. The RETENTION parameter decides the tenure for snapshots in the workload repository.

TOPNSQL affects the number of top SQL to flush for every SQL criteria, such as elapsed time, CPU time, shareable memory, and so on. You can set this value to MAXIMUM and capture the complete set of SQL in the cursor cache at a higher operating cost of space and performance issues. A setting of MAXIMUM will usually collect a lot of data for a normally operating database. You can review the current settings on your database instance from the DBA_HIST_WR_CONTROL view.

The following code will set the snapshot retention period at 64800 minutes (45 days) with an interval at 60 minutes, and flushes the top 200 SQL.

 BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200,  interval => 60,  topnsql => 200, dbid => 2931102824); END; / 

You will review the various operations with baselines now. You can create a baseline using the CREATE_BASELINE option of the DBMS_WORKLOAD_REPOSITORY procedure. A detailed discussion on baselines is given in Chapter 11 under "Capturing Baselines" and "Dropping Baselines." A list of existing snapshots can be found in the DBA_HIST_SNAPSHOT view. Choose the snap_id information and issue the following command to create a baseline.

 BEGIN     DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 41, end_snap_id => 45,          baseline_name => 'qa_test_bsln', dbid => 2931102824); END; / 

The system will automatically assign a unique baseline_id to the new baseline created, which can be reviewed from the DBA_HIST_BASELINE.

 select dbid, baseline_id, baseline_name, start_snap_id, end_snap_id from dba_hist_baseline; DBID           BASELINE_ID    BASELINE_NAME   START_SNAP_ID END_SNAP_ID ------------- -------------- ---------------- ------------- ----------- 2931102824     1              qa_test_bsln      41           45 

Similarly, you can drop the baseline along with or without its snapshots.

 BEGIN   DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'qa_test_bsln',           cascade => TRUE, dbid => 2931102824); END; / 

The value of trUE for the cascade parameter indicates that the pair of snapshots associated with the baseline is also dropped. If you want to retain the snapshots, set the cascade parameter to FALSE. The dbid is for the optional database identifier.