Is there any way to disable hash semi-join in 9i?




We are in the process of migrating our DW from Oracle version 8.1.7.4 to
9.2.0.5. Some of the queries in 9i are using hash semi-joins wherever it is
possible and taking very long time to execute the query. The same query
uses just hash joins and runs much faster in 8i environment.  Both 9i and
8i on Hp-ux v11.

When I was researching on the metalink to see if any one has experienced
similar problems, I found that there is a bug ( Bug 3216002 - HASH JOIN
ANTI / SEMI can use a lot of CPU / spin ) with hash semi/anti join.  It
says the bug is fixed in 10g.

I would like to know if there is any way to disable hash semi join (do not
want to disable hash joins) either at the instance level (by specifying any
parameters in init.ora) or at the query level by specifying hint. I have
gone thru the documentation but did not find any way to disable it.   There
is a hidden parameter '_always_semi_join' and has the value 'CHOOSE'. I
tried to disable it by specifying various values (FALSE, NONE etc) in
init.ora but no success. Also, even if it works, specifying this parameter
would be acceptable by Oracle Support (or) would it invalidates the Oracle
support on this database?

select a.ksppinm NAME,
a.ksppdesc DESCRIPTION,
b.ksppstvl SESSION_VALUE,
c.ksppstvl SYSTEM_VALUE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '_always_semi_join'
/

Name                       Description
Session_value    System_value
_always_semi_join  always use this method for semi-join when possible
CHOOSE             CHOOSE

Thanks in advance for your help and suggestions.

Best Regards,
Prasad
860 843 8377



*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for 
the exclusive use of addressee and may contain proprietary, confidential and/or 
privileged information.  If you are not the intended recipient, any use, 
copying, disclosure, dissemination or distribution is strictly prohibited.  If 
you are not the intended recipient, please notify the sender immediately by 
return e-mail, delete this communication and destroy all copies.
*************************************************************************

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: