Troubleshooting Performance Issues

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: Document 1482811.1

Document 1477599.1### 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. Document 1938786.1## TROUBLESHOOTING STEPS

General Performance

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

Document 390374.1### AWR Generation To troubleshoot the failure to collect AWR snapshots or reports, See: Document 1301503.1

Document 1363422.1### AWR Interpretation How to troubleshoot performance issues using AWR snapshots or reports, See: Document 1359094.1### Buffer Busy Contention To troubleshoot issues where Buffers have become Busy due to concurrent access, see: Document 155971.1### Cache Buffers Chains Latch Contention To troubleshoot issues caused by contention due to multiple sessions waiting to read the same block, see: Document 1342917.1### Configuration Parameters Database parameters are designed so that the default is appropriate for the majority of cases. The following article discusses Optimizer Specific Parameters: Document 1507909.1### 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: Document 730066.1### Deadlocks: Error ORA-00060 For issues related to Deadlocks,refer to : Document 60.1

Document 1509919.1### Disk I/O For issues related to Disk Input/Ouput (I/O),refer to : Document 223117.1

Document 1275596.1### Enqueue/Locks For locking issues refer to : Document 1392319.1

Document 15476.1

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: Document 296377.1### Hung database To troubleshoot cases where the database appears to have hung, refer to : Document 1378583.1

Document 452358.1

Latches

To troubleshoot issues where there is contention on 'cache buffers chains' latches see: Document 1342917.1### Library Cache For issues related to contention in the Library Cache Document 62143.1### Log File Sync To troubleshoot issues where 'log file sync' is a primary cause of session wait activity, see: Document 1376916.1

Mutex Contention

For issues related to Mutex contention, refer to : Document 1377998.1

Document 1349387.1

Document 1357946.1

Document 1356828.1### Operating System (OS) To troubleshoot Operating System (OS) related issues see: Document 352648.1

Document 148176.1### Paging/Memory If heavy paging activity is seen and the database appears to be running more slowly than normal, refer to : Document 1195174.1### Slow Database To troubleshoot occasions where the database appears to be running more slowly than normal, refer to : Document 1362329.1### 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: Document 1509192.1### SQL Tuning For issues with individual queries, refer to the following troubleshooting documents: Document 742112.1

Document 1386036.1### SYSAUX Tablespace Issues Document 1399365.1### TKProf Runtime Errors Document 1383275.1### Upgrade To troubleshoot issues encountered after upgrading a database see: Document 1528847.1

Document 160089.1

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

Document 785351.1

Document 1645862.1

Document 1392633.1

Document 1320966.1### Virtual Circuit Waits Document 1415999.1### '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: Document 278316.1

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 here to open in main browser window