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

From: <Prasada.Gunda_at_hartfordlife.com>
Date: Fri, 30 Jul 2004 11:10:59 -0400
Message-ID: <OFC53AE99D.41575BC8-ON85256EE1.00504D3C-85256EE1.0053045D@hartfordlife.com>

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_at_freelists.org 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

Received on Fri Jul 30 2004 - 10:07:46 CDT