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: Why the optimizer is not choosing the best plan?

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

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 20 Oct 2004 15:19:39 -0500
Message-ID: <008501c4b6e2$209e35c0$6400a8c0@CVMLAP02>


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

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
Received on Wed Oct 20 2004 - 15:16:35 CDT

Original text of this message

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