RE: Poor performance with Histogram
- From: "Mladen Gogala" <mgogala@xxxxxxxxxxx>
- To: <shivaswamykr@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Dec 2006 12:25:23 -0500
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@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:
