| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 11 2006 - 10:53:49 CST
 |
 |