-
reading/understaing STATSPACK
Hi all,
I am still trying to understand of how to read STATSPACK, below is what I found from Metalink and I am not sure how to calculate these number as Oracle did:
Code:
-----------------------------------------------------
MISINTERPRETING THE IMPACT OF WAIT EVENTS: AN EXAMPLE
-----------------------------------------------------
Here is a real life example of why it is important to look at both 'Wait Time'
and 'Service Time' when investigating database performance.
The following is the "Top 5 Wait Events" section of a Statspack report
generated from two snapshots 46 minutes apart:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
-------------------------------------------------------------
Based on this listing we may be tempted to immediately start looking at the
causes between the 'direct path read' and 'db file scattered read' waits and
to try to tune them. This approach would not take into account 'Service Time'.
Here is the statistic that measures 'Service Time' from the same report:
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 358,806 130.5 12,372.6
Let's do some simple math from these figures:
'Wait Time' = 10,827 x 100% / 52,01% = 20,817 cs
'Service Time' = 358,806 cs
'Response Time' = 358,806 + 20,817 = 379,623 cs
If we now calculate percentages for all the 'Response Time' components:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%
the question is how does Oracle get these number:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%
thanks,
-
-
This is version 8 I believe - if you're using 9+ look for a description that matches your version. In 9+ it's easier because the CPU time is put on the same footing as the other waits.
As far as the end-user is concerned, waiting for the CPU to finish its stuff is still a wait. Unfortunately, in v8, seen from the inside, CPU processing was considered a "service" rather than a "wait". This article is saying: "don't forget to add the CPU wait time to the others". (Often it is small and can be overlooked.)
"Service time" = CPU time = 358,806 cs
All other waits . . . . it doesn't say! so calculate:
52.01% of all other waits = 10,827 cs
therefore
100% of all other waits = 20,817 cs
Grand Total of waits seen by end-user = 358,806 + 20,817 = 379,623 cs
So CPU "wait" is 358,806 of 379,623 = 94.52% of the end-user wait time.
Does that help?
ooops - too late
Last edited by DaPi; 04-05-2005 at 11:17 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|