oracle自适应游标,《基于ORACLE的SQL优化读书》笔记 自适应游标共享

自适应游标共享：(通过适时触发硬解析动作，缓解绑定变量窥探带来的副作用)

Exec dbms_stats.gather_table_stats( method_opt=>’for allcolumns size 1’) 不收集直方图统计信息

Exec dbms_stats.gather_table_stats( method_opt=>’for allcolumns size auto’) 自动收集直方图统计信息

1.      将child cursor标记为bindsensitive(执行计划可能需要根据绑定值的变化而变化)

a.      启用绑定变量窥探

b.     使用绑定变量(或是cursor_sharing =’force’)

c.      SQL使用了不安全谓词

2.      将child cursor标记为BindAware(执行计划确定需要根据绑定值的变化而变化)

ORACLE会根据执行目标SQL时，对应的RUNTIME统计信息(逻辑读，CPU，结果集行数)，以及当前绑定变量所在谓词的可选择率来综合综合判断是不是要硬解析。

V$SQL_CS_STATISTICS :显示child cursor中存储的runtime统计信息

V$SQL_CS_SELECTIVITY：显示child cursor中绑定变量谓词条件所对应的可选择率的范围。

bind sensitive到bind aware，需要执行两次。而且 在selectivity那张表中查找出来的COST，好像是对于该绑定变量下，几个不同的PLAN的COST。

对于不同的SELECTIVITY，如果一个CHILD CURSOR 被硬解析过后，则它的SHAREDABLE会被MARK成N。新的cursor会成为bindaware.而且selectivity会合并。

自适应游标共享缺陷：

1.      会导致额外的硬解析。

2.      Child cursor数目会增加(SHARED POOL要增大)

3.      绑定变量个数不能超过14个

create table t1 as select * from dba_objects;

create index idx_t1 on t1(object_type);

update t1 set object_type ='TABLE' where rownum<60001;

update t1 set object_type ='CLUSTER' where rownum<2;

commit;

select count(*) from t1;

select count(*) from t1 where object_type='CLUSTER';

select count(*) from t1 where object_type='TABLE';

execdbms_stats.gather_table_stats(ownname=>'TESTER',tabname=>'T1',estimate_percent=>100,cascade=>true,method_opt=>'forall columns size auto',no_invalidate =>false);

select column_name,num_buckets,histogram fromdba_tab_col_statistics where table_name='T1' and column_name='OBJECT_TYPE';

select object_type,count(*) from t1 group by object_typeorder by 2 desc;

alter system flush shared_pool;

var x varchar2(30);

exec :x := 'CLUSTER';

select count(*) from t1 where object_type =:x;

col sql_text for a10

select sql_text,sql_id,version_count,executions fromv$sqlarea where sql_text like 'select count(*) from t1%';

select * from table(dbms_xplan.display_cursor('8jysbqsh81cr4',0,'advanced'));

col BS for a2

col BA for a2

col SH for a2

select child_number,executions,buffer_gets,is_bind_sensitiveBS,is_bind_aware BA, is_shareable SH,plan_hash_value from v$sql where sql_id='8jysbqsh81cr4';

select child_number,predicate,range_id,low,high fromv$sql_cs_selectivity where sql_id ='8jysbqsh81cr4';

exec :x := 'TABLE';

select count(*) from t1 where object_type =:x;

select count(*) from t1 where object_type =:x;

select sql_text,sql_id,version_count,executions fromv$sqlarea where sql_text like 'select count(*) from t1%';

select * fromtable(dbms_xplan.display_cursor('8jysbqsh81cr4',0,'advanced'));

col BS for a2

col BA for a2

col SH for a2

select child_number,executions,buffer_gets,is_bind_sensitiveBS,is_bind_aware BA, is_shareable SH,plan_hash_value from v$sql where sql_id='8jysbqsh81cr4';

select child_number,predicate,range_id,low,high fromv$sql_cs_selectivity where sql_id ='8jysbqsh81cr4';