绑定变量窥视是否开启和直方图关系

如果有直方图信息，而且绑定变量窥视也开启了，这里我们很好理解，在oracle 9i和oracle 10g时，sql第一次执行会将绑定变量的值带入到sql中，然后根据直方图等统计信息来评估合理的执行计划，在同样的sql下次执行时，会直接使用之前的执行计划，这个也就是我

如果有直方图信息，而且绑定变量窥视也开启了，这里我们很好理解，在oracle 9i和oracle 10g时，sql第一次执行会将绑定变量的值带入到sql中，然后根据直方图等统计信息来评估合理的执行计划，在同样的sql下次执行时，会直接使用之前的执行计划，这个也就是我们经常所接触的绑定变量窥视。

1） 开启了绑定变量窥视，收集该列的直方图：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> alter system set "_optim_peek_user_binds"=true;

System altered.

SQL> create table t002 as select * from dba_objects;

Table created.

SQL> update t002 set object_id=100 where rownum<50000;

49999 rows updated.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T002',met
hod_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='T002';

  NUM_ROWS     BLOCKS
---------- ----------
     50328        712

SQL> select num_distinct,density,num_nulls from user_tab_columns where table_nam
e='T002' and column_name='OBJECT_ID';

NUM_DISTINCT    DENSITY  NUM_NULLS
------------ ---------- ----------
          29 9.8243E-06          0

SQL> variable x number;
SQL> exec : x:=100;

PL/SQL procedure successfully completed.

SQL> select count(*) from t002 where object_id=:x;

  COUNT(*)
----------
     49999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  4yqsqnawx85ty, child number 0
-------------------------------------
select count(*) from t002 where object_id=:x

