直方图对cbo的影响

摘自《基于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

我们看到是生成了一个父游标下面有3个子游标