RE: Why the optimizer is not choosing the best plan?

See Wolfgang Breitling's work on the subject of Oracle pseudoerror debugging
event 10053. Trace the optimizer's decision-making process. You'll probably
discover either a mis-set instance parameter or that you've accidentally
"lied" to the optimizer regarding data cardinalities, skew, or the like.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary
- SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sami Seerangan
Sent: Wednesday, October 20, 2004 2:49 PM
To: Oracle Discussion List
Subject: Why the optimizer is not choosing the best plan?

Hi All,

I have 4 tables join query(all tables statistics are upto date) that
is working fine if it goes for HASH_JOIN.  But without HINT it is
always doing NL_JOIN and  takes about 50 secs to execute.

However using ORDERED hint it is going for HASH_JOIN and the response
time got reduced to less than 5 secs, but we don't prefer to implement
HINT based solution

Why the optimizer is not choosing the best execution plan(In this case
hash_join rather than NL_join)?


-- 
Sami Seerangan
Oracle DBA
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Other related posts: