Adaptive Cursor Sharing（第二篇）
选择率和硬解析
我们上面提到了，在v$sql_cs_histogram视图中，如果此游标的3个桶中出现了两个桶中的count都有非0值，那么此后的解析都要窥探绑定变量的值计算谓词选择率，如果计算选择率不在现有的游标的选择率范围内，就会基于窥探到的绑定变量的值重新硬解析产生一个新的游标，当然这个新游标的执行计划可能与之前是一样的。我们还是来看一个例子就会非常明白这种机制了。
|
SQL>create table t as select 1 id,a.* from dba_objects a,dba_objects b where rownum<10;
Table created.
SQL>create index t_ind on t(id);
Index created.
SQL>insert into t select 2,a.* from dba_objects a,dba_objects b where rownum<1000;
999 rows created.
SQL>insert into t select 3 ,a.* from dba_objects a,dba_objects b where rownum<10000;
9999 rows created.
SQL>insert into t select 4 ,a.* from dba_objects a,dba_objects b where rownum<100000;
99999 rows created.
SQL>insert into t select 5 ,a.* from dba_objects a,dba_objects b where rownum<1000000;
999999 rows created.
SQL>commit;
Commit complete. SQL>begin 2 dbms_stats.gather_table_stats(user, 3 't', 4 method_opt => 'for columns status size 5', 5 cascade => true); 6 7 end; 8 /
SQL>select id,count(*) from t group by id order by id;
ID COUNT(*) ---------- ---------- 1 9 2 999 3 9999 4 99999 5 999999 |
上面的代码精心构造了一个例子，表t上的id字段一共有5个唯一值，每个值的数量都不一样，id字段上有索引，分析了直方图。在这种情况下，如果我们直接使用字符变量不使用绑定变量的话，id在对1,2,3,4做查询的时候，都会使用索引扫描，这种情况下，索引扫描的成本要比全表扫描的成本低，id在对5做查询时，会使用全表扫描，这种情况下全表扫描的成本要比索引扫描成本低。如下表格，我是通过explain工具，使用文本变量后，得出的每个执行计划的cost，可以看到全表扫描的cost为2911，在查询id<5的情况下，由于索引扫描的cost都小于全表扫描的cost因此执行计划都选择了走索引扫描，只有在查询id等于5的时，才选择了走全表扫描。
|
ID |
执行计划 |
COST |
选择率 |
|
1 |
索引扫描 |
4 |
0.0000081 |
|
2 |
索引扫描 |
16 |
0.000899186 |
|
3 |
索引扫描 |
139 |
0.008999959 |
|
4 |
索引扫描 |
1370 |
0.090007696 |
|
5 |
索引扫描 |
13690 |
0. 900085058 |
|
5 |
全表扫描 |
2911 |
0. 900085058 |
上面的表格最后一列提供了谓词的选择率，此处选择率的计算公式为：
选择率=id=?的值在表中的数量/总数量
根据上面表格的cost我们可以知道，谓词的选择率在0.0000081到0.090007696之间都应该选择索引扫描，在0. 900085058的时候应该选择全表扫描，因为id在5的时候，索引扫描的成本13690已经远远大于了全表扫描的成本2911。我们看看下面的例子：
|
SQL>var a number; SQL>exec :a :=1;
PL/SQL procedure successfully completed.
SQL>select count(object_id) from t where id=:a;
COUNT(OBJECT_ID) ---------------- 9
SQL>exec :a :=5;
PL/SQL procedure successfully completed.
SQL>select count(object_id) from t where id=:a;
COUNT(OBJECT_ID) ---------------- 999999
SQL>select count(object_id) from t where id=:a;
COUNT(OBJECT_ID) ---------------- 999999
SQL>col PREDICATE for a10 SQL>-- 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='56g5zg95hcxc1' ORDER BY sql_id, child_number; 4
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ----------------- ------------ ---------- -------------------- -------------------- 56g5zg95hcxc1 1 =A 0 0.810076 0.990093 |
经过上面的一系列的操作后我们已经让这个cursor变得bind aware，如何让SQL变得bind aware我们上面已经论述过，这里不再做详细说明。经过这些步骤后，优化器已经产生出了一个child_number为1的新游标，这个游标基于绑定变量为5的值生成，谓词的选择率范围是：0.810076到0.990093。这个选择率跟我们上面表格里提供的选择率的关系是：（0.810076+0.990093）/2约等于我们上面表格里提供的选择率0. 900085058，Oracle为选择率稍微的预留了一些余地，这样很好。我们再执行id为1的查询看看：
|
SQL>exec :a :=1;
PL/SQL procedure successfully completed.
SQL>select count(object_id) from t where id=:a;
COUNT(OBJECT_ID) ---------------- 9
SQL>-- 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='56g5zg95hcxc1' ORDER BY sql_id, child_number;
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH -------------- ------------ ---------- ---------- -------------------- -------------------- 56g5zg95hcxc1 1 =A 0 0.810076 0.990093 56g5zg95hcxc1 2 =A 0 0.000007 0.000009 |
已经产生了child_number为2的子游标，是基于id为1的值产生的，选择率范围为：0.000007到0.000009。下面就到了本节关键的时刻了，我们再次查询id为4看看会出现什么情况。
|
SQL>exec :a :=4
PL/SQL procedure successfully completed.
SQL>select count(object_id) from t where id=:a;
COUNT(OBJECT_ID) ---------------- 99999
SQL> SQL>col PREDICATE for a10 SQL>-- 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='56g5zg95hcxc1' 4 ORDER BY sql_id, child_number;
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH --------------- ------------ ---------- ---------- -------------------- -------------------- 56g5zg95hcxc1 1 =A 0 0.810076 0.990093 56g5zg95hcxc1 2 =A 0 0.000007 0.000009 56g5zg95hcxc1 3 =A 0 0.000007 0.099008
SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive, 2 is_bind_aware,IS_SHAREABLE 3 FROM v$sql 4 WHERE sql_id='56g5zg95hcxc1';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- -- 0 2 13690 Y N N 1 1 13162 Y Y Y 2 1 4 Y Y N 3 1 1495 Y Y Y |
优化器已经重新生成了一个child_numer为3的子游标，同时选择率的范围已经扩大了，从0.000007到0.099008，也就是现在从id为1到4都被包含在child_number为3的子游标里了。child_number为2的子游标已经被标记为不能共享失效了，如果共享池有紧缺这块内存就可以被清除出去。那是不是意味着我们查询id为3的值时，将不用重新产生新游标，直接可以使用child_number为3的子游标了。我们来看看：
|
SQL>exec :a :=3
PL/SQL procedure successfully completed.
SQL>col PREDICATE for a10 SQL>-- 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='56g5zg95hcxc1' 4 ORDER BY sql_id, child_number;
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH --------------- ------------ ---------- ---------- -------------------- -------------------- 56g5zg95hcxc1 1 =A 0 0.810076 0.990093 56g5zg95hcxc1 2 =A 0 0.000007 0.000009 56g5zg95hcxc1 3 =A 0 0.000007 0.099008
SQL>select count(object_id) from t where id=:a;
COUNT(OBJECT_ID) ---------------- 9999
SQL>-- 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='56g5zg95hcxc1' 4 ORDER BY sql_id, child_number;
SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH -------------- ------------ ---------- ---------- -------------------- -------------------- 56g5zg95hcxc1 1 =A 0 0.810076 0.990093 56g5zg95hcxc1 2 =A 0 0.000007 0.000009 56g5zg95hcxc1 3 =A 0 0.000007 0.099008
SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive, 2 is_bind_aware,IS_SHAREABLE 3 FROM v$sql 4 WHERE sql_id='56g5zg95hcxc1';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- -- 0 2 13690 Y N N 1 1 13162 Y Y Y 2 1 4 Y Y N 3 2 1495 Y Y Y |
没有再生成新的子游标了，同时v$sql中的child_number为3的子游标的执行次数已经加1了。
从上面的示例我们可以知道，在v$sql_cs_histogram视图中，如果此游标的3个桶中出现了两个桶中的count都有非0值，那么此后的解析都要窥探绑定变量的值计算谓词选择率，如果计算选择率不在现有的游标的选择率范围内，就会基于窥探到的绑定变量的值重新硬解析产生一个新的游标，记录此游标的可以代表的选择率范围，当然就像我们例子看到的，新游标的执行计划可能跟之前是一样的，只不过是选择率的范围更广了。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/14.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
