1 一条待优化的SQL语句
要优化的SQL语句是一条简单的select语句，这条语句及它的执行计划如下：
select * from sh.customer_eur WHERE country_id = 52788;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3148057754
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3821 | 723K| 240 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMER_EUR | 3821 | 723K| 240 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("COUNTRY_ID"=52788)
这条语句走了全表扫描，执行计划估算的根据条件过滤出的行是3821行，COUNTRY_ID列上是有索引的
SELECT * FROM user_ind_columns WHERE INDEX_NAME = 'IDX_CE_C';
INDEX_NAME|TABLE_NAME |COLUMN_NAME|COLUMN_POSITION|COLUMN_LENGTH|CHAR_LENGTH|DESCEND|COLLATED_COLUMN_ID|
----------+------------+-----------+---------------+-------------+-----------+-------+------------------+
IDX_CE_C |CUSTOMER_EUR|COUNTRY_ID | 1| 22| 0|ASC | |
语句查询的CUSTOMER_EUR表及语句的条件选择的数据量如下：
select count(*) from sh.customer_eur WHERE country_id = 52788;
COUNT(*)
----------
91
select count(*) from sh.customer_eur;
COUNT(*)
----------
30563
CUSTOMER_EUR表总共有30563条数据，根据语句的条件筛选出的数据有81条。在这种情况下，选择索引范围扫描明显是更好的选择，Oracle优化器却选择了全表扫描。
2 为什么Oracle优化器会选择全表扫描而不是性能更好的索引范围扫描
Oracle优化器在选择访问路径时，对语句where条件返回的行数估算是3821，这和实际返回的行数91相差太远，因而选择了次优化的访问路径。3821这个值是共哪儿来的呢，看一下列COUNTRY_ID上索引的统计信息
SELECT INDEX_NAME,TABLE_NAME,LEAF_BLOCKS,CLUSTERING_FACTOR, NUM_ROWS,DISTINCT_KEYS,LAST_ANALYZED FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_CE_C';
INDEX_NAME|TABLE_NAME |LEAF_BLOCKS|CLUSTERING_FACTOR|NUM_ROWS|DISTINCT_KEYS|LAST_ANALYZED |
----------+------------+-----------+-----------------+--------+-------------+-----------------------+
IDX_CE_C |CUSTOMER_EUR| 69| 4910| 30563| 8|2024-11-26 02:45:30.000|
索引IDX_CE_C的的NUM_ROWS是30563，DISTINCT_KEYS的数目是8个，这两个数相除就是Oracle优化器对筛选出来的而数据条目的估值。再看一下这个索引的不同的键值的行数的统计
select country_id,count(*) from sh.customer_eur group by country_id
COUNTRY_ID COUNT(*)
---------- ----------
52770 7779
52776 8173
52777 383
52778 2039
52779 3833
52786 708
52788 91
52789 7557
不同的键值对应的行数差别很大，最少的91条，最多的8000多条，COUNTRY_ID列上的数据分布是不均匀的，优化器估算的结果同真实情况差别很大，所以得出了次优化的执行计划。解决这个问题的工具就是直方图。
3 直方图简介
在Oracle数据库中，直方图是一种特殊类型的列统计信息，它提供关于表的一个列的更详细的数据分布的信息。直方图将值排序并存放于一系列的桶（bucket）中（sorts values in to “buckets”。
在默认情况下，Oracle优化器假设一个列中不同的值在不同的行中是均匀分布的。如果一个列的值的分布是倾斜的（非均匀分布），这个假设会导致优化器选择错误的访问路径，执行次优化的执行计划。直方图就是解决这个问题的技术，因为总是会遇到列的值在数据行中不均匀分布的情况。
直方图一共有四种：
frequency histogram—每一个不同的键值对应一个单独的桶，因为每个键值都有用自己专用的桶，直方图中的每个桶中值的数量可能差别很大。
Top Frequency Histograms–是前一种直方图的变体，这种直方图忽略不流行的值（统计上不那么重要的值）。官方举的例子是如果一堆硬币中只有1枚是1美分的，我们就可以忽略这个1美分的硬币。如果一小部分值占据了表的大多数行，在这小部分值上创建frequency histogram就非常有用了，不同的值的数量远远大于桶的数量时尤其如此。
legacy height-balanced histogram–值被接近均匀的分布到桶中，每个桶的行数十分接近，这是传统的方式。
hybrid histogram–组合了frequency histogram和height-balanced histogram的特点。
4 生成直方图
生成直方图前先检查以下查询的CUSTOMER_EUR表的COUNTRY_ID列上是否已经生成过直方图，使用下面语句查询
SELECT TABLE_NAME,COLUMN_NAME,NOTES,HISTOGRAM FROM user_TAB_COL_STATISTICS WHERE TABLE_NAME ='CUSTOMER_EUR'
and COLUMN_NAME='COUNTRY_ID';
CUSTOMER_EUR|COUNTRY_ID |STATS_ON_LOAD |NONE |
列上还没有生成直方图信息，用DBMS_STATS包生成直方图信息
begin
dbms_stats.gather_table_stats(ownname =>'SH',
tabname => 'CUSTOMER_EUR',
method_opt=> 'for all indexed columns size auto');
end; 2 3 4 5
6 /
PL/SQL procedure successfully completed.
dbms_stats.gather_table_stats的method_opt控制生成统计信息的列，及桶的大小，生成方法等。method_opt有以下两个选项（也可以使用两种选项的组合）：
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] --在所有列上生成直方图
FOR COLUMNS [column_clause] [size_clause] --在指定列上生成直方图
size_clause 的形式是： SIZE {integer | REPEAT | AUTO | SKEWONLY}。
interfer指定直方图里桶的数量，值在1-2048之间
repeat：只有在列上已有直方图的情况下才搜集直方图信息。
AUTO：Oracle根据列上的数据分布信息和工作负载确定是否收集列的直方图信息。
SKEWONLY：Oracle根据列上的数据分布信息确定是否收集列的直方图信息。
5 检查生成的直方图信息
SELECT TABLE_NAME,COLUMN_NAME,NOTES,HISTOGRAM FROM user_TAB_COL_STATISTICS WHERE TABLE_NAME ='CUSTOMER_EUR'
and COLUMN_NAME='COUNTRY_ID';
TABLE_NAME |COLUMN_NAME|NOTES|HISTOGRAM|
------------+-----------+-----+---------+
CUSTOMER_EUR|COUNTRY_ID | |FREQUENCY|
列上已经创建了类型为FREQUENCY的直方图。生成的直方图的详细信息如下：
SELECT * FROM USER_HISTOGRAMS WHERE table_name='CUSTOMER_EUR' AND COLUMN_NAME='COUNTRY_ID';
TABLE_NAME |COLUMN_NAME|ENDPOINT_NUMBER|ENDPOINT_VALUE|ENDPOINT_ACTUAL_VALUE|ENDPOINT_ACTUAL_VALUE_RAW|ENDPOINT_REPEAT_COUNT|SCOPE |
------------+-----------+---------------+--------------+---------------------+-------------------------+---------------------+------+
CUSTOMER_EUR|COUNTRY_ID | 7779| 52770|52770 |Ã G | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 15952| 52776|52776 |Ã M | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 16335| 52777|52777 |Ã N | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 18374| 52778|52778 |Ã O | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 22207| 52779|52779 |Ã P | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 22915| 52786|52786 |Ã W | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 23006| 52788|52788 |Ã Y | 0|SHARED|
CUSTOMER_EUR|COUNTRY_ID | 30563| 52789|52789 |Ã Z | 0|SHARED|
6 语句的执行计划
生成直方图之后，语句执行计划如下
SQL_ID 0r9r49jtpmsu8, child number 0
-------------------------------------
select * from sh.customer_eur WHERE country_id = 52788
Plan hash value: 968808656
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_EUR | 91 | 17654 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_CE_C | 91 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COUNTRY_ID"=52788)
19 rows selected.
Oracle优化器根据创建的列直方图统计信息得出的查询返回数据的行数是准确的，并据此选择了索引范围扫描，语句的执行成本也降低了很多。下面是另一个条件下的执行计划
SQL_ID g1v1nqms4snpb, child number 0
-------------------------------------
select * from sh.customer_eur WHERE country_id = 52776
Plan hash value: 3148057754
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 240 (100)| |
|* 1 | TABLE ACCESS FULL| CUSTOMER_EUR | 8173 | 1548K| 240 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COUNTRY_ID"=52776)
当COUNTRY_ID是52776时，返回的数据时8173行，Oracle据此选择了全表扫描。由此可见，Oracle优化器可以根据查询返回数据的不同选择合适的访问路径及执行计划。
7 总结
在列上的数据分布不均衡的情况下创建直方图，可以给优化器提供更加准确的列数据分布信息，从而得出更加合适的执行计划。
