| 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: raw traces - EXEC: c=10,000 e=40
Boris,
Your three cases are a simpler manifestation of the problem described in Figure 7-8 of "Optimizing Oracle Performance" (pp163-165).
Each of the three db calls you show happened to be either in the 'kernel running' or 'user running' OS process state when a scheduler interrupt occurred. Each time this occurred, your OS allocated a full 1/100-second unit of CPU capacity consumption to the process. 1/100th of a second equals 10,000 microseconds, so this is why you see c=10000. Yes, the true elapsed time of the call is a known upper bound on the true CPU consumption, but without microstate accounting (Sun Solaris), you can't know the true CPU consumption except to know that it was somewhere between 0 and the value of e.
It's easy to become sad at this point. For example, "Oracle CPU statistics are accurate only to the nearest 10,000 microseconds" sounds really bad. Well, it's not true; it's much worse than that. Each c statistic can be off by *far* more than 10,000 microseconds! I'll show you why in a minute.
But don't worry. There are lots of other db calls in your trace file that look like "c=0,e=29862", which might have been more accurate had they said "c=10000" or maybe "c=20000". You can't correct for these, because you can't know how much correction is required. It's tempting to correct "c=10000,e=40" because you know the "real c" couldn't have exceeded 40, BUT DON'T. If you correct for neither problem, then statistics are on your side. Over the course of a many-line trace file, the total quantization error tends toward zero.
In other words, if you sum your c values and sum your e values for a whole file, the total error is much smaller than it would appear by observing lots of scary "c=10000,e=40" cases. If you try to correct the overestimation problem, you just make things worse (because it's impossible to determine when you have a corresponding underestimation problem).
To answer your question directly, I think the range of c-e for a single db call is -e < c-e < 10,000us. Here's my reasoning:
c=10000,e=x (where x is much smaller than 10,000) Stuff like this happens all the time (as you've observed). It happens when a scheduler interrupt occurs anytime during the x-us execution duration. Of course, the probability of this occurring is approximately x/10,000, so the smaller your value of x, the less likely you'll find this occurring. But even c=10000,e=0 is possible.
c=20000,e=x (where x is much smaller than 10,000) I don't think this can happen as long as there's a single scheduler interrupt for all the CPUs of an SMP system. For two scheduler interrupts to take place during the elapsed duration of a db call, the db call duration would have to be at least e=10000. In general (induction step here), I think it's impossible to register a c=N value unless e has a value of at least N-10000. So I think the largest positive value c-e could take on is 10,000us.
c=0,e=x (where x is arbitrarily large)
This one can happen for all sorts of values of x, and I mean LARGE ones.
Imagine a db call that runs for e=100,000,000 (commas for clarity; this
is 100 seconds). It's entirely possible (though highly improbable) that
the process happens never actually to be using a CPU during a scheduler
interrupt. Therefore, I believe that c-e can be arbitrarily large in the
negative direction. It is improbable in direct proportion to the size of
e=x that c will be zero, but it's possible.
Therefore, I think the bounds of the error c-e are probably -e < c-e < 10,000us.
But, again, don't worry. The likelihood of an overestimated c approximates the likelihood of an underestimated c. Over the course of a largish trace file, it is highly unlikely that the quantization error on the sum of your c statistics will propagate very far in either the overestimate or the underestimate direction. And practical experience bears this out. Our Hotsos Profiler uses this information to full advantage.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta
-----Original Message-----
Boris Dali
Sent: Tuesday, December 02, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L
Reading Cary's book I understand that c and e are measured via different system calls (haven't truss'ed [well tusc'ed] them yet - I am on HP-UX 11.11), but would anybody know what the reasonable upper limit of c-e might be?
I am looking at the trace file where c is more than two orders of magnitude greater than e, which make me wonder if I a have some anomaly on my system
Some examples:
EXEC
#98:c=10000,e=433,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1777312113968
EXEC
#110:c=10000,e=390,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1777312181650
PARSE
#103:c=10000,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1777314524922
Oracle 9.2.0.4.0 on HP-UX 11.11
Thanks,
Boris Dali.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: boris_dali_at_yahoo.ca Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: cary.millsap_at_hotsos.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Dec 02 2003 - 22:14:26 CST
 |
 |