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

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.

----- Original Message -----
From: "Sami Seerangan" <dba.orcl@xxxxxxxxx>
To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, October 20, 2004 3:49 PM
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: