| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Method R and CPU Time
Hi Ian!
One comment on your interesting discussion.
>> 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?
.
May be you are looking for answer to your question in the wrong place? You
mentioned, that performance of the single activity at the off load our is
OK and it can vary during the day.
My guess better source of information for this mentioned propose is OS
statistics like: CPU, IO subsystems queues length and others statistics,
that characterize your HW load during the load ours. Depending of queue
length you can make guess how useful will be upgrade.
>> Can one really use method R to evaluate how a hardware upgrade will
affect performance?
I would say NO :). If we trying to fight with overall system performance
then we need to evaluate overall HW utilization and try to guess which
component to upgrade and who mach.
If you are going to improve some acti vity responce time, then I don't
know better methode then SQL Trace facility usage.
May be a am wrong. Subject to discution.
Regards,
Jurijs
9268222
"MacGregor, Ian A." <ian_at_slac.stanford.edu>
Sent by: oracle-l-bounce_at_freelists.org
02.07.2004 17:42
Please respond to oracle-l
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
cc:
Subject: RE: Method R and CPU Time
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 magnified.
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
ian_at_SLAC.Stanfod.edu
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Friday, July 02, 2004 12:45 AM
To: oracle-l_at_freelists.org
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.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html 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
WAIT_SECS ELAPSED_SECS CPU_SECS
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
8.75
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
iam_at_slac.stanford.edu
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Jul 05 2004 - 09:55:50 CDT
 |
 |