Applies to:

Oracle Database - Standard Edition - Version 7.0.16.0 and later

Oracle Database - Personal Edition - Version 7.1.4.0 and later

Oracle Database - Enterprise Edition - Version 6.0.0.0 and later

Enterprise Manager for Oracle Database - Version 8.1.7.4 and later

Information in this document applies to any platform.

Purpose

This Document provides an overview reference to content that pertains to the resolution of Performance issues affecting the whole database.

For the Database Performance Frequently Asked Questions FAQ,information regarding Performance Related Features or  issues involving individual SQL statements, see:

Questions and Answers

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. For information on suggested preparations to avoid issues and in case diagnostics are required, see:

Performance Information Centres

Performance Information Centres provide access to various aspects of performance issues:

Troubleshooting Guides

There are a number of troubleshooting guides that can help resolve various issues:

Common Problem Topics

• 
  

  Slow Database Performance

  If the database is performing slowly see the following:

  Document 1362329.1 How To Investigate Slow Database Performance Issues
• 
  

  Concurrency Issues

  If you have problems with the concurrency of transactions, initially treat this as a "Slow Database Performance" issue and refer to the following:

  Document 1362329.1 How To Investigate Slow Database Performance Issues
• 
  

  Database Hangs/Spins

  If the database encounters what looks like a hang or a spin conditions refer to the following:

  Document 452358.1 How to Collect Diagnostics for Database Hanging Issues
  Document 68738.1 No Response from the Server, Does it Hang or Spin?Document 392037.1 Database Hangs. Sessions wait for 'resmgr:cpu quantum'
• 
  

  Session Hangs/Spins

  If an individual session appears to hang or spin conditions refer to the relevant sections in the following articles:

  Document 1362329.1 How To Investigate Slow Database Performance Issues
  Document 352648.1 How to Diagnose high CPU usage problems
• 
  

  Locking Issues

  If sessions encounter locking conflicts refer to the following:

  Document 1392319.1 Master Note: Locks, Enqueues and Deadlocks
  Document 15476.1 FAQ about Detecting and Resolving Locking ConflictsDocument 62354.1 TX Transaction locks - Example wait scenarios
  Document 33453.1 REFERENTIAL INTEGRITY AND LOCKING
  • 
    

    Deadlock

    ORA-60 is an application error which usually occurs because a consistent locking strategy has not been followed throughout an application.

    Please refer to the following articles for more information:

    Document 1509919.1 Master Note for Database Error ORA-00060
    Document 18251.1 OERR: ORA 60 "deadlock detected while waiting for resource"
    Document 62365.1 What to do with "ORA-60 Deadlock Detected" Errors

    Please ensure that you are encountering none of the conditions in these articles.

• 
  

  Pre-Upgrade Planning

  For upgrades to 11g, there is a useful webcast entitled "11G Upgrade Best Practices" :
  This recorded seminar provides an overview of tips and best practices for upgrading your database to 11G. It starts with general Challenges and Support Best Practices, but moves on to performance specifics such as AWR and STATSPACK, SQL Plan Management, and Real Application Testing.

  This webcast can be found here:

  11G Upgrade Best Practices 

  For advice on planning your upgrade see:

  Document 785351.1 Oracle 11gR2 Upgrade Companion
  Document 601807.1 Oracle 11gR1 Upgrade Companion
  Document 466181.1 10g Upgrade CompanionDocument 465787.1 Managing CBO Stats during an upgrade to 10g or 11g

  For more general upgrade related information see:

  Document 1392633.1 Things to Consider before upgrading to 11.2.0.3 to avoid performance or wrong results
  Document 1320966.1 Things to Consider before upgrading to 11.2.0.2 to avoid performance or wrong resultsDocument 1464274.1 Master Note for Real Application Testing Option
  Document 762540.1 Consolidated Reference List Of Notes For Migration / Upgrade Service Requests
• 
  

  Post-Upgrade Performance Issue Resolution

  To troubleshoot performance issues encountered after upgrading a database see:

  Document 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
• 
  

  Debugging Waits for Various Events

  There are numerous articles to help you debug issues with wait event contention. The most common of these being:

  Library Cache/Mutex Contention/Cursor Type Events:

  Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
  Document 1377998.1 Troubleshooting: Waits for Mutex Type Events
  Document 1349387.1 Troubleshooting 'cursor: pin S wait on X' waits
  Document 1357946.1 Troubleshooting 'library cache: mutex X' waits.
  

  Other Types:

  Document 1376916.1 Troubleshooting: "Log File Sync" Waits

  Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention
  Document 34405.1 WAITEVENT: "buffer busy waits" Reference Note

  Document 223117.1 Troubleshooting I/O-related waits

  Document 1415999.1 Troubleshooting: Virtual Circuit Waits

