Re: unaccounted for time in a tkprof output
Date: Tue, 11 Aug 2009 10:09:30 -0400
Message-ID: <f30139790908110709v68bd649fvfbb3b7d157ab041d_at_mail.gmail.com>
#3 is a good guess... but i ran this on a larger dataset over the weekend when production is not that busy. It ran for 18 hours and I had to kill it. The waits were pretty much all on the straight inserts without any wait events associated with it. I ran it with a smaller data set since I wanted it to complete so I can get the plans. The plans are the same in dev and test.
The difference in time is almost all associate with inserts, but I do not have any coresponding wait events.
The inserts are a row by row insert that runs in a loop.
Outer cursor
query
query
query
insert
end loop;
Yes i know this is not good code. I am not allowed to change the code in the short term. There is a long change process this would have to go through.
2009/8/10 Ric Van Dyke <ric.van.dyke_at_hotsos.com>
> Generally the unaccounted for time breaks out into these five buckets:
>
>
>
> 1 Measurement intrusion effect (> 0, is negligibly small)
>
> 2 Quantization Error (¡ú 0, is negligibly small)
>
> 3 Time spent Not executing (unconstrained)
>
> 4 Un-instrumented time (¡ú 0, if your kernel is patched)
>
> 5 CPU double-counting during OS calls (¡Ü 0, is negligibly small)
>
>
>
> Most likely it¡¯s #3 and it would make some since that you are on the CPU
> but not executing as much on Production as you do in Test since I will make
> the grand assumption that there are a lot more folks doing things on
> production then there are on test. Oracle doesn¡¯t know how long you spend on
> the CPU but in the ready to run queue and not actually on the CPU doing
> work. The more folks on the system, the more time you will likely spend in
> the queue while on the CPU.
>
>
>
> If you want to know more about this, get the book ¡°Optimizing Oracle
> Performance¡± and/or come to a Hotsos training class (Oracle Performance
> Management
>
> Using Response Time Profiling) where we cover this in great detail.
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> -----------------------
>
>
>
> *Hotsos Symposium *
>
> *March 7 ¨C 11, 2010 *
>
> *Be there.*
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Dba DBA
> *Sent:* Monday, August 10, 2009 4:23 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* unaccounted for time in a tkprof output
>
>
>
> I have this on otn. I didn't want to copy it to email since I can't
> properly format the tkprof outputs.
>
>
>
> How do I have extra elapsed time with out wait events to account for it? I
> am trying to figure out why inserts take longer in production and I don't
> have a wait to go on.
>
>
>
> http://forums.oracle.com/forums/thread.jspa?messageID=3683942#3683942
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 11 2009 - 09:09:30 CDT