Oracle自适应游标共享流程测试实践
流程测试实践
环境信息
1 | SELECT * FROM V$VERSION; |
准备工作
1 | create table t1 as select * from dba_objects; --创建测试表 |
- 索引列数据分布
1 | -- 收集直方图统计信息 |
执行测试
以下测试请在命令窗口执行
- 首次执行,标记Bind Sensitive
- 脚本
1 | var x varchar2(30); |
- 结果
v$sqlarea
1 | select t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS from v$sqlarea t where t.SQL_TEXT like 'select count(1) from t1%'; |
v$sql
1 | select t.PLAN_HASH_VALUE,t.CHILD_NUMBER,t.EXECUTIONS,t.BUFFER_GETS,t.IS_BIND_SENSITIVE,t.IS_BIND_AWARE,t.IS_SHAREABLE from v$sql t where t.SQL_ID = '0tvhwm0fmzzvx'; |
v$sql_cs_statistics
1 | select * from v$sql_cs_statistics t where t.SQL_ID = '0tvhwm0fmzzvx'; |
执行计划
1 | select * from table(dbms_xplan.display_cursor('0tvhwm0fmzzvx',0,'advanced')); |
绑定变量
- 分析
目标SQL已被标记Bind Sensitive,但现在还未标记Bind Aware
- 调整变量,重复执行,标记Bind Aware
- 脚本
1 | exec :x := 'TABLE'; |
- 结果
v$sqlarea
v$sql
v$sql_cs_statistics
执行计划
绑定变量
v$sql_cs_selectivity
- 分析
调整变量后v$sql记录的统计信息(BUFFER_GETS)发生较大变化,且绑定变量为“TABLE”时执行了两次,故目标SQL重新硬解析生成了CHILD_NUMBER为1的执行计划,并将对应Child Cursor标记为了Bind Aware,v$sql_cs_selectivity试图也记录了对应的选择率
- 拓展
v$sql_cs_selectivity 选择率计算逻辑
Oracle首先计算做硬解析时(做了绑定变量窥探后)上述谓词条件的可选择率(这里将计算出来的可选择率记为S),然后将S上下浮动10%就得到了上述可选择率的范围,即可选择率范围的计算公式为[0.9S,1.1S]
另外,在目标列有Frequency类型直方图的前提条件下,如果对目标列施加等值查询条件,且该查询条件的输入值等于该列的某个实际值时,则该谓词条件的可选择率的计算公式为如下所示:
selectivity = BucketSize /NUM_ROWS
注释:
A 上述计算公式适用于启用了绑定变量窥探,目标列有Frequency类型的直方图,对目标列施加等值查询条件,且查询条件的输入值等于该列的某个实际值时的计算。
B NUM_ROWS表示目标列所在表的记录数。
C BucketSize表示目标列的某个实际值所对应的记录数。
- 切换不同参数,扩展执行计划对应的选择率范围
- 脚本
1 | exec :x := 'SYNONYM'; |
- 结果
v$sqlarea
v$sql
v$sql_cs_statistics
执行计划
绑定变量
v$sql_cs_selectivity
- 分析
切换不同参数后,仍然进行的是硬解析,执行计划也相同(INDEX RANGE SCAN),且对应生成了CHILD_NUMBER为2,3的Child Cursor,但是会发现Oracle做了Cursor合并,CHILD_NUMBER为0,1的Child Cursor不再共享(v$sql.IS_SHAREABLE)将会被废弃,不再可用。另外,v$sql_cs_selectivity试图中会对该执行计划的选择率进行扩展,下限从0.036350变成了0.000012。
- 根据绑定变量对应的可选择率,来决定目标SQL的执行时进行硬解析还是软解析/软软解析
- 脚本
1 | exec :x := 'VIEW'; |
- 结果
v$sqlarea
v$sql
- 分析
当切换到新的绑定变量值时,硬解析次数未增加,执行次数增加了,也未生成新的Child Cursor