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
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
