DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: reading/understaing STATSPACK

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    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,

  2. #2
    Join Date
    Jan 2005
    Posts
    221
    got it

    thank you

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width