RE: Method R and CPU Time

  • From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 2 Jul 2004 07:42:03 -0700

The figures represent totals  from running the same statement 10 different 
times with different bind variables, that is on average the elapsed time is 
1.429 seconds per statement execution.  Also  because the report is  based on 
10 runs of a statement any discrepancies in the figuring of e, ela, or c are 

The statements ran starting at 12:05 PM on Jun 25.  Statspack from noon to 
12:15 reported  630 seconds of CPU time.  Again there are four CPU's, the 
machine was not overloaded.

My original question had to do as to why  "sum(ela)" + "c" was over 1.5 times 
as high as "e", and whether for a statement running on a single CPU one needed 
to divide the reported CPU time by the number of processors on the machine just 
as one would when looking at total CPU time  across the entire machine.   If I 
do that, then ela + c < e, but the error is much much less. 

There are things outside of disk waits and CPU times which need to be 
researched.  Such as why submit 10 different requests for 10 different signals. 
 The  requests themselves union a daily partioned table with indexes and a 
non-indexed live table holding a single calendar days worth of data partitioned 
every 10 minutes.  The non-indexed table is the one reporting the scattered 
read waits.  The table is not indexed as it needs to collect signal data in 
real time and is employing direct mode inserts via OCI.  Exactly how the 
partition sizes were decided, I don't know.  Partition pruning is successful.

No one is complaining about the above response time, but it can vary during the 
day due to machine load, and how much of the data is in cache, at times 
reaching unacceptable levels.  Faster hardware is being considered and I'm 
trying to figure how much if any that would  help by figuring how much time is 
actually spent on CPU for these queries vs. waits for physical I/O.  

Ian MacGregor
Stanford Linear Accelerator Center

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] 
Sent: Friday, July 02, 2004 12:45 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Method R and CPU Time

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

----- Original Message -----
From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, July 01, 2004 9:42 PM
Subject: Method R and CPU Time

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

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:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: