Re: what is statspack 'CPU Time' event

It is worth mentioning here that this 'CPU Time' showed in statspack does NOT include time spent in the OS runq, waiting to get on the CPU. Given that, if the system is really badly CPU bound, you might not see this 'CPU Time' in the top 5 timed events, but would see every other Oracle wait event really exaggerated.

On the other hand, even when you see 'CPU Time' in the top 5 timed events, unless you compare the seconds spent on the CPU with the corresponding wall clock time times the number of CPUs on the system, you cannot really tell if you system is well tuned or mostly idle or is 'wasting' a lot of unnecessary CPU from bad SQL et al.

Regards,
-V

on 2/11/2004 10:56 PM srivenu said the following:

>Hi,
>Before Oracle 9.2 this section was called "Top 5 Wait Events". It was
>renamed in Oracle 9.2 to "Top 5 Timed Events" to include the "CPU
>Time" based on the 'CPU used by this session'. This information will
>allow you to determine SQL tuning problems. Notice that in Oracle 9.2
>references are made "Elapsed Time" rather than to "Wait Time". Also
>the "CPU Time" is included as part of the Top events section.
>For further info see $ORACLE_HOME/rdbms/admin/spdoc.txt.
>The cpu time isn't necessarily a bad thing. Prior to 9.2 Oracle didn't
>track it on the statspack report. It is simply the cpu necessary to
>complete the tasks. However, really high cpu time could indicate an
>I/O bottleneck.
>Whats the duration for which you had collected this report ?
>You have set some undocumented parameters, hope you had sufficient
>reason to do so.
>I think Your UNDO_RETENTION of 1 is too small.
>Your LOG_BUFFER is too big.
>When building the database, you could have used a block size of 8KB.
>Is this a production database ? If it is place your control files on
>seperate controllers.
>regards
>Srivenu
>
>
Received on Thu Feb 12 2004 - 13:11:23 CST