Re: Poor performance with Histogram

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


Other related posts: