Thanks everybody for the input. I will put in my efforts to see how best I
can understand it.
On 12/11/06, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> At 08:39 AM 12/11/2006, Shivaswamy Raghunath wrote:
> >Hello listers.
> >
> >I have used histograms to imporve performance drastically on several
> >earlier occassions on our DSS databases. But recently, it took me
> >quite a while to determine that by removing histograms, I am able to
> >run one critical query - the report from which was happened to be of
> >interest to my CEO - far faster than with histogram. The test query
> >- involving two tables, one partitioned(60 Million) and another
> >regular (13 Million), with outer joins, view merging, few aggregate
> >sorts and sub queries- completes in under 5 sec without histograms
> >while it takes nearly 17 minutes with histograms.
> >
> >Can you tell me where I can look to understand this. Plans are
> >different, of course. But how CBO fails to evaluate the plan I could
> >not comprehend. I am in the process of studying Jonathan Lewis
> >(Chapter 7 & 14) to understand. But any input/insight would greatly
> >be appreciated.
>
> Without more detail I can't tell why the CBO creates the plans that it
> does.
> I keep saying, and have been for some time, that histograms are like
> drugs - for the right "illness" and in the right dosage they can work
> wonders, but an indiscriminant overdose - aka 'for all columns size
> {254 | skewonly | auto }' - can kill (performance)". Glad (sort of)
> to see confirmation.
>
>
> 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
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 11 2006 - 13:04:15 CST
