3 Replies Latest reply: Oct 8, 2013 10:11 AM by Mark D Powell RSS

    Need to use pre-upgrade version of execution plan.

    DBA

      Hi,

       

      Recently i we have upgraded database from 10.2.0.5 to 11.2.0.3.4 version and one of the query is not doing well in 11g. when i set optimizer_features_enable to 10.2.0.5, it is executing effectively.  So i thought of using SQL Plan management.  But challenge is that customer does not use bind variables, uses only literals only. So because of this, each sql is getting executed with different SQLID. Can any body suggest solution for this situation?

       

       

      Regards

      DBA.

        • 1. Re: Need to use pre-upgrade version of execution plan.
          Ramin Hashimzadeh
          1. show your execution plan for 10.2.0.5 and 11.2.0.3
          2. if you are using literal instead of bind variables , read about "cursor_sharing"=FORCE parameter
          3. Were you gather Stats after upgrade?
          4. Were you using any hints on query?

           

          ----

          Ramin Hashimzade

          • 2. Re: Need to use pre-upgrade version of execution plan.
            Alvaro

            Submit the sql to SQL tuning advisor. Once it suggests the profile create it with the FORCE MATCH = TRUE which will cause the profile to be shared between the statements that only differ on the literals, through the force matching signature.

            • 3. Re: Need to use pre-upgrade version of execution plan.
              Mark D Powell

              If the SQL uses constants then what you probably want to look at are the histograms on the table and compare the type, number of buckets, etc ... between the two database and determine if differences in the histogram collection may be influncing the plan.  Also checking the setting of cursor_sharing on both instances. would be worthwhile since a change could subject the SQL to bind variable peeking issues.  Forcing cursor sharing can have wide ranging effects on performance and I have seen it do more harm than good, but some shops get away with it.

               

              HTH -- Mark D Powell --