| 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 |
|---|
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 2156456.1 There is a full list of the documents in the following:
Solution
To investigate a slow performance problem, begin by deciding what diagnostics should be gathered. To do this, consider the questions in the sections that match the symptoms of your issue and take the appropriate action. The links in the text point to diagnostics reference articles at the end of this article.
To collect the correct diagnostics for your system, select whether you have a Diagnostic pack license.
Without Diagnostic Pack License
In order to produce reports, access AWR views or use the diagnostic information from any part of the Automatic Workload Repository the Diagnostic Pack License is required. This includes the production of AWR reports, ADDM reports and ASH reports even if these are requested by product support or other agencies. It is the user responsibility to comply with license requirements and not violate license agreements. For further details, refer to:
With Diagnostic Pack License
Database is Slow
Is the performance problem constant or does it occur at certain times of the day ?
- CONSTANT
- Collect an ADDM report, review it's findings and implement any recommendations; then re-check the performance.
- Collect AWR and ASH reports for a period of time when the problem occurs (a 1 hour report is usually sufficient). ASH reports can help identify sessions and SQL that are using high CPU.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect the diagnostic information, follow the instructions in the following document:
Or
Collect Database Performance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
- ONLY CERTAIN TIMES
- Collect an ADDM report for a period of time which covers the period when the problem exists (For instance, if you have a problem when something is run between 12 and 3 then make sure the report covers either that time or part of that time). Review it's findings and implement any recommendations; then re-check the performance.
- Collect AWR and ASH reports for the same problem period as with the ADDM report above. ASH reports can help identify sessions and SQL that are using high CPU.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect the diagnostic information, follow the instructions in the following document:
Or
Collect Database Performance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
CPU usage is high for one or more sessions when things run slowly
- Take some errorstacks from the process(es) whose CPU usage is higher than expected. (* If unable to gather errorstacks then gather pstack (or similar) reports)
- Collect AWR and ASH reports for the period when the CPU usage is high. ASH reports can help identify sessions and SQL that are using high CPU.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU
Or
Collect Database Perfomance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
Once Diagnostics have been collected, refer to the following troubleshooting document: Document 352648.1
Does the problem affect one session, several sessions or all sessions ?
- ONE SESSION: Gather 10046 trace for the session.
- SEVERAL SESSIONS: Gather 10046 trace for one or two of the problem sessions
- ALL SESSIONS:
- Collect an ADDM report, review it's findings and implement any recommendations; then re-check the performance.
- Collect AWR and ASH reports for a period of time when the problem occurs (a 1 hour report is usually sufficient). ASH reports can help identify sessions and SQL that are using high CPU.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect the diagnostic information, follow the instructions in the following document:
Or
Collect Database Performance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
Database Hangs
Does a particular Session ''appear'' to hang or do several sessions or all sessions hang? Collect diagnostics according to the specific scenario:
One session appears to be 'hung'
- Gather 10046 trace for the session.
- Get a few errorstacks for the session
- Collect AWR and ASH reports for the period when the session appears to be 'hung'. ASH reports can help identify problem sessions and SQL.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU |
Or
Collect Database Perfomance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
One SQL Statement appears to be 'hung'
- Gather 10046 trace for the session.
- Take some errorstacks from the process(es) whose CPU usage is higher than expected. (* If unable to gather errorstacks then gather pstack (or similar) reports)
- Collect AWR and ASH reports for the period when the CPU usage is high. ASH reports can help identify sessions and SQL that are using high CPU.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU |
Or
Collect Database Performance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
- For this scenario refer to the following article:
More than one session appears to be 'hung'
- Gather 10046 trace for the session.
- Take some errorstacks from the process(es) whose CPU usage is higher than expected. (* If unable to gather errorstacks then gather pstack (or similar) reports)
- Collect AWR and ASH reports for the period when the CPU usage is high. ASH reports can help identify sessions and SQL that are using high CPU.
- Additionally, for comparison, gather an AWR report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU |
Or
Collect Database Performance Diagnostics (With Diagnostic Pack License)
NOTE 1:-
NOTE 2:-[ADDM
Most sessions appears to be 'hung'
- Treat as a Database Hang
Does the database ''appear'' to hang?
If the database appears to hang then collect the following diagnostics for Hang Articles Below.
Document 452358.1 Sometimes the database may not actually be hung but may be 'spinning' on the CPU which has very similar symptoms. You can use the following Spin article to help you to determine if what you are seeing is a true hang or not and help you deal with it:
Document 68738.1 If the database is actually just slow then see the Slow Database Section above.
Without Diagnostic Pack License
Database is Slow
Is the performance problem constant or does it occur at certain times of the day ?
- CONSTANT
- Collect a Statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient).
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
TO collect the diagnostic information, follow the instructions in the following document:
Collect Database Performance Diagnostics (Without Diagnostic Pack License)
- ONLY CERTAIN TIMES
- Collect a Statspack report for a period of time which covers the period when the problem exists (For instance, if you have a problem when something is run between 12 and 3 then make sure the report covers either that time or part of that time).
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect the diagnostic information, follow the instructions in the following document:
Collect Database Performance Diagnostics (Without Diagnostic Pack License)
CPU usage is high for one or more sessions when things run slowly
- Take some errorstacks from the process(es) whose CPU usage is higher than expected. (* If unable to gather errorstacks then gather pstack (or similar) reports)
- Collect a Statspack report for the period when the CPU usage is high.
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU
Collect Database Perfomance Diagnostics (Without Diagnostic Pack License)
Once Diagnostics have been collected, refer to the following troubleshooting document:
Does the problem affect one session, several sessions or all sessions ?
- ONE SESSION: Gather 10046 trace for the session.
- SEVERAL SESSIONS: Gather 10046 trace for one or two of the problem sessions
- ALL SESSIONS:
- Collect a Statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient).
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect the diagnostic information, follow the instructions in the following document:
Collect Database Performance Diagnostics (Without Diagnostic Pack License)
Database Hangs
Does a particular Session ''appear'' to hang or do several sessions or all sessions hang? Collect diagnostics according to the specific scenario:
One session appears to be 'hung'
- Gather 10046 trace for the session.
- Get a few errorstacks for the session
- Collect a Statspack report for the period when the session appears to be 'hung'.
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU |
Collect Database Performance Diagnostics (Without Diagnostic Pack License)
Once Diagnostics have been collected, refer to the following troubleshooting document:
One SQL Statement appears to be 'hung'
- Gather 10046 trace for the session.
- Get a few errorstacks for the session
- Gather a Statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient).
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU |
Collect Database Performance Diagnostics (Without Diagnostic Pack License)
- For this scenario refer to the following article:
More than one session appears to be 'hung'
- Gather 10046 trace for one or two of the problem sessions
- Get a few errorstacks for one or two of the problem sessions
- Gather a Statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient).
- Additionally, for comparison, gather a Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.
To collect diagnostic information follow the instructions in the following documents:
Collect errorstacks from the process using high CPU |
Collect Database Perfomance Diagnostics (Without Diagnostic Pack License)
Most sessions appears to be 'hung'
- Treat as a Database Hang
Database appears to 'hang'?
If the database appears to hang then collect the following diagnostics for Hang Articles Below.
Document 452358.1 Sometimes the database may not actually be hung but may be 'spinning' on the CPU which has very similar symptoms. You can use the following Spin article to help you to determine if what you are seeing is a true hang or not and help you deal with it:
Document 68738.1 If the database is actually just slow then see the Slow Database Section above.
Referenced Diagnostic Articles
–
ADDM:
Automatic Database Diagnostic Monitor (ADDM) reports should be reviewed prior to and alongside Automatic Workload Repository (AWR) reports to assist in diagnosis since they provide specific recommendations which can help point at potential problems. See:
–
ASH:
Active Session History (ASH) reports provides sampled session activity in the instance. See:
http://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF02606
–
AWR:
Assuming you have the appropriate licenses for AWR, please gather Automatic Workload Repository (AWR) reports for the system. Without the specific licenses please gather statspack snapshots. See the following articles :
–
Statspack:
–
10046:
–
Hang:
–
Spin:
–
Errorstack:
–
Pstack:
–
SQLT:
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
References
NOTE:363422.1 – How to Run Oraclestats in ODP 11.5.10
NOTE:94224.1 – FAQ- Statspack Complete Reference
NOTE:376442.1 – * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
NOTE:748642.1 – How to Generate an AWR Report and Create Baselines
NOTE:390374.1 – Oracle Performance Diagnostic Guide (OPDG)
NOTE:1361401.1 – Where to Find Information About Performance Related Features
NOTE:1386530.1 – Using truss and pstack to Debug Hang Type Problems
NOTE:452358.1 – How to Collect Diagnostics for Database Hanging Issues
NOTE:68738.1 – No Response from the Server, Does it Hang or Spin?
NOTE:70609.1 – How To Display Information About Processes on SUN Solaris
NOTE:1364257.1 – How to Collect Errorstacks for use in Diagnosing Performance Issues.
NOTE:2156456.1 – SRDC – How to Collect Standard Information for a Database Performance Problem for 11g or Greater on Unix/Linux (with Diagnostic Pack License)