Re: increasing COMPATIBLE parameter
Date: Wed, 25 Mar 2015 13:04:46 -0400
Message-ID: <CAFQ5ACKnPD2vt1GqzK572PLWytdZokPOwcWQr27htFP3m-7tng_at_mail.gmail.com>
Ok, I stand corrected. I was thinking that compatible had wider scope than optimizer_features_enabled, and that compatible did affect optimizer.
Clearly, that's incorrect.
optimizer_features_enabled affects optimizer functionality, but compatible only affects backward compatibility for purposes of being able to downgrade a database.
You learn (or, in this case, I probably forgot and re-learned) something new every day.
Thanks Niall and Carlos for the correction.
-Mark
On Wed, Mar 25, 2015 at 12:50 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:
> Changing COMPATIBLE should not make any Optimizer changes. It's all about
> on disk storage. I can imagine there might be a few niche edge cases, but
> COMPATIBLE isn't fundamentally an optimizer thing.
>
> The broad brush approach for the optimizer is "OPTIMIZER_FEATURES_ENABLE"
> and then there is individual control via FIX_CONTROL.
>
> On Wed, Mar 25, 2015 at 3:47 PM, MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
> wrote:
>
>> I bet the database uses the optimizer though. :-)
>>
>> There are hundreds, maybe even thousands, of differences in the query
>> optimizer between 10gR2 and 11gR2, and I expect the vast majority of those
>> are suppressed when you run with COMPATIBLE=10.2.0.4
>>
>> You really should do a regression test before making this change,
>> especially considering that it is a one-way thing. You can increase the
>> value of COMPATIBLE, but you can *never* decrease it again. Not unless
>> you are ready to do an export/import.
>>
>> Most likely, you will be perfectly okay. *LOTS* of people complete this
>> upgrade without a hitch. But in the unlikely(?) case that you are not one
>> of them, you'll probably want to show you did "due diligence" first.
>>
>> On Tue, Mar 24, 2015 at 5:15 PM, Sheehan, Jeremy <
>> JEREMY.SHEEHAN_at_nexteraenergy.com> wrote:
>>
>>> It's been modified in a test instance for a few weeks but I didn't
>>> change it. Someone else did and didn't document anything and they don't
>>> remember if anything else was done. This is a very vanilla db. No ASM,
>>> RAC.
>>>
>>> Thanks!
>>>
>>> Jeremy
>>>
>>> Sent from a phone
>>>
>>> *From:* Don Seiler <don_at_seiler.us>
>>> *Sent:* Mar 24, 2015 4:55 PM
>>> *To:* Sheehan, Jeremy
>>> *Cc:* Oracle-L (oracle-l_at_freelists.org)
>>> *Subject:* Re: increasing COMPATIBLE parameter
>>>
>>> This is an EXTERNAL email. Exercise caution. DO NOT open attachments
>>> or click links from unknown senders or unexpected email.
>>> ------------------------------
>>>
>>> I would be sure to test application behavior on a test/staging
>>> database prior to doing this. But then again, that's solid advice for
>>> making any change in a database.
>>>
>>> Sometimes behavior of various components can change with the
>>> compatible parameter. I've seen it in ASM diskgroup compatible changes, for
>>> what its worth.
>>>
>>> Don.
>>>
>>> On Tue, Mar 24, 2015 at 3:50 PM, Sheehan, Jeremy <
>>> JEREMY.SHEEHAN_at_nexteraenergy.com> wrote:
>>>
>>>> Hello,
>>>>
>>>>
>>>>
>>>> I’m on 11.2.0.3 and am looking to update the COMPATIBLE parameter in a
>>>> database from 10.2.0.4 to 11.2.0.3. I’ve searched pretty extensively (gone
>>>> to page 3 of Google results and checked on MOS – 733987.1). Aside from
>>>> taking a backup before making this change, are there any additional things
>>>> I should look for or do after the change?
>>>>
>>>>
>>>>
>>>> Please let me know! Thanks in advance.
>>>>
>>>>
>>>>
>>>> Jeremy
>>>>
>>>
>>>
>>>
>>> --
>>> Don Seiler
>>> http://www.seiler.us
>>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 25 2015 - 18:04:46 CET