Re: Where does GC grab historical SQL plans from?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: rjoralist2@xxxxxxxxxxxxxxxxxxxxx
- Date: Tue, 17 May 2011 18:40:12 -0600
The first thing I do, provided I work with DBAs who listen ( not always the
case as a consultant ), is to turn off histogram collection in the dbms_stats
defaults ( method_opt=>'for all columns size 1' ).
There are several problems with the out-of-the-box default of 'for all columns
size auto':
- you could suddenly get statistics and even a histogram on a column that
didn't have any before, courtesy of the column usage tracking. At least it is
better than the idiotic 'for all indexed columns ...'
- 11g at least fixed that problem, but in 10g you would gather histograms on
columns of large tables with sampling x% of all rows which totally defeats the
purpose of a histogram. Sampling will not adequately reflect the skew of the
values in the column. "Rare" values can easily be missed altogether.
- what good is a histogram on a single-value column - other than messing up the
optimizer's cardinality calculation. Instead of estimating a (equality)
cardinality of equal to num_rows of the table it estimates a cardinality of 1 !!
and more problems. I stand by my phrase "Histograms are like drugs. An overdose
can kill" in the case of histograms it can ( and does ) kill performance and
you use all sorts of patches to cover up the root cause: disable bind peeking,
hints, ACS ( which has its own problems ), flushing the shared pool, etc. All
that can be avoided if you turn "global" histograms off and only collect
histograms for specific columns where it has been proven that they help.
On 2011-05-17, at 8:14 AM, Rich Jesse wrote:
> 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:
