Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Poor performance with Histogram

Re: Poor performance with Histogram

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 11 Dec 2006 09:53:49 -0700
Message-Id: <20061211165312.62FD7562BA2@turing.freelists.org>


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
Received on Mon Dec 11 2006 - 10:53:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US