Troubleshooting Performance Issues [ID 1377446.1]

最新推荐文章于 2016-11-30 21:45:22 发布

cuilian2013

最新推荐文章于 2016-11-30 21:45:22 发布

阅读量169

点赞数

Troubleshooting Performance Issues [ID 1377446.1]

In this Document

Purpose

Troubleshooting Steps

General Performance

AWR Generation

AWR Interpretation

Buffer Busy Contention

Cache Buffers Chains Latch Contention

Connection Timeouts: Errors ORA-3135/ORA-3136

Disk I/O

Enqueue/Locks

High Numbers of Child Cursors/High Cursor Version Counts

Operating System (OS)

Slow Database

SQL Tuning

SYSAUX$ Issues

TKProf Runtime Errors

Upgrade

Virtual Circuit Waits

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

References

Applies to:

Oracle Server - Personal Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Server - Standard Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Purpose

This article provides a central point for Performance Troubleshooting advice.
This articles supercedes :

Document 106285.1 TROUBLESHOOTING GUIDE: Common Performance Tuning Issues (ARCHIVED)

Troubleshooting Steps

General Performance

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

Document 390374.1 Oracle Performance Diagnostic Guide (OPDG)
Document 1362329.1 How To Investigate Slow Database Performance Issues

AWR Generation

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

Document 1301503.1 Troubleshooting: AWR Snapshot Collection issues
Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point

AWR Interpretation

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

Document 1359094.1 FAQ: How to Use AWR reports to Diagnose Database Performance Issues

Buffer Busy Contention

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

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

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 Troubleshooting 'latch: cache buffers chains' Wait Contention

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 Troubleshooting ORA-3135/ORA-3136 Connection Timeouts Errors - Database Diagnostics

Disk I/O

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

Document 223117.1 Troubleshooting I/O-related waits
Document 1275596.1 How to Tell if the IO of the Database is Slow

Enqueue/Locks

For locking issues refer to :

Document 1392319.1 Master Note: Locks, Enqueues and Deadlocks

Document 15476.1 FAQ about Detecting and Resolving Locking Conflicts

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 Troubleshooting: High Version Count Issues

Hung database

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

Document 1378583.1 Troubleshooting Database Hang Issues
Document 452358.1 How to Collect Diagnostics for Database Hanging Issues

Latches

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

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

Library Cache

For issues related to contention in the Library Cache

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

Log File Sync

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

Document 1376916.1 Troubleshooting: "Log File Sync" Waits

Mutex Contention

For issues related to Mutex contention, refer to :

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.

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

Operating System (OS)

To troubleshoot Operating System (OS) related issues see:

Document 352648.1 How to Diagnose High CPU Usage Problems to the Module Level
Document 148176.1 Diagnosing hardware configuration induced performance problems

Slow Database

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

Document 1362329.1 How To Investigate Slow Database Performance Issues

SQL Tuning

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

Document 742112.1 * Query Performance Degradation - Recommended Actions
Document 745216.1 * Query Performance Degradation - Upgrade Related - Recommended Actions

Document 179668.1 * TROUBLESHOOTING: Tuning Slow Running Queries

Document 33089.1 * TROUBLESHOOTING: Possible Causes of Poor SQL Performance
Document 372431.1 * TROUBLESHOOTING: Tuning a New Query
Document 122812.1 * TROUBLESHOOTING: Tuning Queries That Cannot be Modified
Document 163563.1 * TROUBLESHOOTING: Advanced Query Tuning

Document 1386036.1 How To Diagnose Issues Where Plans Are Different From Different Clients

SYSAUX$ Issues

Document 1399365.1 Troubleshooting Issues with SYSAUX$

TKProf Runtime Errors

Document 1383275.1 Troubleshooting TKProf Runtime Errors

Upgrade

To troubleshoot issues related to upgrading a database see:

Document 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

Document 1320966.1 Things to Consider before upgrading to 11.2.0.2 to avoid performance or wrong results
Document 1392633.1 Things to consider before upgrading to 11.2.0.3 to avoid performance or wrong results

Virtual Circuit Waits

Document 1415999.1 Troubleshooting: 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:

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