Resolving The Problem

Statspack is a diagnosis tool for instance-wide performance problems :
======================================================
It can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.
It also supports application tuning activities by providing data which identifies high-load SQL statements.

Statspack is available with Oracle Release 8.1.6 onwards incorporating many new features, such as:
* Storing performance data in permanent Oracle tables which can be used for analysis at any time in the future
* Capturing high-resource SQL statements
* An easy to use report, with many useful ratios pre-computed

As of 3/29/2005, The PDF from Oracle explaining this process can be found here:

http://www.oracle.com/technology/deploy/performance/pdf/statspack_tunin…
If the above link no longer exists, please contact Applications Support, mention this article number, and ask for a copy of the .pdf.  "statspack_tuning_otn_new.pdf" is available in their SupportAttachments\REF_IMP_Docs folder.

Below are a few quick notes to help you get started. Please refer to the .pdf if you need more detailed information.
It is suggested that you have your DBA set up Statspack to take a snapshot at regular intervals, and periodically generate a report.
Comparing snapshots taken during similar workloads can help pinpoint bottlenecks.

STATSPACK report - Checklist :
- Unusual changes in system load
- Instance efficiency ratios
- Top-5 wait events
- High rates of activity
-Hard parse greater than 100 per second
-Soft parse greater than 300 per second
- Bottlenecks in CPU Usage, IO Systerm or Network
- Long Wait Events, SQL queries
- Low percentages in the Instance Efficiency section
- Tablespaces and Files with slow I/O
- High Buffer wait time
- Latch Sleep Breakdowns and Latch Misses
- Identify Query execution

*NOTE: Be sure to read the "When statistics and wait events can be misleading" section on page 20.