RE: Result caching
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jan 2015 09:09:33 -0800
A trace file will have the clues. Perhaps tkprof with aggregate=no. Perhaps the
execution plan changed. Perhaps the data blocks were cached in the SGA, OS
cache, or storage cache.
Iggy
Date: Tue, 20 Jan 2015 16:56:02 +0000
From: dmarc-noreply@xxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: Result caching
Hi, I'm trying to put together a benchmarking wrapper that we can place scripts
inside. The script is simple in design, it does the following: 1) Loop over
the query that's being benchmarked x number of times (configurable).2) Throw
out the first 2 runs and average the rest of the runs.3) Return the average
duration in milliseconds of the runs from (2). So far this has worked great,
however I'm having some weird results. A query I'm benchmarking as an
unoptimized case takes ~70 seconds to run on average as reported from the
benchmarking wrapper (with 5 runs) which is all fine and dandy, but if I try to
run the wrapper again (with 5 more runs), it completes in 0.6 seconds on
average. I had two thoughts: 1) Maybe the execution plan is being cached. But
this doesn't explain why it consistently ran so slow for 5 runs.2) Maybe the
results are being cached. I reproduced the issue in our DR server and verified
that the results cache didn't change in size, so this is not the issue.3) Maybe
it has something to do with the fact that the query being benchmarked is always
being benchmarked without bind variables (hardcoded bind values of a slow case
to make the wrapper more simple). We are on single-instance Oracle 11gR2 and
I'm running this all through PL/SQL developer. I can include the benchmarking
wrapper script if necessary. This is more academic at this point, when we run
the unoptimized query through the application, it consistently runs at around
70 seconds a run regardless of the number of times run. Thank you for your
time, Stephen Van Linge
Other related posts:
