Common Performance Problems Found in Databases

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