In this Document

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

PURPOSE

This article provides a central point for Performance Troubleshooting advice.

You can access categorised Troubleshooting information via the following:

This articles supersedes :

Best Practices

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 Service Request Diagnostic Collection (SRDC) documents

Service Request Data Collection (SRDC) documents have been specially designed to provide the reader with the necessary instructions to provide a step by step guide to collecting information for a various common Database Performance Issues.

TROUBLESHOOTING STEPS

General Performance

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

AWR Generation

To troubleshoot the failure to collect AWR snapshots or reports, See:

AWR Interpretation

How to troubleshoot performance issues using AWR snapshots or reports, See:

Buffer Busy  Contention

To troubleshoot issues where Buffers have become Busy due to concurrent access, see:

Cache Buffers Chains Latch Contention

To troubleshoot issues caused by contention due to multiple sessions waiting to read the same block, see:

Configuration Parameters

Database parameters are designed so that the default is appropriate for the majority of cases. The following article discusses Optimizer Specific Parameters:

Connection Timeouts: Errors ORA-3135/ORA-3136

To troubleshoot issues caused by Connection Timeouts when errors such as ORA-3135 or ORA-3136 are seen, see:

Deadlocks: Error ORA-00060

For issues related to Deadlocks,refer to :

Disk I/O

For issues related to Disk Input/Ouput (I/O),refer to :

Enqueue/Locks

For locking issues refer to :

 

High Numbers of Child Cursors/High Cursor Version Counts

When a SQL statement is executed, Oracle will try to match that statement up with an existing identical statement that has already been executed and attempt to reuse the stored parsed representation of it (which is stored within a cursor structure in the library cache). If the existing information in the cursor for that statement cannot be used, a new version of that statement will be created and used instead. If lots of versions of a particular statement are created, Oracle has to search through the versions to determine which (if any) can be shared. In extreme circumstances, this can cause mutex contention and other issue that degrade the database performance. In order to troubleshoot these issues and determine the cause, see:

Hung database

To troubleshoot cases where the database appears to have hung, refer to :

Latches

To troubleshoot issues where there is contention on 'cache buffers chains' latches see:

Library Cache

For issues related to contention in the Library Cache

Log File Sync

To troubleshoot issues where 'log file sync' is a primary cause of session wait activity, see:

Mutex Contention

For issues related to Mutex contention, refer to :

Operating System (OS)

To troubleshoot Operating System (OS) related issues see:

Paging/Memory

If heavy paging activity is seen and the database appears to be running more slowly than normal, refer to :

Slow Database

To troubleshoot occasions where the database appears to be running more slowly than normal, refer to :

SQL Issue Diagnosis Using DBMS_SQLDIAG

An increasing number of issues can be detected, diagnosed and avoided using the DBMS_SQLDIAG package. For details see:

SQL Tuning

For issues with individual queries, refer to the following troubleshooting documents:

SYSAUX Tablespace Issues

TKProf Runtime Errors

Upgrade

To troubleshoot issues encountered after upgrading a database see:

For advice on how to avoid such issues before upgrading, see:

Virtual Circuit Waits

'WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!'

To troubleshoot issues when the database detects that a waiter has waited for a resource for longer than a particular threshold and he message "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" appears in the alert log, See:

Community Discussions

Still have questions? Use the communities window below to search for similar discussions or start a new discussion on this subject. (Window is the live community not a screenshot)

Click  to open in main browser window