RE: Method R and CPU Time
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Jul 2004 11:22:33 -0500
Tkprof double-counts /horribly/. It begins with how it handles the =
so-called
"idle events."
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 =
Boston
- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of MacGregor, Ian A.
Sent: Saturday, July 03, 2004 2:31 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Method R and CPU Time
Thanks, these figures are from tkprof on a 9.2.0.4 database. I used =
grep
to extract the totals for all non-recursive SQL statements and then =
turned
that output into an external table. I did the same thing with the =
waits.
I had earlier checked the figures tkprof provides vs. the raw trace file =
for
the same statement run once and found they agreed. I have not summed =
the
waits from the raw trace file upon which tkprof was run, but the CPU and
elapsed times do agree between the raw trace file and the tkprof output.
As CPU_SECS + Wait_SECS > 1.5 * Elapsed_SECS, it appears the =
double-counting
is not always incidental, but can be significant when there are db file =
I/O
waits involved. If this is true, can one really use method R to =
evaluate
how a hardware upgrade will affect performance.
Remember, the figures are from tkprof which Cary states can get things
wrong, and the wait times totals have not been confirmed as accurate =
from
the raw trace data; hence, the question is premature. Has anyone had
difficulty separating time spent waiting for file i/o vs. CPU time?
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
=20
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]=20
Sent: Saturday, July 03, 2004 3:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Method R and CPU Time
Notes in-line.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
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
----- Original Message -----
From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, July 02, 2004 3:42 PM
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.
[jpl] Not necessarily, though you may know it to be true in your case.
[jpl] In the general case, 10 runs would be more likely to flatten out
anomalies
[jpl] minimise descrepancies.
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.
[jpl] Without knowing what tools you are using to produce
[jpl] the numbers, and where they are coming from, and what
[jpl] actually is happening in the code, it is not possible to give
[jpl] a guaranteed answer to that question. But if you are just
[jpl] reading v$mystat and v$session_event for the session, and
[jpl] parts of the query are parallelised, you need to know that
[jpl] PX slave stats are summed back to the QC, but PX slave
[jpl] waits are not. So any attempt to add ela to c to get
[jpl] elapsed time would be misguided.
[jpl] On the other hand, you didn't mention any PX Deq wait
[jpl] time, and I assumed from the reference to ela and c that
[jpl] you are processing a 10046 trace file - so the simple answer
[jpl] to your original question is no - you don't need to divide
[jpl] the c figure by the number of processors.
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@xxxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
