adaptive optimiser features

[raj]

Hi Erman,

How can i disable optimiser adaptive features in EBS.Getting different outputs from prod and test

PROD:
=====

SQL> sho parameter adaptive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
parallel_adaptive_multi_user         boolean     TRUE
SQL>

TEST:
====

SQL> sho parameter adaptive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_adaptive_window_consolidator_enable boolean     TRUE
d
_optimizer_adaptive_cursor_sharing   boolean     TRUE
_optimizer_adaptive_plans            boolean     TRUE
_optimizer_nlj_hj_adaptive_join      boolean     TRUE
_optimizer_strans_adaptive_pruning   boolean     TRUE
_px_adaptive_dist_method             string      CHOOSE
optimizer_adaptive_features          boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
parallel_adaptive_multi_user         boolean     TRUE
SQL>


Thank you

[raj]

I am not sure why there is a diff between prod and test servers but i need to disable all the adaptive features in test.please suggest.I have already disabled using alter system set optimizer_adaptive_features=FALSE scope=spfile IN TEST

[ErmanArslansOracleBlog]

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;
That's it.

You have also hidden parameters set in your TEST environment..
These are shown in "show parameter output".

Normally we don't see them in show-parameter output, but here we see them.. Probably they are explictly set in your init.ora/spfile.

I reviewed some of them and it seems that they are set to their default values.

But still, you need to get rid of them (as you want your TEST to be equal with your PROD in terms of parameters..) Take the necessary actions to equalize those parameter values with PROD.

If you don't see any *adaptive* underscore parameters set in PROD, then you shouldn't see them in TEST either.