Re: Result caching
- From: "stephen van linge" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "swvanlinge@xxxxxxxxx" for DMARC)
- To: "rajendra.pande@xxxxxxx" <rajendra.pande@xxxxxxx>, "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jan 2015 17:17:16 +0000 (UTC)
@IggyOk I'll look at the trace files, this'll be a good excuse to get to know
trace files and tkprof better. @RajendraThe first run of the benchmarking
script (runs the query itself 5 times) is just the first time I hit "execute"
on it. The re-run is the second time the benchmarking script is run with the
same query (another 5 times). It certainly could be the instrumentation, but
as the query in question is a SELECT and is being run exactly as-is over and
over again, I believe the possibility of an error is minute. Stephen
From: "rajendra.pande@xxxxxxx" <rajendra.pande@xxxxxxx>
To: dmarc-noreply@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, January 20, 2015 9:10 AM
Subject: RE: Result caching
#yiv0010460891 #yiv0010460891 -- _filtered #yiv0010460891
{font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv0010460891
{font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv0010460891
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv0010460891
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv0010460891
#yiv0010460891 p.yiv0010460891MsoNormal, #yiv0010460891
li.yiv0010460891MsoNormal, #yiv0010460891 div.yiv0010460891MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv0010460891 a:link,
#yiv0010460891 span.yiv0010460891MsoHyperlink
{color:blue;text-decoration:underline;}#yiv0010460891 a:visited, #yiv0010460891
span.yiv0010460891MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv0010460891
span.yiv0010460891EmailStyle17 {color:#002060;}#yiv0010460891
.yiv0010460891MsoChpDefault {font-size:10.0pt;} _filtered #yiv0010460891
{margin:1.0in 1.0in 1.0in 1.0in;}#yiv0010460891 div.yiv0010460891WordSection1
{}#yiv0010460891 How do you tell the difference between a re-run (average .6
seconds) and a second run (average 70 seconds)My thought is there is some issue
with the instrumentation. Another question is how do you validate the results.
Is it possible that there is some error in the re-run that is not caught
Regards
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of stephen van linge
Sent: Tuesday, January 20, 2015 11:56 AM
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
Please visit our website at
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
for important disclosures and information about our e-mail
policies. For your protection, please do not transmit orders
or instructions by e-mail or include account numbers, Social
Security numbers, credit card numbers, passwords, or other
personal information.
Other related posts:
