Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Why the optimizer is not choosing the best plan?

Why the optimizer is not choosing the best plan?

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Wed, 20 Oct 2004 15:49:01 -0400
Message-ID: <f09dd6280410201249139e9d9c@mail.gmail.com>


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 Received on Wed Oct 20 2004 - 14:44:35 CDT

Original text of this message

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