Plan hash value: 3014849763

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   158 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T002 | 50065 |   195K|   158   (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T002@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

30 rows selected.

SQL> exec : x:=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from t002 where object_id=:x;

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

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------- ----------
SQL_ID 4yqsqnawx85ty，子编号 0
--------------------------------------------------
从 t002 中选择 count(*)，其中 object_id=:x

计划哈希值：3014849763

-------------------------------------------------- --------------------------
|身份证号|操作 |        |名称 |行 |字节 |成本（%CPU）|时间     |
-------------------------------------------------- --------------------------
|   0 |选择语句      |       |       |   158（100）|          |
|   1 |  对聚合进行排序 |      |     1 |     4 |            |          |
|*  2 |   表访问已满| T002 | 50065|   195K|   158（1）| 00:00:02 |
-------------------------------------------------- --------------------------

查询块名称/对象别名（通过操作id标识）：
-------------------------------------------------- -----------

   1 - SEL$1
   2 - SEL$1 / T002@SEL$1

谓词信息（通过操作id标识）：
-------------------------------------------------- -
   2 - 过滤器（“OBJECT_ID”=：X）

列投影信息（通过操作id识别）：
-------------------------------------------------- ---------

   1 - (#keys=0) COUNT(*)[22]

已选择 30 行。

这里比较好理解，由于开启了绑定指针窥视，该列还有直方图信息，所以第一次执行时要取具体值带，然后根据具体值的直方图信息来评估行，这里的通过谓词过滤后提示返回的行是50065，关于有直方图的情况下提示行的方式，非常复杂，小鱼自己也没有过多的深究，后面有机会整理相应的文章来分享。

2）如果开启了绑定手表，但没有收集直方图：
SQL>更改系统设置“_optim_peek_user_binds”=true;

系统已更改。

SQL>执行 dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T002',met
hod_opt=>'对于所有列大小为 1');

PL/SQL 过程成功完成。

SQL>变量y号;
SQL>exec : y:=100

PL/SQL 过程成功完成

SQL>从 t002 中选择 count(*)，其中 object_id=:y;

  计数(*)
----------
     49999

SQL>从表中选择 *(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
-------------------------------------------------- ------------------------

SQL_ID 86ngbvm962n14，子编号 0
--------------------------------------------------
从 t002 中选择 count(*)，其中 object_id=:y

计划哈希值：3014849763

-------------------------------------------------- ------------------------
|身份证号|操作 |        |名称 |行 |字节 |成本（%CPU）|时间
-------------------------------------------------- ------------------------
|   0 |选择语句      |       |       |   158 (100)|
|   1 |  对聚合进行排序 |      |     1 |     4 |            |
|*  2 |   表访问已满| T002 |  1290 | 1290  5160 | 5160   158（1）| 00:00:02
-------------------------------------------------- ------------------------

查询块名称/对象别名（通过操作id标识）：
-------------------------------------------------- -----------

   1 - SEL$1
   2 - SEL$1 / T002@SEL$1

谓词信息（通过操作id标识）：
-------------------------------------------------- -

   2 - 过滤器（“OBJECT_ID”=：Y）

列投影信息（通过操作id识别）：
-------------------------------------------------- ---------

   1 - (#keys=0) COUNT(*)[22]

已选择 30 行。

这里我们来算算第 1290 行是如何提示出来的

这里介绍最简单的如何计算行，选择性、密度的公式：（以下计算公式在该列没有直方图前提下）

小鱼之前介绍cbo优化器的基本知识里面提过一个选择性选择率这个概念

可选择率selectivity=释放指定谓词条件返回结果集的记录数/未施加任何谓词条件的原始结果集的记录数，可选择率越大，那么cbo估算返回的rows也越大。

那么集的势rows=selectivity*未施加任何谓词条件的原始结果集的记录数

那么这个可选择率selectivity如何计算了，在列的统计信息中num_nulls为0时，selectivity=1/num_distinct

SQL> select num_distinct,num_nulls,density,num_buckets from dba_tab_columns wher
e table_name='T002' and column_name='OBJECT_ID';

NUM_DISTINCT  NUM_NULLS    DENSITY NUM_BUCKETS
------------ ---------- ---------- -----------
          39          0 .025641026           1

SQL> select num_rows from user_tables where table_name='T002';

  NUM_ROWS
----------
     50328

SQL> select 1/39 from dual;

      1/39
----------
.025641026

SQL> select 50328*1/39 from dual;

50328*1/39
----------
1290.46154

这里我们通过统计信息发现计算而来的1290跟执行计划的rows 1290完全一致

列没有直方图，而且num_nulls为0时:
Selectivity_without_null=(1/num_distinct),也就是列的统计信息中num_nulls为0时，列的选择率是1/num_distinct，此时density也是等于1/num_distinct

列没有直方图，但是num_nulls又不为0时:
selectivity_with_null=(1/num_distinct)*(num_rows-num_nulls)/(num_rows)，而density还是等于1/num_distinct

对于第一点num_nulls为0,列没有直方图，selectivity选择率和density上面已经进行了验证，下面稍微扩展点，来验证下num_nulls不为0，没有直方图时，可选择率selectivity、rows和density关系

SQL> update t002 set object_id=null where rownum<1000;
SQL> commit;
SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T002',method_opt=>'for all columns size 1');

SQL> select NUM_ROWS, --表中的记录数
  2  BLOCKS, --表中数据所占的数据块数
  3  EMPTY_BLOCKS, --表中的空块数
  4  AVG_SPACE, --数据块中平均的使用空间
  5  CHAIN_CNT, --表中行连接和行迁移的数量
  6  AVG_ROW_LEN --每条记录的平均长度
  7  from dba_tables
  8  where owner=&owner and table_name=&tabname;
Enter value for owner: 'XIAOYU'
Enter value for tabname: 'T002'
old   8: where owner=&owner and table_name=&tabname
new   8: where owner='XIAOYU' and table_name='T002'

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
     50328        712            0          0          0          91

SQL> SELECT column_name,
  2         num_distinct,
  3         num_nulls,
  4         density,
  5         num_buckets,
  6         low_value,
  7         high_value
  8    FROM dba_tab_col_statistics
  9   WHERE     owner = &owner
 10         AND table_name = &tabname
 11         AND column_name IN (&col1);
Enter value for owner: 'XIAOYU'
old   9:  WHERE     owner = &owner
new   9:  WHERE     owner = 'XIAOYU'
Enter value for tabname: 'T002'
old  10:        AND table_name = &tabname
new  10:        AND table_name = 'T002'
Enter value for col1: 'OBJECT_ID'
old  11:        AND column_name IN (&col1)
new  11:        AND column_name IN ('OBJECT_ID')

COLUMN_NAME              NUM_DISTINCT  NUM_NULLS    DENSITY    NUM_BUCKETS LOW_VALUE                      HIGH_VALUE
------------------------------ ------------ ---------- ---------- ----------- --
---------------------------- ------------------------------
OBJECT_ID                         44           943            .022727273         1 C2
02                           C3064A3F

SQL>从 t002 中选择 count(*)，其中 object_Id=100;

  计数(*)
----------
     49000

SQL>从表中选择 *(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
-------------------------------------------------- ------------------------------------------
-------------------------------------------------- ------------------------------------------
-------------------------------------------------- ------------------------------------------
-------------------------------------------------- ----------
SQL_ID 3yaw02xfsf7c8，子编号 0
--------------------------------------------------
从 t002 中选择 count(*)，其中 object_Id=100

计划哈希值：3014849763

-------------------------------------------------- --------------------------
|身份证号|操作 |        |名称 |行 |字节 |成本（%CPU）|时间     |
-------------------------------------------------- --------------------------
|   0 |选择语句      |       |       |   158（100）|          |
|   1 |  对聚合进行排序 |      |     1 |     3 |            |          |
|*  2 |   表访问已满| T002 |  1122 | 1122  3366|   158（1）| 00:00:02 |
-------------------------------------------------- --------------------------

谓词信息（通过操作id标识）：
-------------------------------------------------- -

   2 - 过滤器（“OBJECT_ID”=100）


已选择 19 行。

来算算选择率selectivity_with_null=(1/num_distinct)*(num_rows-num_nulls)/(num_rows)
 
SQL>从对偶中选择(50328-943)/50328*1/44;

(50328-943)/50328*1/44
----------------------
             .02230143

算算密度=1/num_distinct也和dba_tab_columns中值一样

SQL>从对偶中选择 1/44；

      1/44
----------
.022727273

根据选择率selectivity_with_null跟执行的股票的行同样符合计划的，这个地方要注意rows是严格根据num_rows*选择性的，而不是num_rows*密度，在因为没直方图时密度计算方式始终是1/num_distinct

SQL>从对偶中选择 0.02230143* 50328;

0.02230143*50328
----------------
      1122.38637

我们发觉上面的注意力点计算选择性的方式可以直接简化为：
（1/num_distinct）*(num_rows-num_nulls)/num_rows,如果num_null为0，此时（1/num_distinct）*(num_rows-num_nulls)/num_rows就直接等于1/num_distinct

3）关闭绑定手表，也不收集直方图：
SQL>创建表 t003 作为 select * from dba_objects;

表已创建。

SQL>更改系统设置“_optim_peek_user_binds”= false;

系统已更改。

SQL>更新 t003 set object_id=10000 where object_id
已更新 48524 行。

SQL>提交；

提交完成。

SQL>执行 dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T003',method
_opt=>'对于所有列大小为 1');

PL/SQL 过程成功完成。

SQL>从 user_tables 中选择 num_rows where table_name='T003';

  NUM_ROWS
----------
     50325

SQL>从 user_tab_columns 中选择 num_distinct、密度、num_nulls，其中 table_nam
e='T003' 和 column_name='OBJECT_ID';

NUM_DISTINCT    密度  NUM_NULLS
------------ ---------- ----------
         184 .005434783          0

SQL>可变数字；
SQL>执行：a:=10000;

PL/SQL 过程成功完成。

SQL>从 t003 中选择 count(object_name)，其中 object_id=:a;

COUNT（OBJECT_NAME）
------------------
             48524

SQL>从表中选择 *(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
-------------------------------------------------- ------------------------------------------
-------------------------------------------------- ----------
SQL_ID dq92pjhyfrg1n，子编号 0
--------------------------------------------------
从 t003 中选择 count(object_name)，其中 object_id=:a

计划哈希值：3872854764

-------------------------------------------------- --------------------------
|身份证号|操作 |        |名称 |行 |字节 |成本（%CPU）|时间     |
-------------------------------------------------- --------------------------
|   0 |选择语句      |       |       |   154（100）|          |
|   1 |  对聚合进行排序 |      |     1 |    29 | 29            |          |
|*  2 |   表访问已满| T003 |   274 | 274  7946 |   154（1）| 00:00:02 |
-------------------------------------------------- --------------------------

查询块名称/对象别名（通过操作id标识）：
-------------------------------------------------- -----------

   1 - SEL$1
   2 - SEL$1 / T003@SEL$1

谓词信息（通过操作id标识）：
-------------------------------------------------- -
   2 - 过滤器（“OBJECT_ID”=：A）

列投影信息（通过操作id识别）：
-------------------------------------------------- ---------

   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - “OBJECT_NAME”[VARCHAR2,128]

已选择 31 行。

SQL>执行：a:=10;

PL/SQL 过程成功完成。

SQL>从 t003 中选择 count(object_name)，其中 object_id=:a;

COUNT（OBJECT_NAME）
------------------
                 0

SQL>从表中选择 *(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
-------------------------------------------------- ------------------------------------------
-------------------------------------------------- ----------
SQL_ID dq92pjhyfrg1n，子编号 0
--------------------------------------------------
从 t003 中选择 count(object_name)，其中 object_id=:a

计划哈希值：3872854764

-------------------------------------------------- --------------------------
|身份证号|操作 |        |名称 |行 |字节 |成本（%CPU）|时间     |
-------------------------------------------------- --------------------------
|   0 |选择语句      |       |       |   154（100）|          |
|   1 |  对聚合进行排序 |      |     1 |    29 | 29            |          |
|*  2 |   表访问已满| T003 |   274 | 274  7946 |   154（1）| 00:00:02 |
-------------------------------------------------- --------------------------

查询块名称/对象别名（通过操作id标识）：
-------------------------------------------------- -----------

   1 - SEL$1
   2 - SEL$1 / T003@SEL$1

谓词信息（通过操作id标识）：
-------------------------------------------------- -
   2 - 过滤器（“OBJECT_ID”=：A）

列投影信息（通过操作id识别）：
-------------------------------------------------- ---------

   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - “OBJECT_NAME”[VARCHAR2,128]

已选择 31 行。

关闭绑定变量，没有直方图时:
选择率selectivity=1/num_distinct*(num_rows-num_nulls)/num_rows

SQL>从对偶中选择 50325*1/184;

50325*1/184
-----------
 273.505435

SQL>从对偶中选择 50325*0.005434783;

50325*0.005434783
-----------------
       273.505454

特别注意点：
而且我们查看v$sql时发现，关闭绑定变量窥视并不是不共享sql，而是说sql第一次执行时不带入具体值，这个如果大家没有测试过可能会想当然的以为是关闭绑定变量窥视是不共享绑定的sql语句，其实绑定变量窥视真正含义是sql第一次执行时带入绑定具体值，而如果关闭了绑定变量窥视，则不会带入具体值，那么由于不带入具体值，直方图也不会影响selectivity计算
 
SQL> select child_number,sql_id,sql_text from v$sql where sql_text like 'select
count(object_name) from t003 where object_id=:a%';

CHILD_NUMBER SQL_ID
------------ -------------
SQL_TEXT
--------------------------------------------------------------------------------
------------------------------------------------------------
           0 dq92pjhyfrg1n
select count(object_name) from t003 where object_id=:a

4）关闭绑定变量窥视，收集直方图：
SQL> alter system set "_optim_peek_user_binds"=false;

System altered.

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T003',method
_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='T003';

  NUM_ROWS
----------
     50325

SQL> select num_distinct,density,num_nulls from user_tab_columns where table_nam
e='T003' and column_name='OBJECT_ID';

NUM_DISTINCT    DENSITY  NUM_NULLS
------------ ---------- ----------
         197 .000010034          0

SQL> variable b number;
SQL> exec : b:=10000;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from t003 where object_id=:b;

COUNT(OBJECT_NAME)
------------------
             48524

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  9qunh3ms4kjzw, child number 0
-------------------------------------
select count(object_name) from t003 where object_id=:b

Plan hash value: 3872854764

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   154 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    29 |            |          |
|*  2 |   TABLE ACCESS FULL| T003 |   255 |  7395 |   154   (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T003@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=:B)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - "OBJECT_NAME"[VARCHAR2,128]

31 rows selected.

SQL> exec : b:=10;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from t003 where object_id=:b;

COUNT(OBJECT_NAME)
------------------
                 0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  9qunh3ms4kjzw, child number 0
-------------------------------------
select count(object_name) from t003 where object_id=:b

计划哈希值：3872854764

-------------------------------------------------- --------------------------
|身份证号|操作 |        |名称 |行 |字节 |成本（%CPU）|时间     |
-------------------------------------------------- --------------------------
|   0 |选择语句      |       |       |   154（100）|          |
|   1 |  对聚合进行排序 |      |     1 |    29 | 29            |          |
|*  2 |   表访问已满| T003 |   255 | 255  7395 |   154（1）| 00:00:02 |
-------------------------------------------------- --------------------------

查询块名称/对象别名（通过操作id标识）：
-------------------------------------------------- -----------

   1 - SEL$1
   2 - SEL$1 / T003@SEL$1

谓词信息（通过操作id标识）：
-------------------------------------------------- -

   2 - 过滤器（“OBJECT_ID”=：B）

列投影信息（通过操作id识别）：
-------------------------------------------------- ---------

   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - “OBJECT_NAME”[VARCHAR2,128]

已选择 31 行。

如果有直方图，但是关闭绑定变量窥视，由于无法把绑定变量的值带入sql语句中，此时选择性计算方式还是1/num_distinct*(num_rows-num_nulls)/num_rows
SQL>从对偶中选择 50325*1/197;

50325*1/197
-----------
 255.456853

在关闭绑定变量查询后，sql语句还是会软解析，只是绑定变量的sql语句执行时第一次无法带入到sql语句中，选择性计算无法应用到直方图信息，所以此时无直方图中对于此类绑定变量的sql语句没有影响。

关于有直方图时，字段的选择、密度等如何计算，小鱼后续会整理文档来分享。

原文地址：绑定手表是否开启和直方图关系，感谢原作者分享。