My Oracle Support Banner

How to Investigate Slow or Hanging Database Performance Issues (Doc ID 1362329.1)

Last updated on SEPTEMBER 25, 2018

Applies to:

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

Goal

This article outlines the basic steps to take when faced with a Slow Database Performance Problem. Database performance issues have a wide variety of causes but typically the primary symptoms are that the database is slow or in extreme cases 'hung'.

Collecting Performance Diagnostics with 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 2065318.1> SRDC - How to Collect Standard Information for a Database Performance Problem (with Diagnostic Pack License)
<Document 2065320.1> SRDC - How to Collect Standard Information for a Database Performance Problem (without Diagnostic Pack License)
<Document 2156456.1> SRDC - How to Collect Standard Information for a Database Performance Problem for 11g or Greater on Unix/Linux (with Diagnostic Pack License)

There is a full list of the documents in the following:

<Document 1938786.1> List of Available Database Performance Related SRDC Documents

 

Solution

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
Goal
 Collecting Performance Diagnostics with Performance Service Request Diagnostic Collection (SRDC) documents
Solution
 With Diagnostic Pack License
 Database is Slow
 Is the performance problem constant or does it occur at certain times of the day ?
 CPU usage is high for one or more sessions when things run slowly
 Does the problem affect one session, several sessions or all sessions ?
 Database Hangs
 One session appears to be 'hung'
 One SQL Statement appears to be 'hung'
 More than one session appears to be 'hung'
 Most sessions appears to be 'hung'
 Does the database ''appear'' to hang?
 Without Diagnostic Pack License
 Database is Slow
 Is the performance problem constant or does it occur at certain times of the day ?
 CPU usage is high for one or more sessions when things run slowly
 Does the problem affect one session, several sessions or all sessions ?
 Database Hangs
 One session appears to be 'hung'
 One SQL Statement appears to be 'hung'
 More than one session appears to be 'hung'
 Most sessions appears to be 'hung'
 Database appears to 'hang'?
 Referenced Diagnostic Articles
 ADDM:
 ASH:
 AWR:
 Statspack:
 10046:
 Hang:
 Spin:
 Errorstack:
 Pstack:
 SQLT:
 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.