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.1
alter session set "_optimizer_null_aware_antijoin" = false; -- true
alter session set "_optimizer_extend_jppd_view_types" = false; -- true
alter session set "_replace_virtual_columns" = false; -- true
alter session set "_first_k_rows_dynamic_proration" = false; -- true
alter session set "_bloom_pruning_enabled" = false; -- true
alter session set "_optimizer_multi_level_push_pred" = false; -- true
alter session set "_optimizer_group_by_placement" = false; -- true
alter session set "_optimizer_extended_cursor_sharing_rel" = none; -- simple
alter session set "_optimizer_adaptive_cursor_sharing" = false; -- true
alter session set "_optimizer_improve_selectivity" = false ; -- true
alter session set "_optimizer_enable_density_improvements" = false; -- true
alter session set "_optimizer_native_full_outer_join" = off; -- force
alter session set "_optimizer_enable_extended_stats" = false; -- true
alter session set "_nlj_batching_enabled" = 0; -- 1
alter session set "_optimizer_extended_stats_usage_control" = 255; -- 224
alter session set "_bloom_folding_enabled" = false; -- true
alter session set "_optimizer_coalesce_subqueries" = false; -- true
alter session set "_optimizer_fast_pred_transitivity" = false; -- true
alter session set "_optimizer_fast_access_pred_analysis" = false; -- true
alter session set "_optimizer_unnest_disjunctive_subq" = false; -- true
alter session set "_optimizer_unnest_corr_set_subq" = false; -- true
alter session set "_optimizer_distinct_agg_transform" = false; -- true
alter session set "_aggregation_optimization_settings" = 32; -- 0
alter session set "_optimizer_connect_by_elim_dups" = false; -- true
alter session set "_optimizer_eliminate_filtering_join" = false; -- true
alter session set "_connect_by_use_union_all" = old_plan_mode; --true
alter session set "_optimizer_join_factorization" = false; -- true
alter session set "_optimizer_use_cbqt_star_transformation" = false; -- true
alter session set "_optimizer_table_expansion" = false ; -- true
alter session set "_and_pruning_enabled" = false ; -- true
alter session set "_optimizer_distinct_placement" = false ; -- true
alter session set "_optimizer_use_feedback" = false ; -- true
alter session set "_optimizer_try_st_before_jppd" = false ; -- true
  1. Apart from checking for a valid operator there are also a number of subsequent bind sensitivity checks that need to be performed before it can be marked as bind sensitive, if it fails any of these the cursor will not be marked as bind sensitive and adaptive cursor sharing would not occur.

    If any of the following checks fail ECS will be disabled :-
    - Extended cursor sharing is disabled
    - The query has no binds
    - Parallel query is used
    - Certain parameters like (“bind peeking”=false) are set
    - Hints are in use
    - Outlines are being used
    - It is a recursive query
    - The number of binds in a given sql statement are greater than 14.

    When all the criteria are met the cursor is marked as bind sensitive and a “sharing context” containing information about execution statistics of the cursor is created and stored against the cursor.

    Cursors that are marked as bind-sensitive can been identified by the column
    IS_BIND_SENSITIVE=Y in V$SQL or V$SQLAREA.

  2. alter system set “_optimizer_extended_cursor_sharing_rel”=none;
    alter system set “_optimizer_extended_cursor_sharing”=none;
    alter system set “_optimizer_adaptive_cursor_sharing”=false;

  3. Pingback: _library_cache_advice和latch:shared pool、latch:shared pool simulator | Ask Maclean Oracle Blog