抓出必须创建直方图的列

前面提到，当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。

如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。

下面我们还是以测试表 test 为例，用实验讲解直方图。

首先我们对测试表 test 收集统计信息，在收集统计信息的时候，不收集列的直方图，语句for all columns size 1表示对所有列都不收集直方图。

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                 tabname          => 'TEST',
                                 estimate_percent => 100,
                                   method_opt      => 'for all columns size 1',
                                  no_invalidate    => FALSE,
                                  degree           => 1,
                                  cascade          => TRUE);
 END;

PL/SQL procedure successfully completed.

Histogram 为 none 表示没有收集直方图。

select a.column_name,
        b.num_rows,
         a.num_distinct Cardinality,
         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
         a.histogram,
        a.num_buckets
    from dba_tab_col_statistics a, dba_tables b
   where a.owner = b.owner
     and a.table_name = b.table_name
    and a.owner = 'SCOTT'
     and a.table_name = 'TEST';

COLUMN_NAME       NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ----------- ----------- --------- -----------
OWNER                72462          29         .04 NONE                1
OBJECT_NAME          72462       44236       61.05 NONE                1
SUBOBJECT_NAME       72462         106         .15 NONE                1
OBJECT_ID            72462       72462         100 NONE                1
DATA_OBJECT_ID       72462        7608        10.5 NONE                1
OBJECT_TYPE          72462          44         .06 NONE                1
CREATED              72462        1366        1.89 NONE                1
LAST_DDL_TIME        72462        1412        1.95 NONE                1
TIMESTAMP            72462        1480        2.04 NONE                1
STATUS               72462           1           0 NONE                1
TEMPORARY            72462           2           0 NONE                1
GENERATED            72462           2           0 NONE                1
SECONDARY            72462           2           0 NONE                1
NAMESPACE            72462          21         .03 NONE                1
EDITION_NAME         72462           0           0 NONE                0
15 rows selected.

owner 列基数很低，现在我们对 owner 列进行查询。

set autot trace
select * from test where owner='SCOTT';

7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|  0  | SELECT STATEMENT  |      |  2499 |   236K|   289   (1)| 00:00:04 |
|* 1  |  TABLE ACCESS FULL| TEST |  2499 |   236K|   289   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SCOTT')

请注意看粗体字部分，查询 owner＝’SCOTT『返回了 7 条数据，但是 CBO 在计算Rows的时候认为owner='SCOTT'返回2 499条数据，Rows 估算得不是特别准确。从 72 462 条数据里面查询出 7 条数据，应该走索引，所以现在我们对 owner 列创建索引。

create index idx_owner on test(owner);

Index created.

我们再来查询一下。

select * from test where owner='SCOTT';

