RE: _convert_set_to_join default
Date: Thu, 1 Mar 2018 19:28:18 +0000
Message-ID: <7062_1519932510_5A98545E_7062_16344_1_ECDEF0CC6716EC4596FCBC871F48292AB1962F49_at_ZRH-S231>
Actually, I haven’t set OFE at all. So it had the default value of 12.2.0.1 . What Franck said is, you have to add the 5th digit to the OFE version in order to activate the features like SJC:
alter session set optimizer_features_enable='12.2.0.1.1' ;
select a.ksppinm name,b.ksppstvl value,b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '%convert_set_to_join%'
order by
17 name ;
NAME
VALUE
DEFLT TYPE
--------- ----------------------------------------DESCRIPTION
_convert_set_to_join
TRUE
TRUE boolean
enables conversion of set operator to join
Nenad
From: Mohamed Houri [mailto:mohamed.houri_at_gmail.com]
Sent: Donnerstag, 1. März 2018 19:32
To: Franck Pachot
Cc: Noveljic Nenad; ORACLE-L (oracle-l_at_freelists.org)
Subject: Re: _convert_set_to_join default
I think that all new CBO parameters introduced in Oracle release x (12.1.0.1 for example) will be unset if you decide to set the optimizer mode at release x-1 (11.2.0.4 for example) during your upgrade process from 11gR2 to 12cR1. Actually I got the same question when I wanted to set the _optimizer_ansi_join_lateral_enhance<https://hourim.wordpress.com/2017/06/02/_optimizer_ansi_join_lateral_enhance/> parameter to FALSE in a 12cR1 and was trying to find if other applications have already done this before me. Indeed I find that this parameter has been unset in several 12cR1 databases without any specific alter system command. All those applications have a common caracteristic: they have set the optimizer mode to 11.2.0.4 during their upgrade process to 12cR1.
Best regards
Mohamed Houri
2018-03-01 16:38 GMT+01:00 Franck Pachot <franck_at_pachot.net<mailto:franck_at_pachot.net>>:
Hi,
This parameter is always false except if you put the optimizer_features_enable to higher than the current version (12.2.0.1.1 for example for your 12.2.0.1).
See: https://blog.dbi-services.com/oracle-121021-set-to-join-conversion/
I don't know why they do that. it is still the case in 18c where it is enabled only if you set OFE=18.1.0.1 where the default is 18.1.0
Regards,
Franck.
On Thu, Mar 1, 2018 at 3:31 PM Noveljic Nenad <nenad.noveljic_at_vontobel.com<mailto:nenad.noveljic_at_vontobel.com>> wrote: Could you please check the value of _convert_set_to_join on your 12.2.0.1.180116 database?
I’m trying to figure out what kind of condition might have flipped it to FALSE (the value wasn’t set in the spfile):
select a.ksppinm name,b.ksppstvl value,b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '%convert_set_to_join%'
order by
name ;
NAME
VALUE
DEFLT TYPE
--------- ----------------------------------------DESCRIPTION
_convert_set_to_join
FALSE
TRUE boolean
enables conversion of set operator to join
I’m on Solaris x86-64 (not sure if it matters).
Thanks,
Nenad
http://nenadnoveljic.com/blog/
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
Important Notice
This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.
E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Member of Oraworld-team<http://www.oraworld-team.com/>
Visit My - Blog<http://www.hourim.wordpress.com/>
Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/> Linkedin Profile<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>
My Twitter<https://twitter.com/MohamedHouri> - MohamedHouri<https://twitter.com/MohamedHouri>
<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <style type="text/css">p { font-family: Arial;font-size:9pt }</style> </head> <body> <p> <br>Important Notice</br> <br>This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.</br> <br>E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.<br/> </p> </body> </html>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 01 2018 - 20:28:18 CET