This discussion is archived
3 Replies Latest reply: Oct 8, 2013 8:11 AM by Mark D Powell RSS

Need to use pre-upgrade version of execution plan.

DBA Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 --

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points