7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation                    |Name     | Rows  | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |  2499 |  236K |   73   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID |TEST     |  2499 |  236K |   73   (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN            |IDX_OWNER|  2499 |       |    6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SCOTT')

现在我们查询 owner='SYS'。

select * from test where owner='SYS';

30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation                   | Name     | Rows  | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |          |  2499 |   236K|   73   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID| TEST     |  2499 |   236K|   73   (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN           | IDX_OWNER|  2499 |       |    6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')

注意粗字体部分，查询owner='SYS'返回了 30 808 条数据。从 72 462 条数据里面返回 30 808 条数据能走索引吗？很明显应该走全表扫描。也就是说该执行计划是错误的。

为什么查询owner='SYS'的执行计划会用错呢？因为 owner 这个列基数很低，只有 29，而表的总行数是 72 462。前文着重强调过，当列没有收集直方图统计信息的时候，CBO 会认为该列数据分布是均衡的。正是因为 CBO 认为 owner 列数据分布是均衡的，不管 owner 等于任何值，CBO 估算的 Rows 永远都是2 499。而这2 499是怎么来的呢？答案如下。

select round(72462/29) from dual;

round(72462/29)
--------------
          2499

现在大家也知道了，执行计划里面的Rows 是假的。执行计划中的 Rows 是根据统计信息以及一些数学公式计算出来的。很多 DBA 到现在还不知道执行计划中 Rows 是假的这个真相，真是令人遗憾。

在做SQL 优化的时候，经常需要做的工作就是帮助CBO计算出比较准确的Rows。注意：我们说的是比较准确的 Rows。CBO 是无法得到精确的 Rows 的，因为对表收集统计信息的时候，统计信息一般都不会按照 100% 的标准采样收集，即使按照 100% 的标准采样收集了表的统计信息，表中的数据也随时在发生变更。另外计算 Rows 的数学公式目前也是有缺陷的，CBO 永远不可能计算得到精确的 Rows。

如果CBO 每次都能计算得到精确的Rows，那么相信我们这个时候只需要关心业务逻辑、表设计、SQL 写法以及如何建立索引了，再也不用担心SQL 会走错执行计划了。

Oracle12c 的新功能 SQL Plan Directives 在一定程度上解决了 Rows 估算不准而引发的 SQL 性能问题。关于 SQL Plan Directives，本书不做过多讨论。

为了让 CBO 选择正确的执行计划，我们需要对 owner 列收集直方图信息，从而告知 CBO 该列数据分布不均衡，让 CBO 在计算 Rows 的时候参考直方图统计。现在我们对 owner 列收集直方图。

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',
                                  tabname          => 'TEST',
                                  estimate_percent => 100,
                                  method_opt       => 'for columns owner size skewonly',
                                  no_invalidate    => FALSE,
                                  degree           => 1,
                                  cascade          => TRUE);
END;

PL/SQL procedure successfully completed.

查看一下 owner 列的直方图信息。

select a.column_name,
         b.num_rows,
         a.num_distinct Cardinality,
         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
         a.histogram,
         a.num_buckets
    from dba_tab_col_statistics a, dba_tables b
   where a.owner = b.owner
      and a.table_name = b.table_name
     and a.owner = 'SCOTT'
     and a.table_name = 'TEST';

COLUMN_NAME       NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM  NUM_BUCKETS
--------------- ---------- ----------- ----------- ---------- -----------
OWNER                72462          29         .04 FREQUENCY           29
OBJECT_NAME          72462       44236       61.05 NONE                 1
SUBOBJECT_NAME       72462         106         .15 NONE                 1
OBJECT_ID            72462       72462         100 NONE                 1
DATA_OBJECT_ID       72462        7608        10.5 NONE                 1
OBJECT_TYPE          72462          44         .06 NONE                 1
CREATED              72462        1366        1.89 NONE                 1
LAST_DDL_TIME        72462        1412        1.95 NONE                 1
TIMESTAMP            72462        1480        2.04 NONE                 1
STATUS               72462           1           0 NONE                 1
TEMPORARY            72462           2           0 NONE                 1
GENERATED            72462           2           0 NONE                 1
SECONDARY            72462           2           0 NONE                 1
NAMESPACE            72462          21         .03 NONE                 1
EDITION_NAME         72462           0           0 NONE                 0
15 rows selected.

现在我们再来查询上面的 SQL，看执行计划是否还会走错并且验证 Rows 是否还会算错。

select * from test where owner='SCOTT';


7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id  |Operation                  | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |          |    7 |   679 |     2   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID| TEST     |    7 |   679 |     2   (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN           | IDX_OWNER|    7 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SCOTT')
SQL> select * from test where owner='SYS';
30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 30808 |  2918K|   290   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST | 30808 |  2918K|   290   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')

对 owner 列收集完直方图之后，CBO 估算的 Rows 就基本准确了，一旦 Rows 估算对了，那么执行计划也就不会出错了。

大家是不是很好奇，为什么收集完直方图之后，Rows 计算得那么精确，收集直方图究竟完成了什么操作呢？对 owner 列收集直方图其实就相当于运行了以下 SQL。

select owner,count(*) from test group by owner;

直方图信息就是以上 SQL 的查询结果，这些查询结果会保存在数据字典中。这样当我们查询 owner 为任意值的时候，CBO 总会算出正确的 Rows，因为直方图已经知道每个值有多少行数据。

如果 SQL 使用了绑定变量，绑定变量的列收集了直方图，那么该 SQL 就会引起绑定变量窥探。绑定变量窥探是一个老生常谈的问题，这里不多做讨论。Oracle11g 引入了自适应游标共享（Adaptive Cursor Sharing），基本上解决了绑定变量窥探问题，但是自适应游标共享也会引起一些新问题，对此也不做过多讨论。

当我们遇到一个 SQL 有绑定变量怎么办？其实很简单，我们只需要运行以下语句。

select 列， count(*) from test group by 列 order by 2 desc；

如果列数据分布均衡，基本上 SQL 不会出现问题；如果列数据分布不均衡，我们需要对列收集直方图统计。

关于直方图，其实还有非常多的话题，比如直方图的种类、直方图的桶数等，本书在此不做过多讨论。在我们看来，读者只需要知道直方图是用来帮助CBO 在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows就够了。

什么样的列需要收集直方图呢？当列出现在 where 条件中，列的选择性小于 1% 并且该列没有收集过直方图，这样的列就应该收集直方图。注意：千万不能对没有出现在 where 条件中的列收集直方图。对没有出现在 where 条件中的列收集直方图完全是做无用功，浪费数据库资源。

下面我们为大家分享本书第二个全自动化优化脚本。

抓出必须创建直方图的列

（大家可以对该脚本进行适当修改，以便用于生产环境）。

select a.owner,
        a.table_name,
        a.column_name,
         b.num_rows,
         a.num_distinct,
        trunc(num_distinct / num_rows * 100,2) selectivity,
         'Need Gather Histogram' notice
    from dba_tab_col_statistics a, dba_tables b
   where a.owner = 'SCOTT'
     and a.table_name = 'TEST'
      and a.owner = b.owner
     and a.table_name = b.table_name
    and num_distinct / num_rows<0.01
      and (a.owner, a.table_name, a.column_name) in
         (select r.name owner, o.name table_name, c.name column_name
            from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
          where o.obj# = u.obj#
            and c.obj# = u.obj#
             and c.col# = u.intcol#
             and r.name = 'SCOTT'
             and o.name = 'TEST')
     and a.histogram ='NONE';

OWNER TABLE COLUM   NUM_ROWS NUM_DISTINCT SELECTIVITY NOTICE
----- ----- ----- ---------- ------------ ----------- ----------------------
SCOTT TEST  OWNER      72462           29         .04 Need Gather Histogram

SQL优化入门

直方图（HISTOGRAM）

抓出必须创建直方图的列