1.3 直方图（HISTOGRAM）
前面提到，当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。
如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。
下面我们还是以测试表test为例，用实验讲解直方图。
首先我们对测试表test收集统计信息，在收集统计信息的时候，不收集列的直方图，语句for all columns size 1表示对所有列都不收集直方图。
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 tabname => 'TEST', 4 estimate_percent => 100, 5 method_opt => 'for all columns size 1', 6 no_invalidate => FALSE, 7 degree => 1, 8 cascade => TRUE); 9 END; 10 / PL/SQL procedure successfully completed.
Histogram为none表示没有收集直方图。
SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 a.histogram, 6 a.num_buckets 7 from dba_tab_col_statistics a, dba_tables b 8 where a.owner = b.owner 9 and a.table_name = b.table_name 10 and a.owner = 'SCOTT' 11 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列进行查询。
SQL> set autot trace SQL> 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列创建索引。
SQL> create index idx_owner on test(owner); Index created. ``` 我们再来查询一下。 ``` SQL> 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'`。
SQL> 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是怎么来的呢？答案如下。
SQL> 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列收集直方图。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 100,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
查看一下owner列的直方图信息。
SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 a.histogram, 6 a.num_buckets 7 from dba_tab_col_statistics a, dba_tables b 8 where a.owner = b.owner 9 and a.table_name = b.table_name 10 and a.owner = 'SCOTT' 11 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是否还会算错。
SQL> 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条件中的列收集直方图完全是做无用功，浪费数据库资源。
下面我们为大家分享本书第二个全自动化优化脚本。
抓出必须创建直方图的列（大家可以对该脚本进行适当修改，以便用于生产环境）。
SQL> select a.owner, 2 a.table_name, 3 a.column_name, 4 b.num_rows, 5 a.num_distinct, 6 trunc(num_distinct / num_rows * 100,2) selectivity, 7 'Need Gather Histogram' notice 8 from dba_tab_col_statistics a, dba_tables b 9 where a.owner = 'SCOTT' 10 and a.table_name = 'TEST' 11 and a.owner = b.owner 12 and a.table_name = b.table_name 13 and num_distinct / num_rows<0.01 14 and (a.owner, a.table_name, a.column_name) in 15 (select r.name owner, o.name table_name, c.name column_name 16 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 17 where o.obj# = u.obj# 18 and c.obj# = u.obj# 19 and c.col# = u.intcol# 20 and r.name = 'SCOTT' 21 and o.name = 'TEST') 22 and a.histogram ='NONE'; OWNER TABLE COLUM NUM_ROWS NUM_DISTINCT SELECTIVITY NOTICE ----- ----- ----- ---------- ------------ ----------- ---------------------- SCOTT TEST OWNER 72462 29 .04 Need Gather Histogram