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@xxxxxxxxx> 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@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 >>> >> >> > > > -- > Niall Litchfield > Oracle DBA > http://www.orawin.info >