Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Method R and CPU Time

Re: Method R and CPU Time

From: Jonathan Lewis <>
Date: Fri, 2 Jul 2004 08:45:23 +0100
Message-ID: <007e01c46008$8c250de0$7102a8c0@Primary>

Just hand-waving and burbling out loud on this one, as I don't know how the 14.29 elapsed seconds is derived.

I note that you've got what seems to be a lot of CPU time recorded for what may be a relatively small amount of data handling.

Is it possible that the machine is running at high levels of CPU usage, so that you get a lot of:

    Me running on CPU

        Me still runnable but pre-emptively descheduled -
        so Oracle won't be recording a wait event or CPU
        time but real time is still passing
    Me back at top of queue and running again

In this scenario, CPU and Wait time do not increase, but elapsed time increases. Of course, the tasks you are running, and the task other people are running concurrently would have to be very CPU intensive for the lost time to be such a large fraction of the wait time.


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st

I'm having a hard time determining how much time is actually spent on CPU,

Consider the following

Runtime           TRACE_ID        EVENT                               WAITS
25-JUN-2004 12:05 nlco_ora_7279   db file sequential read                96
.27        14.29      13.07
25-JUN-2004 12:05                 db file scattered read                378
7.14        14.29      13.07
25-JUN-2004 12:05                 SQL*Net message from client           475
1.34        14.29      13.07

                       ***************************           -----    -----
                                  sum                                   949

The CPU_SECS + WAIT_SECS should equal elapsed secs at least approximately. I know there is some double counting between the file i/o waits and the CPU's but 13.07 + 8.75 = 21.82 and 21.82/14.29 * 100 = anout 153%. That's a lot more than the incidental double-counting error Cary speaks of in his book.

This particular machine has 4 CPU's and when looking at CPU usage at the system level one needs to take that into account. But what about for individual statements that are not parallelized; i.e., running against one and only one CPU, does one need to divide the CPU results by four on a four CPU machine.

Ian MacGregor
Stanford Linear Accelerator Center

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Fri Jul 02 2004 - 02:42:10 CDT

Original text of this message