Re: Result caching
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: dmarc-noreply@xxxxxxxxxxxxx
- Date: Fri, 23 Jan 2015 10:42:52 -0500
It depends upon version. In 11.2.0.3 Cardinality feedback was simply not
ready.
On Jan 23, 2015 10:39 AM, "Job Miller" <dmarc-noreply@xxxxxxxxxxxxx> wrote:
> > *thus added code to disable cardinality feedback at the session level
> on the top of the script and my results became consistent.*
>
> Is this query meaningful, or just a sample query used to test out your
> test harness?
>
> If cardinality feedback is indeed working as advertised and fixing a
> problem with the limitations of stats collection or a data specific issue,
> do you want to lock your users into bad performance for consistency of
> testing sake or do you want to fix the problem to get good production
> results the first time?
>
> Job
>
> ------------------------------
> *From:* stephen van linge <dmarc-noreply@xxxxxxxxxxxxx>
> *To:* "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>; "
> rajendra.pande@xxxxxxx" <rajendra.pande@xxxxxxx>; "oracle-l@xxxxxxxxxxxxx"
> <oracle-l@xxxxxxxxxxxxx>
> *Sent:* Thursday, January 22, 2015 2:17 PM
> *Subject:* Re: Result caching
>
> I worked offline with Iggy and was able to deduce the cause of the
> issue, the following is a summary of our efforts and the result (in bold
> for those of you that don't want to read the details).
>
> I ran a trace and tkprof on both the first and second runs of the script,
> and we noticed that there was significant physical I/O occurring on the
> first run and not on the second run. Upon closer examination, each run had
> a different plan hash, thus Iggy directed me to view v$sql_shared_cursor (
> http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3059.htm#REFRN30254
> ).
>
> We noticed that the "USE_FEEDBACK_STATS" flag was the only difference
> between the plans, and so upon further examination Iggy found that the
> cause of the divergent plans could be cardinality feedback in 11g r2 (
> https://blogs.oracle.com/optimizer/entry/cardinality_feedback).
>
> Since this query is likely to be run more "here and there" instead of in
> rapid succession, I judged that the more production-accurate result for us
> would be to benchmark the query without cardinality feedback enabled, and
> *thus
> added code to disable cardinality feedback at the session level on the top
> of the script and my results became consistent.*
>
> Stephen
>
>
>
> ------------------------------
> *From:* stephen van linge <dmarc-noreply@xxxxxxxxxxxxx>
> *To:* "rajendra.pande@xxxxxxx" <rajendra.pande@xxxxxxx>; "
> dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>; "
> oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> *Sent:* Tuesday, January 20, 2015 9:17 AM
> *Subject:* Re: Result caching
>
> @Iggy
> Ok I'll look at the trace files, this'll be a good excuse to get to know
> trace files and tkprof better.
>
> @Rajendra
> The 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
>
> 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:
