RE: Poor performance with Histogram

Shiva,  

    One of the problems with histograms, especially those that are not refreshed on a regular basis, is that they have a min and max value. If your query specifies a value that is outside the bounds of the histogram then the CBO decides that a full table scan is the only way to handle the problem.  

    Case in point that I've run into. An application (name to remain anonymous) did a delete on a table where the primary key was a negative 1 (-1). Now that's great to eliminate odd rows of data that are not suppose to be there, but there was a trigger on the table to prevent a negative number from ever entering. CBO decided that the only way to resolve this statement was to fully scan the table of 127 million rows. The Band-Aid was to disable the trigger, force a -1 into the column, refresh the histogram, delete the -1 row, and re-enable the trigger. Thereafter we NEVER refreshed that table's histograms which made the app run great, until the vendor could "fix" the code. Which they did, 6 months later.    

Dick Goulet, Senior Oracle DBA

45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

RGoulet_at_kanbay.com
: POWERING TRANSFORMATION  

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 queriescompletes  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