RE: How to calculate the CPU Time for a procedure

Juan,

>You can use wait events too,
>Oracle wait interface a practical guide to performance
>diagnostics tuning
>from richmond shee has a good example

Wait events do NOT provide any measure of CPU usage - there are no 'waits' for CPU. These values come entirely from V$SESSTAT (name from V$STATNAME). I do not remember anything in the OWI book that mentions this as coming from OWI (the example uses on P10 uses v$sesstat). STATSPACK in 9i and above does count in 'CPU Time' in the Top 5 events, but that is not from the OWI!

On the other hand, the original poster should look at mining these values from V$SESSTAT for the 'parse time cpu', 'CPU Used by this session', 'recursive cpu usage' and 'session logical reads' that can give a good indication of CPU usage. Even without any details provided, I would definitely state that this is due to an untuned bunch of SQL that is performing way too much LIO.

Hth,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Fear connects you to the Negative, but Faith connects you to the Positive! I Jn 4:18

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- http://www.freelists.org/webpage/oracle-l