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>:

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

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