Oracle绑定变量窥视与自适应游标共享

一.Oracle的绑定变量窥视与自适应游标共享

创建test表，列status存在2个值，有数据倾斜，在列status

create table test
as 
select rownum id,
    DBMS_RANDOM.STRING('A',12) name,
    DECODE(MOD(ROWNUM,500),0,'Inactive','Active') status
from all_objects
where rownum<=50000;

#建索引
create index test_id_ind on test(status);

#由于列倾斜，所以收集直方图信息
EXEC dbms_stats.gather_table_stats(user,'test',method_opt=>'for columns status size 254',cascade=>true);

select count(*) cnt,status from test group by status;

问题：如果SQL硬解析生成执行计划时“窥视”到的绑定变量刚好只适合少部分数据，不适合其他大多数数据，那么直到下一次硬解析前（一般是收集统计信息后才会触发硬解析），都会使用这个不优的执行计划。

解决：

1.收集直方图，当查询Active的时候，直方图观察到绝大多数都是Active，则会走全表扫描，当查询Inactive的时候，直方图观察到绝大多数都是Inactive，则会走索引扫描。

2.11g之后的自适应游标共享特性，优化器在使用adaptive cursor sharing后，已经可以为一个带有绑定变量的SQL产生出多个执行计划，这个特性非常有用对于列上数据有倾斜，在列上收集了直方图的SQL

• 使用自适应游标共享时，会遵循以下的步骤：

    1) 一条新的SQL语句第一次传入shared pool时，还是和曾经一样，进行硬解析。并且进行绑定变量窥视，计算where条件各个列的selectivity，该游标会被标记为是一个绑定敏感的游标（bind-sensitive cursor）。同一时候，oracle还会保留包括绑定变量的where条件的其它信息，比方selectivity等。Oracle会为该selectivity维持一个范围，oracle叫做立方体（cube）。仅仅要传入的绑定变量所产生的selectivity落在该范围里面，也就是落在该cube里面，就不产生新的运行计划，而直接拿该cube所相应的运行计划来用。

    2) 下次再次运行同样的SQL时，传入了新的绑定变量，如果使用新的绑定变量的selectivity落在已经存在的cube范围里，于是这次SQL的运行会使用该cube所相应的运行计划。

    3) 同样的查询再次运行时，如果所使用的新的绑定变量导致这时候的selectivity不再落在已经存在的cube里了，于是也就找不到相应的运行计划。于是系统会进行一个硬解析，这将产生第二个新的运行计划。并且新的selectivity以及相应的cube也会保存下来。也就是说，这时，我们分别有两个cube以及两个运行计划。

    4) 同样的查询再次运行时，如果所使用的新的绑定变量导致这时候的selectivity不落在现存的两个cube中的不论什么一个，所以系统又会进行硬解析。如果这时硬解析所产生的运行计划与第一次产生运行计划一样，也就是说，在第一次评估selectivity的cube时过于保守，导致cube过小，进而导致了这一次的不必要的硬解析。于是，oracle会将第一次产生的cube与这次产生的cube合并成一个新的更大的cube。那么，下次再次进行软解析的时候，如果selectivity落在新的cube里，则会使用第一次所产生的运行计划。

实验:

var a varchar2(100)

set autotrace off

exec :a :='Inactive'

select /*+ find_me */ count(name) from test where status = :a;

select sql_text,sql_id,executions from v$sql where sql_text like '%find_me%';

查询ACS状态：

select sql_text,sql_id,executions from v$sql where sql_text like '%find_me%';

 select child_number,executions,buffer_gets,is_bind_sensitive s,is_bind_aware a from v$sql where sql_id='b3vywf9pt4cab';

Y表示该语句正在被窥视中，证明不确定该语句是不是执行的很好。

查看执行计划：

select * from table (dbms_xplan.display_cursor('b3vywf9pt4cab',0));

可以看到该语句是走索引的，是没有问题的，因为我给的绑定变量值是Inactive。

但是如果这时候 我给的绑定变量值是active，那就有很大的问题了，因为这时候全表扫描是最好的选择。

exec :a :='Active';

select /*+ find_me */ count(name) from test where status = :a;

此时这个sql仍然还只有一个游标。

多执行几次

select /*+ find_me */ count(name) from test where status = :a;

发现这个时候这个sql有第二个子游标了。A列的Y的意思是它是从其它的游标演变过来的。

绑定变量窥视与自适应共享游标建议：

10g 之前，由于没有ASC参数，绑定变量窥视参数一错到底，建议关闭。

11.2.0.4之后：建议全部开启。