Re: Where does GC grab historical SQL plans from?
Date: Tue, 17 May 2011 09:14:36 -0500 (CDT)
> 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
>> 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 22.214.171.124 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?