CPU time and Statspack
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: CPU time and Statspack

  1. #1
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630

    CPU time and Statspack

    Hello,
    I am looking at a statspack output and what I see is
    ....
    Elapsed: 291.62 (mins)
    ....
    Top 5 Timed Events

    ~~~~~~~~~~~~~~~~~~ % Total

    Event Waits Time (s) Ela Time

    -------------------------------------------- ------------ ----------- --------

    CPU time 439,177 86.13
    .....
    The machine has 8 CPUs .
    a time of 439177 sec is around 7319 min.
    On the other hand, the total maximum CPU processing time is 291.62*8 CPUs= 2332.96 min.
    1)Can someone clarify that?
    Appears that CPU Time reffers not to the CPU processing time, but to the time session waits for CPU.
    2) If so, with these figures can we conclude that the machine is highly overloaded in terms of CPU?
    3) Any idea what could cause the CPU overloading


    Thanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    interval time?

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Elapsed: 291.62 (mins)

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    too high GETS can cause CPU overloading. btw, for your first and second point

    1)Can someone clarify that?
    Appears that CPU Time reffers not to the CPU processing time, but to the time session waits for CPU.

    -- It means total CPU spent for parse/recursive call/execute.. In simple terms 'CPU used by this session' (Service Time).

    2) If so, with these figures can we conclude that the machine is highly overloaded in terms of CPU?

    it may not necessarily be so, it may be due to cumulative CPU Time for N sessions.

    But since your "% Total Ela Time" for CPUT time is 86.13, you may have to look at SQL's ordered by GETS ( i would particualrly see GETS PER EXEC )


    Abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Thanks for the replay,
    but still..
    my point is that for 291 min ( which is the time period we measure) 8 processors may spend no more than 2332.96 minutes in processing ( i.e 291 min * 8 processors)
    How can we then claim that even the comulative CPU is 439177 sec (or 7319 min.)
    It is much jigher than physically possible CPU time

    Thanks
    Boris

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    may I ask where did you get this figuer?
    ---------
    the total maximum CPU processing time is 291.62*8 CPUs= 2332.96 min.
    ------
    for 291 min ( which is the time period we measure) 8 processors may spend no more than 2332.96 minutes in processing ( i.e 291 min * 8 processors)
    How can we then claim that even the comulative CPU is 439177 sec (or 7319 min.)
    -----------
    Can this be seen in a statspack output or something?
    ---------------

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Not sure, that's why I'm asking.
    The Statspack, in the section Top 5 wait events says
    CPU Time 439177
    I understand that as time spent by all the sessions during the measuring period, in CPU processing.
    If I am wrong, please correct me.
    Also, my understanding is that for 291 min, 8 processors can spend no more than 291 mins in processing. If I am wrong again, please let me know and clarify that

    Thanks

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    as what Krishna said. Service Time. and follow the explaination.
    --------------
    If you are not satisfied try to find docs that explain the interpretation of statspack output.
    ---------------

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    ok i think u didnt get "cumulative CPU Time for N sessions", well i will give you a simple example

    Consider 2 sessions seeking CPU simultaneouly and have spent N seconds each.. Actual time elapsed is N seconds, but CPU Time computed by ORACLE and as shown in AWR/Statspack report is 2N.

    Abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    OK thanks, that's my understanding as well,
    The point is that since the CPU time reported in the statspack is lot more than the physical time between the snapshots, multiplied by the number of processors, that would mean that the time sessions waits actually for CPU but not using CPU is also counted in the statspack as CPU time.
    That on the other hand will mean that CPU is a bottleneck since sessions spends lots of time waiting for CPU right?

    P.S.
    I see lots of db buffer gets and I identified the SQL causing the number of gets, but I am interested generally does the fact that CPU Time in the statspack is lot more that the intterval multiplied by processots, means that CPU is a bottleneck

    Thanks for the discussion
    Boris

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