Recently i we have upgraded database from 10.2.0.5 to 126.96.36.199.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?
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 --