Database statistics provide information about the type of load on the database and the internal and external resources used by the database. To accurately diagnose performance problems with the database using ADDM, statistics must be available.

A cumulative statistic is a count such as the number of block reads. Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics about segments and services. Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes.

By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by AWR are queried from memory. The gathered data can be displayed in both reports and views.

The following initialization parameters are relevant for AWR:

• STATISTICS_LEVEL

  Set this parameter to TYPICAL (default) or ALL to enable statistics gathering by AWR. Setting STATISTICS_LEVEL to BASIC disables many database features, including AWR, and is not recommended.

• CONTROL_MANAGEMENT_PACK_ACCESS

  Set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables many database features, including ADDM, and is strongly discouraged.

The database statistics collected and processed by AWR include:

• Time Model Statistics

• Wait Event Statistics

• Session and System Statistics

• Active Session History Statistics

• High-Load SQL Statistics

Performance tuning using the Oracle performance method is driven by identifying and eliminating bottlenecks in the database, and by developing efficient SQL statements. Database tuning is performed in two phases: proactively and reactively.

In the proactive tuning phase, you must perform tuning tasks as part of your daily database maintenance routine, such as reviewing ADDM analysis and findings, monitoring the real-time performance of the database, and responding to alerts.

In the reactive tuning phase, you must respond to issues reported by users, such as performance problems that may occur for only a short duration of time, or performance degradation to the database over a period of time.

SQL tuning is an iterative process to identify, tune, and improve the efficiency of high-load SQL statements.

Applying the Oracle performance method involves the following:

• Performing pre-tuning preparations, as described in "Preparing the Database for Tuning"

• Tuning the database proactively on a regular basis, as described in "Tuning the Database Proactively"

• Tuning the database reactively when performance problems are reported by the users, as described in "Tuning the Database Reactively"

• Identifying, tuning, and optimizing high-load SQL statements, as described in "Tuning SQL Statements"

To improve database performance, you must apply these principles iteratively.

This section lists and describes common performance problems found in databases. By following the Oracle performance method, you should be able to avoid these problems in an Oracle Database instance. If you experience these problems, then repeat the steps in the Oracle performance method, as described in "Using the Oracle Performance Method", or consult the appropriate section that addresses these problems:

• CPU bottlenecks

  Is the application performing poorly because the system is CPU-bound? Performance problems caused by CPU bottlenecks are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify CPU bottlenecks by using the Performance page in Cloud Control, as described in "Monitoring CPU Utilization".

• Undersized memory structures

  Are the Oracle memory structures such as the System Global Area (SGA), Program Global Area (PGA), and buffer cache adequately sized? Performance problems caused by undersized memory structures are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify memory usage issues by using the Performance page in Cloud Control, as described in "Monitoring Memory Utilization".

• I/O capacity issues

  Is the I/O subsystem performing as expected? Performance problems caused by I/O capacity issues are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify disk I/O issues by using the Performance page in Cloud Control, as described in "Monitoring Disk I/O Utilization".

• Suboptimal use of Oracle Database by the application

  Is the application making suboptimal use of Oracle Database? Problems such as establishing new database connections repeatedly, excessive SQL parsing, and high levels of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. Performance problems caused by suboptimal use of Oracle Database by the application are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also monitor top activity in various dimensions—including SQL, session, services, modules, and actions—by using the Performance page in Cloud Control, as described in "Monitoring User Activity".

• Concurrency issues

  Is the database performing suboptimally due to a high degree of concurrent activities in the database? A high degree of concurrent activities might result in contention for shared resources that can manifest in the form of locks or waits for buffer cache. Performance problems caused by concurrency issues are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify concurrency issues by using Top Sessions in Cloud Control, as described in "Monitoring Top Sessions".

• Database configuration issues

  Is the database configured optimally to provide desired performance levels? For example, is there evidence of incorrect sizing of log files, archiving issues, too many checkpoints, or suboptimal parameter settings? Performance problems caused by database configuration issues are diagnosed by ADDM, as described in Automatic Database Performance Monitoring.

• Short-lived performance problems

  Are users complaining about short-lived or intermittent performance problems? Depending on the interval between snapshots taken by AWR, performance problems that have a short duration may not be captured by ADDM. You can identify short-lived performance problems by using the Active Session History report, as described in Resolving Transient Performance Problems.

• Degradation of database performance over time

  Is there evidence that the database performance has degraded over time? For example, are you or your users noticing that the database is not performing as well as it was 6 months ago? You can generate an AWR Compare Periods report to compare the period when the performance was poor to a period when the performance is stable to identify configuration settings, workload profile, and statistics that are different between these two time periods. This technique helps you identify the cause of the performance degradation, as described in Resolving Performance Degradation Over Time.

• Inefficient or high-load SQL statements

  Are any SQL statements using excessive system resources that impact the system? Performance problems caused by high-load SQL statements are diagnosed by ADDM, as described in Automatic Database Performance Monitoring and "Identification of High-Load SQL Statements Using ADDM Findings". You can also identify high-load SQL statements by using Top SQL in Cloud Control, as described in "Identifying High-Load SQL Statements Using Top SQL". After they have been identified, you can tune the high-load SQL statements using SQL Tuning Advisor, as described in Tuning SQL Statements.

• Object contention

  Are any database objects the source of bottlenecks because they are continuously accessed? Performance problems caused by object contention are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also optimize the data access path to these objects using SQL Access Advisor, as described in Optimizing Data Access Paths.

• Unexpected performance regression after tuning SQL statements

  Is the performance of SQL statements degrading after they have been tuned? Tuning SQL statements may cause changes to their execution plans, resulting in a significant impact on SQL performance. In some cases, the changes may result in the improvement of SQL performance. In other cases, the changes may cause SQL statements to regress, resulting in a degradation of SQL performance.

  Before making changes on a production system, you can analyze the impact of SQL tuning on a test system by using SQL Performance Analyzer. This feature enables you to forecast the impact of system changes on a SQL workload by:

  • Measuring the performance before and after the change

  • Generating a report that describes the change in performance

  • Identifying the SQL statements that regressed or improved

  • Providing tuning recommendations for each SQL statement that regressed

  • Enabling you to implement the tuning recommendations when appropriate

  See Also:

  Oracle Database Testing Guide to know more about how to use SQL Performance Analyzer