Toad World® Forums

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


#1

hello all,

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

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 10.2.0.3

or how can i get query to work with 10.2.0.3 ??? 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 10.2.0.3

any comments/help is appericiated.


#2

Hi,

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.

Rene
SessionParameters.png


#3

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

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


#4

Hi,

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


#5

thanks alex…