Time Model Statistics

When tuning an Oracle database, each component has its own set of statistics. To look at the system as a whole, it is necessary to have a common scale for comparisons. For this reason, most Oracle Database advisories and reports describe statistics in terms of time. 

In addition, the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views provide time model statistics. Using the common time instrumentation helps to identify quantitative effects on the database operations.

The most important of the time model statistics is DB time. This statistics represents the total time spent in database calls and is an indicator of the total instance workload. It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions).

DB time is measured cumulatively from the time of instance startup. Because DB time it is calculated by combining the times from all non-idle user sessions, it is possible that the DB time can exceed the actual time elapsed after the instance started. For example, an instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is approximately 120 minutes.

The objective for tuning an Oracle system could be stated as reducing the time that users spend in performing some action on the database, or simply reducing DB time. Other time model statistics provide quantitative effects (in time) on specific actions, such as logon operations and hard and soft parses.

Adaptive Thresholds

Adaptive thresholds enable you to monitor and detect performance issues while minimizing administrative overhead. Adaptive thresholds can automatically set warning and critical alert thresholds for some system metrics using statistics derived from metric values captured in the moving window baseline. The statistics for these thresholds are recomputed weekly and might result in new thresholds as system performance evolves over time. In addition to recalculating thresholds weekly, adaptive thresholds might compute different thresholds values for different times of the day or week based on periodic workload patterns.

For example, many databases support an online transaction processing (OLTP) workload during the day and batch processing at night. The performance metric for response time per transaction can be useful for detecting degradation in OLTP performance during the day. However, a useful OLTP threshold value is almost certainly too low for batch workloads, where long-running transactions might be common. As a result, threshold values appropriate to OLTP might trigger frequent false performance alerts during batch processing. Adaptive thresholds can detect such a workload pattern and automatically set different threshold values for the daytime and nighttime.

Note:

In Oracle Database 11g Release 2 (11.2), Oracle Database automatically determines the appropriate time groupings for a database. However, before Oracle Database 11g Release 2 (11.2), time groupings were specified manually by the database administrator.

There are two types of adaptive thresholds:

• Percentage of maximum: The threshold value is computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.
• Significance level: The threshold value is set to a statistical percentile that represents how unusual it is to observe values above the threshold value based the data in the moving window baseline. Specify one of the following percentiles:
• High (.95): Only 5 in 100 observations are expected to exceed this value.
• Very High (.99): Only 1 in 100 observations are expected to exceed this value.
• Severe (.999): Only 1 in 1,000 observations are expected to exceed this value.
• Extreme (.9999): Only 1 in 10,000 observations are expected to exceed this value.

Note:

When you specify Severe (.999) or Extreme (.9999), Oracle Database performs an internal calculation to set the threshold value. In some cases, Oracle Database cannot establish the threshold value at these levels using the data in the baseline, and the significance level threshold is not set.

If you are not receiving alerts as expected, and you specified a Severe (.999) or Extreme (.9999) significance level threshold, then you can try setting the significance level threshold to a lower value, such as Very High (.99) or High (.95). Alternatively, you can set a percentage of maximum threshold instead of a significance level threshold. If you change the threshold and find that you are receiving too many alerts, then you can try increasing the number of occurrences to cause an alert.

Percentage of maximum thresholds are most useful when a system is sized for peak workloads, and you want to be alerted when the current workload volume is approaching or exceeding previous high values. Metrics that have an unknown but definite limiting value are good candidates for these settings. For example, the redo generated per second metric is typically a good candidate for a percentage of maximum threshold.

Significance level thresholds are most useful for metrics that should exhibit statistically stable behavior when the system is operating normally, but might vary over a wide range when the system is performing poorly. For example, the response time per transaction metric should be stable for a well-tuned OLTP system, but may fluctuate widely when performance issues arise. Significance level thresholds are meant to generate alerts when conditions produce both unusual metric values and unusual system performance.

Database Statistics

Database statistics provide information on the type of load on the database and the internal and external resources used by the database. 

• Wait Events
• Time Model Statistics
• Active Session History
• System and Session Statistics

Operating System Statistics

• CPU Statistics
• Virtual Memory Statistics
• Disk I/O Statistics
• Network Statistics

Component UNIX Tool

CPU          sar, vmstat, mpstat, iostat

Memory sar, vmstat

Disk         sar, iostat

Network netstat

Oracle Database Performance Method

Performance improvement is an iterative process. Removing the first bottleneck might not lead to performance improvement immediately, because another bottleneck might be revealed that has an even greater performance impact on the system. For this reason, the Oracle performance method is iterative. Accurately diagnosing the performance problem is the first step towards ensuring that the changes you make to the system will result in improved performance.

Performance problems generally result from a lack of throughput, unacceptable user or job response time, or both. The problem might be localized to specific application modules, or it might span the entire system. Before looking at any database or operating system statistics, it is crucial to get feedback from the most important components of the system: the users of the system and the people ultimately paying for the application. Getting feedback from users makes determining the performance goal easier, and improved performance can be measured in terms of real business goals, rather than system statistics.

The Oracle performance method can be applied until performance goals are met or deemed impractical. This process is iterative, and it is likely that some investigations will be made that have little impact on the performance of the system. It takes time and experience to accurately pinpoint critical bottlenecks in a timely manner. The Automatic Database Diagnostic Monitor (ADDM) implements the Oracle performance method and analyzes statistics to provide automatic diagnosis of major performance problems. Using ADDM can significantly shorten the time required to improve the performance of a system.

Limit the Scope

Does the performance issue originate in the operating system (OS), the instance, or the application SQL? This question is not always easy to answer. 

Poorly performing SQL can cause excessive physical reads and writes, appearing to be an I/O issue. Improperly sized memory components (an instance configuration issue) can lead to excessive swapping in the OS. 

Poor disk configuration can appear to be an instance configuration problem, causing a large redo file waits or commit waits, and other problems.

Eliminate possibilities. When the instance appears to have I/O problems, compare the instance file I/O statistics to OS level statistics. The differences can guide you to the actual problem. For example: A higher than normal average wait time on a particular tablespace, could be due to:

Hardware: A file is on a slow drive or an improper RAID configuration.

OS: The OS is busy with other files on the same drive or partition.

Instance: The tablespace was created with different properties than other tablespaces have, other busy database files are on the same disk or partition (the database I/O is not balanced across all the drives), or the objects being accessed are mostly in the same tablespace, file, or disk.

Application: The application is doing excessive I/O due to poor access path choice by the optimizer due to out of date statistics, inefficient indexes, or other reasons.

Determine the scope of the problem to focus your efforts on the solutions that provide the most benefit.