My Oracle Support Banner

* Troubleshooting Performance Issues (Doc ID 1377446.1)

Last updated on JULY 19, 2021

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A 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:

<Document 1542678.2> Troubleshooting Assistant: SQL Performance Issues

This articles supersedes :

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

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:

<Document 1482811.1> Best Practices: Proactively Avoiding Database and Query Performance Issues
<Document 1477599.1> Best Practices Around Data Collection For Performance Issues

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> List of Available Database Performance Related SRDC Documents

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
 Best Practices
 Pro-Active Problem Avoidance and Diagnostic Collection
 Performance Service Request Diagnostic Collection (SRDC) documents
Troubleshooting Steps
 General Performance
 AWR Generation
 AWR Interpretation
 Buffer Busy  Contention
 Cache Buffers Chains Latch Contention
 Configuration Parameters
 Connection Timeouts: Errors ORA-3135/ORA-3136
 Deadlocks: Error ORA-00060
 Disk I/O or ASM
 Enqueue/Locks
 High Numbers of Child Cursors/High Cursor Version Counts
 Hung database
 Latches
 Library Cache
 Log File Sync
 Mutex Contention
 Operating System (OS)
 Paging/Memory
 Slow Database
 SQL Issue Diagnosis Using DBMS_SQLDIAG
 SQL Tuning
 Statistics
 Bind Peeking / ACS
 Dynamic Sampling
 SQL Tuning with VPD
 SQLT / SQLHC
 Plan Stability SPM/SQL Profiles/SQL Patches)
 SYSAUX Tablespace Issues
 10046/TKPROF
 Upgrade
 Virtual Circuit Waits
 row cache lock / row cache mutex
 New Features
 Autonomous Health Framework(AHF) - TFA/ORAchk/EXAchk
 Webcasts
 Community Discussions
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.