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: Ryan <ryan_gaffuri_at_comcast.net>
Date: Wed, 20 Oct 2004 22:23:52 -0400
Message-ID: <003601c4b715$026a3e90$350a5444@ryannew>


I've found that oracle only picks the wrong join type if table stats are not accurate. re-analyze your tables.

check the number of records in each table vs. the row_nums column in dba_tables. see if they are close to being the same. Odds are they are not. oracle chooses join types and order based primarily on the following:

  1. index on appropriate columns
  2. estimate number of rows scaned
  3. relative table sizes.

2 and 3 are based on stats.

> 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 - 21:19:15 CDT

Original text of this message

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