=====
davey23uk
you can't say that and not qualify your statements with examples
====
First of all statspack report is not telling the true picture. Statspack collects statistics from all processes - foreground (user processes) and background (DBWR, LGWR, PMON, etc).
The "TOP 5 WAIT EVENTS" is now changed to "Top 5 Timed Events" in 9i b/c Oracle now realized they are not "Top 5 Wait Events" anymore and the existing statspack code is not excluding background processing time that really does not directly slowdown the foreground processes.
Most of the time waht you see in the Top 5 Timed Events will mislead you to wrong direction of tuning.
2nd Reason:
Statspack does not exclude the processes that are not finished during the interval. Example, a process starts at time T0.
I take a statspack snap for the interval T1 and T2. If I look into the report, I get a different picture and I don't even see the SQL started at time T0.
If you really want to measure and see where the real waits are happening, then you must query from v$session_event and v$session_wait when the application is running.
Can the statspack tell you exactly whether my SGA is oversized or undersized?
NO.
Can the statspack tell you how many buffers are hot buffers due to be cooled, how many buffers to be heated, and how much are free?
NO.
I can write so many questions about the statspack, but the above points are enough.
Only one thing I like in Statspack is: Top N SQL. This is the section one must concentrate initially to tune the application.
Tamil
