流程测试实践

环境信息

1
SELECT * FROM V$VERSION;

环境信息

准备工作

1
2
3
4
5
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; --查看索引列数据分布情况
  • 索引列数据分布

索引列数据分布

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 收集直方图统计信息
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';

直方图统计信息

执行测试

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

  1. 首次执行,标记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%';

image-20231109143631901

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';

image-20231109143709879

v$sql_cs_statistics

1
select * from v$sql_cs_statistics t where t.SQL_ID = '0tvhwm0fmzzvx';

image-20231109143804652

执行计划

1
select * from table(dbms_xplan.display_cursor('0tvhwm0fmzzvx',0,'advanced'));

image-20231109144437061

绑定变量

image-20231109144514513

  • 分析

目标SQL已被标记Bind Sensitive,但现在还未标记Bind Aware

  1. 调整变量,重复执行,标记Bind Aware
  • 脚本
1
2
exec :x := 'TABLE';
select count(1) from t1 where object_type = :x; --执行两次
  • 结果

v$sqlarea

image-20231109151313125

v$sql

image-20231109151349098

v$sql_cs_statistics

image-20231109151427989

执行计划

image-20231109151456415

绑定变量

image-20231109151525820

v$sql_cs_selectivity

image-20231109151622317

  • 分析

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

image-20231109153044206

  1. 切换不同参数,扩展执行计划对应的选择率范围
  • 脚本
1
2
3
4
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

image-20231109153739280

v$sql

image-20231109153804361

v$sql_cs_statistics

image-20231109153847282

执行计划

image-20231109153922967

绑定变量

image-20231109153957160

v$sql_cs_selectivity

image-20231109164745204

  • 分析

切换不同参数后,仍然进行的是硬解析,执行计划也相同(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。

  1. 根据绑定变量对应的可选择率,来决定目标SQL的执行时进行硬解析还是软解析/软软解析
  • 脚本
1
2
exec :x := 'VIEW';
select count(1) from t1 where object_type = :x;
  • 结果

v$sqlarea

image-20231109171745546

v$sql

image-20231109171901222

  • 分析

当切换到新的绑定变量值时,硬解析次数未增加,执行次数增加了,也未生成新的Child Cursor