RE: Poor performance with Histogram

Maybe setting the event 10053, level 1 cout tell you something about the optimizer decisions and the reasons for them?

There is a document, available from Hotsos library, called "A look under the hood of CBO", by Wolfgang Breitling and

there is a Metalink article named "Case Study: Analyzing 10053 Trace Files" which explain how to use this trace.

Jonathan's book also explains in detail how to abuse the 10053 trace.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Shivaswamy Raghunath
Sent: Monday, December 11, 2006 10:39 AM To: ORACLE-L
Subject: Poor performance with Histogram  

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.

Thanks,
Shiva

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