SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to force Oracle using HASH JOIN

qtphamApr 22 2008 — edited Apr 23 2008

Hi,

I have performance issue with our SQL queries. If I use hint /*+ordered use_hash(table_name)*/ the queries run very fast as they use HASH JOIN instead of NESTED LOOPS. I have several thousands of such queries and cannot modify them in order to force Oracle to use HASH JOIN.

I have tried to change parameters in init.ora and re-analyze the database. But the queries still use NESTED LOOPS.

Here are some parameters in my database:

hash_area_size=256M
pga_aggregate_target=6G
pgamax_size=1953125K
pga_aggregate_target=6G

My servers has 16G of RAM each. We are using RAC with 2 nodes.

Please advice.

Thx

Locked Post

New comments cannot be posted to this locked post.

Locked on May 21 2008

Added on Apr 22 2008

2 comments

2,282 views