How to check and disable Adaptive Cursor Sharing in 11g

_optimizer_adaptive_cursor_sharing=false disables the feature.

There are 2 new columns in V$sql , IS_BIND_SENSITIVE and IS_BIND_AWARE that indicate the status for individual cursors.

1.) The parameter “_optimizer_adaptive_cursor_sharing” can be changed “on the fly”. This means if you issue an ‘alter system set “_optimizer_adaptive_cursor_sharing” = false |true; ‘ will be reflected in any existing session.

Remember, to disable ACS in 11g ,you should also set alter  system set “_optimizer_extended_cursor_sharing_rel”=’NONE’;

The parameter can be set at session or system level.
When set to NONE it stops the code from maintaining the internal statistical data about the binds.

 

And I advise you set “_optimizer_extended_cursor_sharing” = NONE .

 

2.) show parameter will always retrieve non-default settings also for hidden parameters:

sho parameter adapt
_optimizer_adaptive_cursor_sharing boolean FALSE

1.) non-default hidden (=underscore) parameters are shown with “show parameter ”
2.) the setting of hidden (=underscore) parameters are not supposed to be queried by end users.
3.) You may use 10053 tracing for obtaining the information for optimizer related parameters

sqlplus
set lines 200
set null null
set pages 99
set timi on
set time on

alter session set max_dump_file_size=unlimited;
alter session set events ’10053 trace name context forever, level 1′;

– execute a statement causing a hardparse:

select /* a new comment */ * from dual;

exit

-> Use an editor or an unix command ( ie grep) and search for the _optimizer_adaptive_cursor_sharing parameter in the tracefile.

If you want to restore Optimizer_enabled_features from 11.2.0.1 to 10.2.0.4 , then you set:

alter session set "_optimizer_undo_cost_change" = '10.2.0.4'; -- 11.2.0.1alter session set "_optimizer_null_aware_antijoin" = false; -- truealter session set "_optimizer_extend_jppd_view_types" = false; -- truealter session set "_replace_virtual_columns" = false; -- truealter session set "_first_k_rows_dynamic_proration" = false; -- truealter session set "_bloom_pruning_enabled" = false; -- truealter session set "_optimizer_multi_level_push_pred" = false; -- truealter session set "_optimizer_group_by_placement" = false; -- truealter session set "_optimizer_extended_cursor_sharing_rel" = none; -- simplealter session set "_optimizer_adaptive_cursor_sharing" = false; -- truealter session set "_optimizer_improve_selectivity" = false ; -- truealter session set "_optimizer_enable_density_improvements" = false; -- truealter session set "_optimizer_native_full_outer_join" = off; -- forcealter session set "_optimizer_enable_extended_stats" = false; -- truealter session set "_nlj_batching_enabled" = 0; -- 1alter session set "_optimizer_extended_stats_usage_control" = 255; -- 224alter session set "_bloom_folding_enabled" = false; -- truealter session set "_optimizer_coalesce_subqueries" = false; -- truealter session set "_optimizer_fast_pred_transitivity" = false; -- truealter session set "_optimizer_fast_access_pred_analysis" = false; -- truealter session set "_optimizer_unnest_disjunctive_subq" = false; -- truealter session set "_optimizer_unnest_corr_set_subq" = false; -- truealter session set "_optimizer_distinct_agg_transform" = false; -- truealter session set "_aggregation_optimization_settings" = 32; -- 0alter session set "_optimizer_connect_by_elim_dups" = false; -- truealter session set "_optimizer_eliminate_filtering_join" = false; -- truealter session set "_connect_by_use_union_all" = old_plan_mode; --truealter session set "_optimizer_join_factorization" = false; -- truealter session set "_optimizer_use_cbqt_star_transformation" = false; -- truealter session set "_optimizer_table_expansion" = false ; -- truealter session set "_and_pruning_enabled" = false ; -- truealter session set "_optimizer_distinct_placement" = false ; -- truealter session set "_optimizer_use_feedback" = false ; -- truealter session set "_optimizer_try_st_before_jppd" = false ; -- truerefer http://www.askmaclean.com/archives/how-to-check-and-disable-adaptive-cursor-sharing-in-11g.html