Oracle自适应游标共享流程测试实践

流程测试实践

环境信息

1	SELECT * FROM V$VERSION;

环境信息

准备工作

create table t1 as select * from dba_objects; --创建测试表

create index idx_t1 on t1(object_type); --创建单列索引

select t.object_type, count(1) from t1 t group by t.object_type; --查看索引列数据分布情况

索引列数据分布

索引列数据分布

-- 收集直方图统计信息
BEGIN
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'T1',
                                estimate_percent => 100,
                                cascade          => true,
                                method_opt       => 'for all columns size auto',
                                no_invalidate    => false);
END;

-- HISTOGRAM为FREQUENCY则表示已经存在直方图统计信息
SELECT t.HISTOGRAM, t.*
  FROM DBA_TAB_COL_STATISTICS T
 WHERE T.TABLE_NAME = 'T1'
   AND T.COLUMN_NAME = 'OBJECT_TYPE';

直方图统计信息

执行测试

以下测试请在命令窗口执行

首次执行,标记Bind Sensitive

脚本

1
2
3

var x varchar2(30);
exec :x := 'CLUSTER';
select count(1) from t1 where object_type = :x;

结果

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 2	exec :x := 'TABLE'; select count(1) from t1 where object_type = :x; --执行两次

结果

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表示目标列的某个实际值所对应的记录数。

切换不同参数，扩展执行计划对应的选择率范围

脚本

exec :x := 'SYNONYM';
select count(1) from t1 where object_type = :x;
exec :x := 'CLUSTER';
select count(1) from t1 where object_type = :x;

结果

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。