RE: Result caching
Date: Tue, 20 Jan 2015 09:09:33 -0800
Message-ID: <BLU179-W91B443D67B9BBA936E8EF9EB4B0_at_phx.gbl>
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_at_freelists.org
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 20 2015 - 18:09:33 CET