一.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之后:建议全部开启。