How To Investigate Slow or Hanging Database Performance Issues [ID 1362329.1]-CSDN博客

In this Document

Applies to:

Oracle Server - Standard Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Oracle Server - Enterprise Edition - Version 6.0.0.0 to 11.2.0.3 [Release 6.0 to 11.2]
Oracle Server - Personal Edition - Version 7.1.4.0 to 11.2.0.3 [Release 7.1.4 to 11.2]
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.

Fix

To investigate a slow performance problem, begin by deciding what diagnostics should be gathered. To do this, consider the following questions and take the appropriate action. The links in the text point to diagnostics referenced in articles at the end of this article:-

Database is Slow: Is the performance problem constant or does it occur at certain times of the day ?

CONSTANT
- Gather an AWR or Statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient).
- If you have an historic report which covers the same time of day and period when the performance was OK then take that too.
  
  Document 748642.1 How to Generate an AWR Report and Create Baselines
ONLY CERTAIN TIMES
- Gather an AWR or 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 an AWR or 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.
  
  Document 748642.1 How to Generate an AWR Report and Create Baselines

NOTE 1:- As much as possible statspack reports should be minimum 10 minutes, maximum 30 minutes. Longer periods can distort the information and reports should be re-gathered using a shorter time period.
With AWR a 1hr report is OK, but for most performance issues a short 10-30 minute snapshot should be sufficient.

NOTE 2:- It is often prudent to use a matched ADDM report initially to give a pointer to the main issues. Reading the corresponding ADDM report as a first step to tuning can save a lot of time because it immediately points at the main user as compared to trying to understand what an AWR report is presenting. See: ADDM

NOTE 3:- If SQL performance is suspected as the cause of the slowness then collect an ASH report for the same period. If a specific SQL is suspected of slowness then run an ASH report just for that SQLID and also look at using SQLTXplain to diagnose issues with that statement. See: ASH and SQLT

Database is Slow: 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 - Gather AWR or Statspack reports

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning.
Document 748642.1 How to Generate an AWR Report and Create Baselines

Database Hangs: Does a particular Session ''appear'' to hang or do several sessions or all sessions hang?

Please collect the following 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
Gather an AWR (or Statspack) report for a period of time when the problem occurs (a 1 hour report is usually sufficient).

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning.
Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues
Document 748642.1 How to Generate an AWR Report and Create Baselines

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 an AWR (or Statspack) report for a period of time when the problem occurs (a 1 hour report is usually sufficient).

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning.
Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues
Document 748642.1 How to Generate an AWR Report and Create Baselines

Most sessions appears to be 'hung'

Treat as a Database Hang

Database Hangs: Does the database ''appear'' to hang?

If the database appears to hang then collect the following diagnostics for Hang Articles Below.

Document 452358.1 Database Hangs: What to collect for support.

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 No Response from the Server, Does it Hang or Spin?

If he database is actually just slow then see the Slow Database Section above.

Database is Slow: Is the CPU usage high for one or more sessions when things run slowly ?

YES - Take some errorstacks from the suspect CPU process.
(* If unable to gather errorstacks then gather pstack reports)

Document 352648.1 How to Diagnose high CPU usage problems
Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues
Document 70609.1 How To Display Information About Processes on SUN Solaris

Comprehensive Overview of Various Performance Scenarios

Note:390374.1 Oracle Performance Diagnostic Guide (OPDG)

Referenced Diagnostic Articles

ADDM:

Automatic Database Diagnostic Monitor (ADDM) reports can be reviewed along with AWR to assist in diagnosis since they provide specific recommendations which can help point at potential problems. See:

Document 250655.1 How to use the Automatic Database Diagnostic Monitor
ASH:

Active Session History (ASH) reports provides sampled session activity in the instance. See:

Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)
Part Number E16638-06
Chapter 5 Automatic Performance Statistics
5.3.8 Generating Active Session History Reports
http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#PFGRF02606
AWR:

Assuming you have the appropriate licenses for AWR, please gather Automatic Workload Repository (AWR) reports for the system. See the following articles :

Document 1363422.1 AWR Reports - Information Center
Document 748642.1 How to Generate an AWR Report and Create Baselines

Without the specific licenses please gather statspack snapshots.
Statspack:

Document 94224.1 FAQ- Statspack Complete Reference
10046:

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning.
Hang:

Document 452358.1 Database Hangs: What to collect for support.
Spin:

Document 68738.1 No Response from the Server, Does it Hang or Spin?
Errorstack:

Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues
Pstack:

Document 70609.1 How To Display Information About Processes on SUN Solaris
SQLT:

Document 215187.1 SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly

References

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:748642.1 - How to Generate an AWR Report and Create Baselines
NOTE:1364257.1 - How to Collect Errorstacks for use in Diagnosing Performance Issues.
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:390374.1 - Oracle Performance Diagnostic Guide (OPDG)
NOTE:1361401.1 - Where to Find Information About Performance Related Features

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18914315/viewspace-741878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18914315/viewspace-741878/