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 - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----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. ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
