oracle acs ecs 绑定变量窥视 列倾斜解决方案

--解决绑定变量偷窥+不同变量值分布不均匀问题的解决方案：
------打开绑定变量窥视、打开ECS、打开ACS
-------缺点: 1>bug  多  2>占用过多的SGA空间  3>过多的子游标 4 >过多的子游标导致mutex争用  5>解析死循环，挂死

--绑定变量偷窥打开
alter system set "_optim_peek_user_binds"=TRUE scope=spfile sid='*';
-ECS扩展子游标共享打开
alter system set "_optimizer_extended_cursor_sharing"='udo' scope=spfile sid='*';
alter system set "_optimizer_extended_cursor_sharing_rel"='simple' scope=spfile sid='*';
--ACS自适应子游标共享打开
alter system set "_optimizer_adaptive_cursor_sharing"=true scope=spfile sid='*' ;




绑定变量偷窥(bind peeking)--概念:
  首次执行-硬解析，偷窥一眼绑定变量的值（比如绑定变量字段是status，只有VALID和INVALID 两个值)
      1>如果为VALID，则走全表扫描，并将执行计划缓存起来。后续执行（软解析），不管传入什么值，都不再偷窥绑定变量的值，沿用全表扫描
	  2>如果为INVALID，则走索引范围扫描，并将执行计划缓存起来。后续执行（软解析),不再偷窥绑定变量的值，沿用索引范围扫描


当遇到硬解析的时候，就可能会因为传入值的数据分布变化，产生新的执行计划
--硬解析发生场景:
    1>首次执行
	2>挤出shared pool，再次执行的时候会发生硬解析，就需要偷窥绑定变量值且只偷窥一次，定下执行计划，后续不再变化
	3>被DDL
	4>grant /revoke
	5>统计信息的搜集（no_invalidate =>false，no_invalidate =》true 不要求立即马上根据新的统计信息硬解析，而是在随后的0-5个小时内硬解析即可) 



最理想的事：
选择率相近的，走相同的执行计划
		

--问题模拟


create  table t1 as select *from sys.dba_objects;
create index idx_t1_1 on t1(status);


--统计分析
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'T1',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size AUTO',
                                no_invalidate    => FALSE,
                                degree           => 1,
                                cascade          => TRUE);
END;
/


var b1 varchar2(20)
exec :b1 :='VALID'
select max(object_id) from t1  where status=:b1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last peeked_binds'));


var b1 varchar2(20)
exec :b1 :='INVALID'
select max(object_id) from t1  where status=:b1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last peeked_binds'));



select max(object_id) from t1  where status='INVALID';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last peeked_binds'));
select max(object_id) from t1  where status='VALID';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last peeked_binds'));


--is_bind_sensitive 绑定敏感
--is_bind_aware  绑定感知
--is_shareable  绑定共享
select child_number,is_bind_sensitive,is_bind_aware,is_shareable,executions,buffer_gets  from v$sql where sql_id='3xy1gfy5k9bab';
CHILD_NUMBER I I I EXECUTIONS BUFFER_GETS
------------ - - - ---------- -----------
           0 Y N N          2        1530
           1 Y Y Y          4        5000
           2 Y Y Y          3          78
select child_number,peeked,executions,rows_processed,buffer_gets,cpu_time from v$sql_cs_statistics where sql_id='3xy1gfy5k9bab';
CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
------------ - ---------- -------------- ----------- ----------
           2 Y          1            454          26          0
           1 Y          1         174344        1250          0
           0 Y          1            454          76          0
		   
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='3xy1gfy5k9bab';
CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           2 =B1                                               0 0.001165   0.001424
           1 =B1                                               0 0.898830   1.098570



select child_number,is_bind_sensitive,is_bind_aware,is_shareable,executions,buffer_gets  from v$sql where sql_id='3xy1gfy5k9bab';
select child_number,peeked,executions,rows_processed,buffer_gets,cpu_time from v$sql_cs_statistics where sql_id='3xy1gfy5k9bab';
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='3xy1gfy5k9bab';

ACS 工作原理:
----case 1：
    1、首次执行，偷瞄一眼绑定变量，确定执行计划A,STATUS 数据分布均匀（统计信息的直方图有或者无）
    2、后续变量不再偷窥，沿用执行计划A
    
---case 2：
    1、首次执行，偷瞄一眼绑定变量，确定执行计划A，STATUS 数据分布不均匀（统计信息有直方图），做个标记：BIND敏感，
	   需要去记录首次硬解析执行时实时的运行信息RunTime1(返回多少行，逻辑读、执行计划作为比较的基准）
    2、如果真的传入了分布不均匀的值，还沿用执行计划A，得到一个执行结果并记录Runtime2 ,如果runtime 2比runtime1 慢很多，下一次执行会硬解析

	3、第三次执行，就可以重新硬解析了，并标记：BIND感知 ：bind aware