Customizing DBA Performance Queries and their Variations

To effectively diagnose performance problems, statistics must be available. Oracle generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle also tracks cumulative statistics on segments and services. When analyzing a performance problem in any of these scopes, you typically look at the change in statistics (delta value) over the period of time you are interested in. Specifically, you look at the difference between

1. the cumulative value of a statistic at the start of the period and
2. the cumulative value at the end.

Cumulative values for statistics are generally available through dynamic performance views, such as the V$SESSTAT and V$SYSSTAT views. Note that the cumulative values in dynamic views are reset when the database instance is shutdown. The Automatic Workload Repository (AWR) automatically persists the cumulative and delta values for most of the statistics at all levels except the session level. This process is repeated on a regular time period and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period.

Another type of statistic collected by Oracle is called a metric. A metric is defined as the rate of change in some cumulative statistic. That rate can be measured against a variety of units, including time, transactions, or database calls. For example, the number database calls per second is a metric. Metric values are exposed in some V$ views, where the values are the average over a fairly small time interval, typically 60 seconds. A history of recent metric values is available through V$ views, and some of the data is also persisted by AWR snapshots.
A third type of statistical data collected by Oracle is sampled data. This sampling is performed by the active session history (ASH) sampler. ASH samples the current state of all active sessions. This data is collected into memory and can be accessed by a V$ view. It is also written out to persistent store by the AWR snapshot processing.

There are many public-domain scripts that can be used to get a snapshot of Oracle performance. As a review, examine the use of V$ views below. The DBA will use a script like this one to look inside a running Oracle database. Your specific needs may not require a full snapshot of all possible values. For example, if you have little freelist contention, you may choose not to run the buffer busy wait statistics. As we have discussed, there are many SQL methods for extracting performance information from Oracle. Each may return identical results, but with different levels of performance.

Both queries use the V$SYSSTAT view and look at physical reads as a percentage of consistent_gets plus db_block_gets, but do this in different ways. It is common to see identical results come from different coding techniques. Note that this is the accumulated buffer hit ratio since database start time, so it does not indicate the current hit ratio. In this example, the second query runs faster than the first query, because it joins the V$SYSSTAT table against itself.
The next lesson looks at a technique to easily apply what we've learned about query performance, and create an automated alert monitor.

Before you move on to, click the Exercise link below to write an SGA snapshot routine using the V$ views.
Customizing DBA Performance - Exercise