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

From: Joze Senegacnik <JozeS_at_hermes-plus.si>
Date: Thu, 21 Oct 2004 12:41:02 +0200
Message-ID: <5844F9B4711770478E44A075199030950A5173@simail01.adriatic.snt.eu>

If you are on 9i then you should try to gather system statistics. This = may change the join method from NL to HJ.

Regards, Joze=20

-----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 9: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)?

--=20

Sami Seerangan
Oracle DBA
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 21 2004 - 05:36:39 CDT