Re: dbms_stats messing up sys schema

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 10 Nov 2006 09:39:10 -0700
Message-Id: <20061110163923.40F234A65EE@turing.freelists.org>

At 01:55 AM 11/10/2006, Stefan Knecht wrote:
>Wolfgang,
>
>could you provide some more details why gathering histograms on all
>columns can result in bad performance ? Isn't it generally good
>practice to provide the optimizer with as much information as possible ?

Ever heard of information overload? ;-)

But seriously. In an ideal world, yes, unnecessary histograms, e.g. on a column with unique values or on a column with just a single value or ..., should not cause problems (other than wasting space and cpu cycles). But we do not live in an ideal world and I HAVE experienced a case where changing the stats gathering from 'for all columns size 1' to 'for all indexed columns size skewonly' caused a severe performance problem ( I referred to that in my presentation "Histograms - Myths and Facts" at the CBO Days ). The optimizer's codepath and calculations when histograms are present are different from the non-histogram case and the CBO has to again make certain simplifying assumptions which could well be further off than those it makes without histograms - in special circumstances. Particularly where unnecessary histograms, some of which I outlined at the beginning, are involved.

For example. Logically and (thankfully) resultwise there is no difference between a join predicate

select ... from A, B where A.col1 = B.col2

and

select ... from A, B where B.col2 = A.col1

Surprise - with histograms on col1 and col2 there may be a difference and if you are unlucky that difference can result in a different access path with different performance. And in this case I'm not even talking about unnecessary histograms on columns with no skew in the value distribution but even with perfectly legitimate histograms on columns with severe non-uniform distribution.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

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

Received on Fri Nov 10 2006 - 10:39:10 CST