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

From: Antonio Belloni <toni_belloni_at_yahoo.com.br>
Date: Wed, 20 Oct 2004 17:18:38 -0300 (ART)
Message-ID: <20041020201838.55460.qmail@web20103.mail.yahoo.com>

Hi,

If you want that Oracle consider hash joins , you should set the following parameters in the init.ora:

HASH_JOIN_ENABLED = TRUE
HASH_AREA_SIZE = n , where n is the amount of memory allocated for hash joins.

Also , consider setting the following parameters: ALWAYS_SEMI_JOIN = HASH
ALWAYS_ANTI_JOIN = HASH So , semi and anti join operations will be using hash join.

For more information about the parameters and their valid values , see "Oracle Reference Guide" in http://tahiti.oracle.com

Regards,
Antonio Belloni

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

---

Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/

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

Received on Wed Oct 20 2004 - 15:14:24 CDT