• 
  

  Common Causes of Performance issues

  • 
    

    Cursor Sharing/High Version Counts for Cursors

    The failure to share cursors effectively can have a highly detrimental affect on a database. For example, having hundreds of versions of the same cursor is likely to be wasting space, is unlikely to promote good performance and, in extreme cases, can cause contention issues.
    For issues linked to Cursor related waits see:

    Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events

    Also, see the following articles for more information and help with troubleshooting issues:

    Document 296377.1Troubleshooting: High Version Count Issues
    Document 438755.1 High SQL Version Counts - Script to determine reason(s)Document 62143.1 Understanding and Tuning the Shared Pool
  • 
    

    High CPU usage

    In cases where CPU usage of individual sessions or the whole database is high, refer to the following:

    Document 352648.1 How to Diagnose High CPU Usage Problems to the Module Level
    Document 273646.1 How to diagnose the high CPU utilization of ORACLE.EXE in Windows environment

    Note that High CPU usage can be caused by many factors including contention for cursors (see above), inefficient SQL and may be associated with excessive buffer reads etc.

  • 
    

    Issues With waits for 'log file sync'

    If your system is waiting for the redo log files to synchronise with the disk, the following articles can help:

    Document 1376916.1 Troubleshooting: "Log File Sync" Waits

    Document 34592.1 WAITEVENT: "log file sync" Reference Note

    Document 857576.1 How to Minimise Waits for 'Log File Sync'?
    Document 1278149.1 High Log File Sync Wait Events, LGWR Posting Slow Write Times, Low IO Waits

    Document 1064487.1 Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)

• 
  

  WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

  This Issue occurs when the database detects that a waiter has waited for a resource for longer than a particular threshold. The message "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" appears in the alert log and trace and systemstates are dumped.

  Typically this is caused by two (or more) incompatible operations being run simltaneously. See:

  Document 278316.1 Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"
• 
  

  Buffer Busy/Cache Buffers Chains Latch waits

  Buffer Busy waits occur when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".

  Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.

  Typical solutions are to look for SQL that accesses the blocks in question and determine if the repeated reads are necessary.

  Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention

  Document 34405.1 WAITEVENT: "buffer busy waits" Reference Note
  Document 155971.1 Resolving Intense and "Random" Buffer Busy Wait Performance Problems:
  Document 163424.1 How To Identify a Hot Block Within The Database Buffer Cache

• 
  

  SYSAUX Issues

  SYSAUX is a mandatory tablespace in 10g which stores all auxiliary database metadata related to various product options and features. Of particular interest to Performance is the storage of Automatic Workload Repository (AWR) data:

  Document 552880.1 General Guidelines for SYSAUX Space Issues

  Document 1055547.1 SYSAUX Grows Because Optimizer Stats History is Not Purged
  Document 387914.1 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged

  Document 554831.1 How to Control the Set of Top SQLs Captured During AWR Snapshot Generation
  Document 1292724.1 SYSAUX Tablespace Grows Rapidly

Performance Diagnostics References

• 
  

  General Diagnostics Overview

  Document 438452.1 Performance Tools Quick Reference Guide
  Document 781198.1 Required Diagnostics for Database Performance Issues
• 
  

  AWR/Statspack

  Document 1363422.1 AWR Reports - Start Point
  Document 94224.1 FAQ- Statspack Complete Reference
• 
  

  10046 Trace

  Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning
• 
  

  Systemstates/Hanganalyze

  Document 121779.1 - Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.
  Document 175006.1 Steps to generate HANGANALYZE trace files

• 
  

  Errorstacks for Performance Issues

  Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues
• 
  

  PStack

  Document 70609.1  How To Display Information About Processes on SUN Solaris
• 
  

  PL/SQL Profiler

  Document 243755.1 Implementing and Using the PL/SQL Profiler
• 
  

  OS Watcher

  OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues.

  Document 301137.1 OS Watcher User Guide
• 
  

  LTOM

  The Lite Onboard Monitor (LTOM) is a java program designed as a proactive, real-time diagnostic platform and provides real-time automatic problem detection and data collection.

  Document 352363.1 LTOM - The On-Board Monitor User Guide
  Document 461050.1 The LTOM Graph (LTOMg) User Guide
  Document 461228.1 The LTOM Graph FAQDocument 461052.1 LTOM System Profiler - Sample Output
  Document 977975.1 Diagnose LTOM Connection Problems

• 
  

  Trace/Result Interpretation

  The following articles can help with the interpretation of various diagnostics:

  Document 390374.1 - Oracle Performance Diagnostic Guide (OPDG)
  Document 39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE outputhttp://www.oracle.com/technetwork/database/focus-areas/performance/statspack-opm4-134117.pdf

Performance (and other) Webcasts

A series of Performance (and General Datbase topic) related webcasts, including topics such as SQLHC, SQLTXPLAIN and OSWBB, can be found here:

Performance and Scalability White Papers and Documentation

Performance and Scalability White Papers:

For White papers concerning Real Application Testing (RAT) and SQL Performance Analyzer (SPA) see:

Links to the main Tuning and Performance documentation:

Interacting With Performance Support

Community: Database Tuning

A community has been set up for My Oracle Support (MOS) users moderated by Oracle. The goal of this community is to exchange database related Tuning knowledge and concepts. The community can be found via the following article:

References

NOTE:1320966.1 - Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results

NOTE:461228.1 - The LTOM Graph FAQ

NOTE:175006.1 - Steps to generate HANGANALYZE trace files (9i and below)

NOTE:18251.1 - OERR: ORA 60 "deadlock detected while waiting for resource"

NOTE:452358.1 - How to Collect Diagnostics for Database Hanging Issues

NOTE:199083.1 - * Master Note: SQL Query Performance Overview

NOTE:223117.1 - Troubleshooting I/O-related waits

NOTE:68738.1 - No Response from the Server, Does it Hang or Spin?

NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged

NOTE:390374.1 - Oracle Performance Diagnostic Guide (OPDG)

NOTE:392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum'

NOTE:166650.1 - Working Effectively With Support Best Practices

NOTE:461050.1 - The LTOM Graph (LTOMg) User Guide

NOTE:1357946.1 - Troubleshooting 'library cache: mutex X' waits.

NOTE:352648.1 - How to Diagnose High CPU Usage Problems to the Module Level

NOTE:554831.1 - How to Control the Set of Top SQLs Captured During AWR Snapshot Generation

NOTE:398838.1 - FAQ: SQL Query Performance - Frequently Asked Questions

NOTE:465787.1 - How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g

NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

NOTE:39817.1 - Interpreting Raw SQL_TRACE output

NOTE:62365.1 - Troubleshooting "ORA-00060 Deadlock Detected" Errors

NOTE:785351.1 - Oracle 11gR2 Upgrade Companion

NOTE:857576.1 - How to Minimise Waits for 'Log File Sync'

NOTE:94224.1 - FAQ- Statspack Complete Reference

NOTE:70609.1 - How To Display Information About Processes on SUN Solaris

NOTE:977975.1 - Diagnose LTOM Connection Problems

NOTE:1055547.1 - SYSAUX Grows Because Optimizer Stats History is Not Purged

NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note

NOTE:352363.1 - LTOM - The On-Board Monitor User Guide

NOTE:601807.1 - Oracle 11gR1 Upgrade Companion

NOTE:210014.1 - How to Log a Good Performance Service Request

NOTE:1377446.1 - * Troubleshooting Performance Issues

NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data

NOTE:1528847.1 - Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade

NOTE:1392633.1 - Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results

NOTE:466181.1 - Oracle 10g Upgrade Companion

NOTE:1361401.1 - Where to Find Information About Performance Related Features

NOTE:1362329.1 - How To Investigate Slow or Hanging Database Performance Issues

NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Start Point

NOTE:1364257.1 - How to Collect Errorstacks for use in Diagnosing Performance Issues.

NOTE:163424.1 - How To Identify a Hot Block Within The Database Buffer Cache.

NOTE:1360119.1 - * FAQ: Database Performance Frequently Asked Questions

NOTE:552880.1 - General Guidelines for SYSAUX Space Issues

NOTE:1278149.1 - Intermittent Long 'log file sync' Waits, LGWR Posting Long Write Times, I/O Portion of Wait Minimal

NOTE:1195363.1 - Database Performance and SQL Tuning Documentation on OTN

NOTE:121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle

NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

NOTE:1415999.1 - Troubleshooting: Virtual Circuit Waits

NOTE:1064487.1 - Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)

NOTE:438452.1 - Performance Tools Quick Reference Guide

NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s)

NOTE:461052.1 - LTOM System Profiler - Sample Output

NOTE:278316.1 - Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "

NOTE:15476.1 - FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors

NOTE:1392319.1 - Master Note: Locks, Enqueues and Deadlocks (ORA-00060)

NOTE:155971.1 - Resolving Intense and "Random" Buffer Busy Wait Performance Problems

NOTE:1376916.1 - Troubleshooting: "log file sync" Waits

NOTE:1292724.1 - Suggestions if your SYSAUX Tablespace grows rapidly or too large

NOTE:62354.1 - Waits for 'Enq: Tx - Row Lock Contention' - Wait Scenario Examples

NOTE:1377998.1 - Troubleshooting: Waits for Mutex Type Events

NOTE:1383594.1 - Collaborate with MOS Database Tuning Community Members

NOTE:762540.1 - Consolidated Reference List Of Notes For Migration / Upgrade Service Requests

NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events

NOTE:1509919.1 - Master Note for Database Error ORA-00060 "deadlock detected while waiting for resource"

NOTE:781198.1 - Diagnostics for Database Performance Issues

NOTE:273646.1 - How to Diagnose High CPU Utilization of ORACLE.EXE in Windows Environment

NOTE:1342917.1 - Troubleshooting 'latch: cache buffers chains' Wait Contention

NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.

NOTE:296377.1 - Troubleshooting: High Version Count Issues

NOTE:301137.1 - OSWatcher Black Box (Includes: [Video])

NOTE:33453.1 - Locking and Referential Integrity

NOTE:34405.1 - WAITEVENT: "buffer busy waits" Reference Note