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. > > > > > > >