Ah. I see the objection, reading farther down the thread. Okay. I was only *guessing* that COMPATIBLE would probably affect optimizer features -- in addition to many others. While it turns out that the example/suggestion I have given -- about optimizer issues resulting from changing COMPATIBLE -- is probably fatuous, this does not really affect the discussion. Changing COMPATIBLE from 10.2.0.3 to 11.0.2.4 *might* have unpredictable affects on the application. It is a one-way change that cannot be reverted without rebuilding the database. It is therefore prudent to test carefully before making the change. My apologies to all for a poor choice of illustrations. :-) On Wed, Mar 25, 2015 at 2:49 PM, MARK BRINSMEAD <mark.brinsmead@xxxxxxxxx> wrote: > Yes. I know what COMPATIBLE is for. :-) > > But the *intended* use of COMPATIBLE is not what is being discussed here. > > On Wed, Mar 25, 2015 at 12:16 PM, Carlos Sierra < > carlos.sierra.usa@xxxxxxxxx> wrote: > >> Mark, >> >> COMPATIBLE is used when you do a database upgrade, lets say from 10.2.0.4 >> to 11.2.0.4, and you need (for a few days) to have the capability to go >> back to 10.2.0.4. Once you are comfortable on the new release, you simply >> reset this parameter and let it default for current database release you >> are on. >> >> OPTIMIZER_FEATURES_ENABLE controls many features of the CBO, thus you can >> set it to a prior value when and if needed. I only see some value to do >> this during a few days after an upgrade. For the most part, you also want >> to reset this parameter (remove from SPFILE) and use default value so you >> get to benefit of the new features for your release. >> >> So if you are on 11g, and have been on it for a while, I do not see any >> reason to keep COMPATIBLE set to a prior version. It is not like you will >> downgrade to this prior version anymore. So I would simply reset it (remove >> from SPFILE) and let it default. Yes, you cannot go back to the outdated >> value you had. But again, do you still want to go back to your old release? >> >> Please, corrections are always welcomed. >> >> Carlos Sierra >> Life is Good! >> >> >> >> On Mar 25, 2015, at 11:47, MARK BRINSMEAD <mark.brinsmead@xxxxxxxxx> >> 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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxx> >>> *Sent:* Mar 24, 2015 4:55 PM >>> *To:* Sheehan, Jeremy >>> *Cc:* Oracle-L (oracle-l@xxxxxxxxxxxxx) >>> *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@xxxxxxxxxxxxxxxxx> 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 >>> >> >> >> >