直方图对cbo的影响

原创

aoerqilengbai 2021-09-08 09:17:35 博主文章分类：sql优化 ©著作权

文章标签 sql 直方图 oracle 执行计划 d3 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者aoerqilengbai的原创作品，请联系作者获取转载授权，否则将追究法律责任

摘自《基于oracle的sql优化》

当某个列上有直方图后，会认为对该目标列施加的等值查询条件是一个不安全的谓词条件，不安全的含义就是相关sql的执行计划会随着对目标列传入值的不同而不同，正常情况下这样是正常的，因为有了直方图，该列本省就是不均衡的了，如果目标sql的执行计划不随谓词条件传入值的不同而不同，则cbo就认为该谓词条件是一个安全的谓词条件。

这种不安全的谓词条件有个问题，在cursor_sharing设成similar后，本是想在不修改sql的前提下，让oracle用系统产生的绑定变量来替换该sql里where条件中针对目标列传入的具体值，以达到尽可能重用shared curosr中存储的执行计划，但如果列上有直方图信息，此时oracle对每个输入distinct值都产生一个child cursor,实际上cursor_sharing就没有起作用。

SQL> create table t(id1 varchar2(10),id2 varchar2(10),id3 varchar2(10));

表已创建。

SQL> create index idx_t_id1 on t(id1);

索引已创建。

SQL> declare
2 i number;
3 begin
4 for i in 1..20 loop
5 insert into t values('A','B','C');
6 end loop;
7 commit;
8 for i in 1..10000 loop
9 insert into t values('D','E','F');
10 end loop;
11 commit;
12 for i in 1..10000 loop
13 insert into t values('G','H','I');
14 end loop;
15 commit;
16 end;
17 /

PL/SQL 过程已成功完成。

SQL> select id1,count(*) from t group by id1;

ID1 COUNT(*)
-------------------- ----------
D 10000
A 20
G 10000

SQL> select id2,count(*) from t group by id2;

ID2 COUNT(*)
-------------------- ----------
H 10000
B 20
E 10000

SQL> show parameter cursor_sharing

NAME TYPE
------------------------------------ ----------------
VALUE
------------------------------
cursor_sharing string
EXACT

alter session set cursor_sharing='SIMILAR';

SQL> select /*+ dynamic_sampling(t 0) */ count(*) from t where id1='A';

COUNT(*)
----------
20

SQL> select /*+ dynamic_sampling(t 0) */ count(*) from t where id1='B';

COUNT(*)
----------
0

SQL> select /*+ dynamic_sampling(t 0) */ count(*) from t where id1='C';

COUNT(*)
----------
0

SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%dynamic%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID VERSION_COUNT
-------------------------- -------------
select /*+ dynamic_sampling(t 0) */ count(*) from t where id1=:"SYS_B_0"
3rxau47dk57tv 1

我们看到cusor_sharing=similar确实是将3条sql看为1条sql了，要禁止动态采样，否则动态采样的时候会收集直方图信息，如在用动态采用的情况下，结果如下：

SQL> select /*+ xxx */ count(*) from t where id1='A';

COUNT(*)
----------
20

SQL> select /*+ xxx */ count(*) from t where id1='B';

COUNT(*)
----------
0

SQL> select /*+ xxx */ count(*) from t where id1='C';

COUNT(*)
----------
0

SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%xxx%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID VERSION_COUNT
-------------------------- -------------
select /*+ xxx */ count(*) from t where id1=:"SYS_B_0"
97y4sjq246659 3