want to REMOVE optimizer_features_enable('9.2.0') dependency from views

hello all,

  is there anyways where i can tell sql optimier to use optimizer_features_enable of is the situation...our DB is on but we are using optimizer_features_enable parameter to 9.2.0 (reason being some views dont work on

so the question is…we want to remove the 9i optimizer dependncy on our views…which some are complex selects…i know optimizer_features_enable can be alter session…so when i am using sql optimizer can i instruct the sql optimizer re-wrtie engine to use optimizer_features_enable of

or how can i get query to work with ??? i know we can use the this hint( /+ optimizer_features_enable(‘9.2.0’)/)…but we dont want too…

if i alter my session to 10203 and the the selects(veiws) with ussing /+ optimizer_features_enable(‘9.2.0’)/ hint…it works…but we do not want to use that hint…

we want to re-write the sql and make them work on

any comments/help is appericiated.


In the Tuning Lab, you can set an Oracle parameter with the following:

  1. Right-click in the SQL Text or Scenario Explorer window.
  2. Select Alter Session Parameters.
  3. Locate optimizer_features_enable.
  4. Change the value.


THANKS FOR THE HELP RENE…but now one more Q…once i change that…will it produce sql re-write based on the optimizer ??

and also is there anyways in the options or anything to avoid generating Store outlines ??


The function Rene suggested does not apply to the optimization. It looks like you are looking for a way to set the session parameter for the session used to optimize the SQL. This kind of function is not available in the current release. However, we do have an enhancement request to provide this function in our next release.

For generating outlines, the optimization process does not generate outlines for you automatically. Only if you right-click a rewrite and choose Deploy Outline will the product create an outlines in your database.


thanks alex…