Re: Where does GC grab historical SQL plans from?

  • From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 17 May 2011 09:14:36 -0500 (CDT)

Hey Wolfgang,

> Do you need the histograms? Why not just not collect them.
>
> On 2011-05-13, at 9:22 AM, Rich Jesse wrote:
>
>> Because we have histograms, CBO sees this and
>> correctly chooses another index for that query.  And because we're on
>> 10.1,
>> if this should be the first hard parse of that SQL, the "incorrect" index
>> sticks for subsequent runs.

In hindsight, I think that would have been the correct choice from the start
for 10.1 and its first-parse-only bind peeking.  However, I started this job
two weeks before go-live (which thankfully turned into six weeks!) and the
analysis for that could not have been a priority at the time.

Now, 4+ years later, I'm planning the upgrade to 11.2.0.2 and the promise of
Adaptive Cursor Sharing.  Although the particular column I have an issue
with does not have skewed data (it's a sequenced key), my
analysis/thought/hope is that in 11.2, the errant query would only affect
that execution -- that subsequent runs with "good" bind values would be
parsed differently.

That's my main problem.  Not the execution of that first query with the
"bad" bind value, but every execution afterwards with "good" values.

Removing histograms for that one column in 10.1 has the potential to change
plans for many SQLs.  At this point, I think it would be better for me to
spend that time moving us to 11.2.  And re-reading your CBO paper...and
Alberto Dell'era's Join Over Histograms, too...  :)

Any thoughts on this mess?

Thanks!
Rich

--
//www.freelists.org/webpage/oracle-l


Other related posts: