7 Replies Latest reply: Feb 11, 2013 8:34 AM by marksmithusa RSS

    optimizer_feature_enable in 11G kept to 10G?

    Suddhasatwa_Bhaumik
      Hello,

      In our platform we upgraded few databases to 11.2.0.3 after which we were seeing performance issues in some critical SQL’s. Because of the fact that we were hitting deadlines, we had set the value of optimizer_feature_enable (otherwise, OFE) to 10.2.0.3 in our 11.2.0.3 databases. Post this change, all queries are running fine.

      Do you see any architectural/performance impact on our databases in 11.2.0.3 if we keep the OFE to 10.2.0.3? We know, we would not get the new CBO features in this case, but are there any other negative impact(s) which you foresee with this configuration? Kindly advice.

      Note: Oracle suggests we may keep it to 10.2.0.3 and regular support would be available, but in case we hit any bugs w.r.t the optimizer/CBO they would not generate/deliver any fixes for us.

      OS is Solaris 10.

      Regards,
      Suddhasatwa
        • 1. Re: optimizer_feature_enable in 11G kept to 10G?
          kuljeet singh -
          SBhaumik_DBA wrote:
          Hello,

          In our platform we upgraded few databases to 11.2.0.3 after which we were seeing performance issues in some critical SQL’s. Because of the fact that we were hitting deadlines, we had set the value of optimizer_feature_enable (otherwise, OFE) to 10.2.0.3 in our 11.2.0.3 databases. Post this change, all queries are running fine.

          Do you see any architectural/performance impact on our databases in 11.2.0.3 if we keep the OFE to 10.2.0.3? We know, we would not get the new CBO features in this case, but are there any other negative impact(s) which you foresee with this configuration? Kindly advice.

          Note: Oracle suggests we may keep it to 10.2.0.3 and regular support would be available, but in case we hit any bugs w.r.t the optimizer/CBO they would not generate/deliver any fixes for us.

          OS is Solaris 10.

          Regards,
          Suddhasatwa
          must read
          http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf

          in your case,just enable sql plan baseline feature.
          after few days/week once 10g plan loaded into plan history then you can set optimizer_feature_enable =11.2.0.3,now oracle will not use the new 11g plan until it will provide more benefit from existing plan.

          also read
          http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdfi
          • 2. Re: optimizer_feature_enable in 11G kept to 10G?
            Suddhasatwa_Bhaumik
            Hi Kuljeet

            Thanks for the suggestions above.

            I would try out the same and update all.

            Thanks,
            Suddhasatwa
            • 3. Re: optimizer_feature_enable in 11G kept to 10G?
              Suddhasatwa_Bhaumik
              Hello All,

              We have reverted the OFE to 11.2.0.3 and the issues were being resolved. However, below is the course of action(s) which took place from our end, which makes our client a bit worried:

              1. We upgraded databases from 10g to 11g using the standard procedure, and no issues were reported. We even tested fallback to 10g version and it worked well.
              2. Client starts the UAT testing of applicaiton after upgrade. At this time, OFE = 11.2.0.3.
              3. After 50% of UAT is completed, client reports few problematic queries.
              4. We change the OFE to 10.2.0.3 and see good improvements, and hence we keep OFE as 10.2.0.3 from here onwards.
              5. Rest of the UAT is completed, and we change OFE back to 11.2.0.3.

              Now, half of UAT is done with OFE = 11g and rest with OFE = 10g. What impact should this make on the Application?
              I.e., How much change or difference in behaviour do you/all expect, based on your experiences, with above steps being done?

              Note: We are aware that ideally the OFE should be equal to the DB version for getting new features of the CBO. Oracle also suggests the same, so that we get the new features, as well as we get the bug fixes for newer version. However, we are looking for a definite solution to the above, so as to determine what level of impact our changes to OFE has caused?

              The front end applicaiton is PeopleSoft HRMS 8.90.000 with PeopleTools 8.49.00.

              Regards,
              Suddhasatwa
              • 4. Re: optimizer_feature_enable in 11G kept to 10G?
                Suddhasatwa_Bhaumik
                Further addition -- the 11Gr@ manual says the below about new features added in 11G.
                Enhanced Bind Peeking
                Use extended statistics to estimate selectivity
                Use native implementation for full outer joins
                Partition pruning using join filtering
                Group by placement optimization
                Null aware antijoins
                Please advice. Thanks.
                • 5. Re: optimizer_feature_enable in 11G kept to 10G?
                  Dom Brooks
                  If setting OFE works for you, then that's a valid short-term approach.

                  There are a number of alternative approaches.

                  If the scope of the problem is a handful of statements, then it's better to use a solution that matches the scope.
                  Setting OFE system-wide is going to deprive other statements of a possible performance lift as well as giving complications should you run into other bugs, etc or require patches, etc.

                  You could use sql plan baselines as mentioned by Kuljeet to lock in a few executions plans for the statements that are causing you problems.

                  You could hint OFE at a statement level.

                  If you can't change the source code and you don't want to lock in a full baselined plan, then you could look at using the [url http://orastory.wordpress.com/2012/03/06/sql-patch-i/]SQL Patch functionality .

                  I would always want to identify the SQL that has degraded and identify why it has problems or what feature is adversely affecting it.
                  In my experience, it is often the SQL that is poorly written/expressed that has performance issues after upgrade.

                  I would also advocate extending your AWR retention prior to upgrade so you can get the pre upgrade plans for your biggest hitting SQL should they degrade post upgrade, you can then use the Baseline or SQL Profile mechanisms to quickly lock in a previous plan.

                  To get a better list of optimizers features that have changed between 10.2.0.3 and 11.2.0.3, look at V$SYSTEM_FIX_CONTROL, e.g.:
                  select *
                  from   v$system_fix_control
                  where  optimizer_feature_enable like '11%'
                  or     optimizer_feature_enable = '10.2.0.4'
                  order by optimizer_feature_enable, bugno;
                  Also see this post by Coskan about using this information to identify any specific fixes which have affected your execution plan:
                  http://coskan.wordpress.com/2011/01/17/11-2-0-2-performance-stories/
                  • 6. Re: optimizer_feature_enable in 11G kept to 10G?
                    Suddhasatwa_Bhaumik
                    Hi Dom

                    Thanks very much for the above information! It was very helpful!

                    However I was trying to figure out what impact it had made when we ran UAT half way with OFE = 10g and half way with 11g.

                    Honestly speaking, looking at the documentation of 11gr2 from where I have given my last reply, I do not foresee much change in our PeopleSoft environment while using OFE back to 10g. However, it is always better to cross check with experts on OTN!

                    As of now we have reverted back to 11G and things are working fine, however above query is to look for the consistency of UAT.

                    Please advice .

                    Regards,
                    Suddhasatwa
                    • 7. Re: optimizer_feature_enable in 11G kept to 10G?
                      marksmithusa
                      We did the same: after an upgrade to 11.2.0.3, we had two batch jobs which decided to take forever post-upgrade. Turns out that they weren't tested by the project before the upgrade.

                      As a stop-gap measure, we reduced optimizer_features_enable back to 10.2.0.3, had the project re-code the two batch jobs, check them in test and then move the OFE back to 11.2.0.3.

                      I guess this is why Oracle don't recommend updating the compatible parameter after an upgrade until you're 100% sure all is well!