RE: Poor performance with Histogram
- From: "Richard J. Goulet" <rgoulet@xxxxxxxxxx>
- To: <shivaswamykr@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Dec 2006 10:17:59 -0500
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@xxxxxxxxxx
: POWERING TRANSFORMATION
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
