文章整理来源于：
剑破冰山之Oracle开发
http://www.dbform.com/html/2010/1200.html
--下面来先关组下ORACLE的预测行对执行计划的影响。
DROP TABLE T PURGE;
CREATE TABLE T(ID,COL1,COL2)
AS
SELECT ROWNUM,CASE WHEN ROWNUM >100 THEN 200 ELSE ROWNUM END,ROWNUM
FROM DUAL
CONNECT BY LEVEL <=10000;
CREATE INDEX T_COL1 ON T(COL1);
admin@ORCL> SELECT COUNT(*) FROM T WHERE COL1=200;
COUNT(*)
----------
9900
admin@ORCL> SELECT COUNT(*) FROM T WHERE COL1<>200;
COUNT(*)
----------
100
--收集表统计信息，但不收集直方图(SIZE 1即不收集)
admin@ORCL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 'ADMIN',
4 'T',
5 CASCADE=>TRUE,
6 ESTIMATE_PERCENT=>100,
7 METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
8 NO_INVALIDATE=>FALSE);
9 END;
10 /
--查看执行计划,返回的行数实际上应为为9900，而Oracle预估的返回行数为99，导致Oracle认为走索引效率比较高。这是个比较错误的执行计划。
admin@ORCL> SELECT * FROM T WHERE COL1 = 200;
已选择9900行。
执行计划
----------------------------------------------------------
Plan hash value: 1192298089
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 990 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 99 | 990 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_COL1 | 99 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=200)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1363 consistent gets
0 physical reads
0 redo size
212731 bytes sent via SQL*Net to client
7634 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9900 rows processed
--了解下Oracle在没有收集直方图的情况下，如何计算出COL1 = 200的返回行数
admin@ORCL> SELECT COUNT(DISTINCT COL1) FROM T;
COUNT(DISTINCTCOL1)
-------------------
101
admin@ORCL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
10000
--Oracle 会认为COL1每个值都是均匀分布的
admin@ORCL> select round(10000/101) from dual;
ROUND(10000/101)
----------------
99
--全面收集行的直方图
admin@ORCL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 'ADMIN',
4 'T',
5 CASCADE=>TRUE,
6 ESTIMATE_PERCENT=>100,
7 METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',
8 NO_INVALIDATE=>FALSE);
9 END;
10 /
--Oracle选择了正确的执行计划：全表扫描，逻辑读也从1363->685
admin@ORCL> SELECT * FROM T WHERE COL1 = 200;
已选择9900行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 99000 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 9900 | 99000 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=200)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
685 consistent gets
0 physical reads
0 redo size
212731 bytes sent via SQL*Net to client
7634 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9900 rows processed
--我们来仔细研究下Oracle是如何存储列的直方图信息
--直方图的类型有三种
None: 没有直方图
Frequency: 频率直方图
HEIGHT BALANCED: 等高直方图
--当该列的distinct值数量<=bucket数量时，直方图的类型为FREQUENCY,否者为HEIGHT BALANCE
admin@ORCL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T' AND OWNER = 'ADMIN';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- ------------ ----------- ---------------
ID 10000 254 HEIGHT BALANCED
COL1 101 101 FREQUENCY
COL2 10000 254 HEIGHT BALANCED
--col1的直方图类型为Frequency,
--在DBA_TAB_HISTOGRAMS视图中，字段endpoint_value就对应字段COL1的值，
--而endpoint_number对应字段COL1值的记录条数。注意：这里的记录条数是累加的。
admin@ORCL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
2 FROM DBA_TAB_HISTOGRAMS
3 WHERE OWNER = 'ADMIN'
4 AND TABLE_NAME = 'T'
5 AND COLUMN_NAME = 'COL1';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
COL1 1 1
COL1 2 2
COL1 3 3
COL1 4 4
COL1 5 5
COL1 6 6
COL1 7 7
COL1 8 8
COL1 9 9
COL1 10 10
.... .. ..
COL1 98 98
COL1 99 99
COL1 100 100
COL1 10000 200
--Frequency直方图的统计方式应该就等同于下面的sql
admin@ORCL> SELECT COL1 AS ENDPONT_VALUE,COUNT(*) AS CNT,SUM(COUNT(*)) OVER(ORDER BY COL1) AS ENDPOINT_NUM FROM T GROUP BY COL1 ORDER BY COL1
ENDPONT_VALUE CNT ENDPOINT_NUM
------------- ---------- ------------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 1 7
8 1 8
9 1 9
10 1 10
.. . ..
98 1 98
99 1 99
100 1 100
200 9900 10000
已选择101行。
--我们看看直方图类型为HEIGHT BALANCE类型时，是如何存储的
--为了方便演示，这里SIZE 250表示250个bucket来存放数据
admin@ORCL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 'ADMIN',
4 'T',
5 CASCADE=>TRUE,
6 ESTIMATE_PERCENT=>100,
7 METHOD_OPT=>'FOR ALL COLUMNS SIZE 250',
8 NO_INVALIDATE=>FALSE);
9 END;
10 /
PL/SQL 过程已成功完成。
--查看直方图信息
admin@ORCL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T' AND OWNER = 'ADMIN';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- ------------ ----------- ---------------
ID 10000 250 HEIGHT BALANCED
COL1 101 101 FREQUENCY
COL2 10000 250 HEIGHT BALANCED
admin@ORCL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, END
2 FROM DBA_TAB_HISTOGRAMS
3 WHERE OWNER = 'ADMIN'
4 AND TABLE_NAME = 'T'
5 AND COLUMN_NAME = 'ID';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
ID 0 1
ID 1 40
ID 2 80
ID 3 120
ID 4 160
ID 5 200
ID 6 240
ID 7 280
ID 8 320
ID 9 360
ID 10 400
.. .. ...
ID 248 9920
ID 249 9960
ID 250 10000
--这里的ENDPOINT_NUMBER不再表示记录的条数，它标示BUCKET(桶)的编号，ENDPOINT_VALUE的算法如下：
--记录条数为10000条，共分配到250个桶中，那么每个桶的数据量为40条记录
第一个ENDPOINT_VALUE的值相当于如下SQL的返回值。
admin@ORCL> select max(ID) from (select ID from t order by ID) where rownum<=40;
MAX(ID)
----------
40
第二个ENDPOINT_VALUE的值相当于如下SQL的返回值。
admin@ORCL> select max(ID) from (select ID from t order by ID) where rownum<=40*2;
MAX(ID)
----------
80
第三个ENDPOINT_VALUE的值相当于如下SQL的返回值。
admin@ORCL> select max(ID) from (select ID from t order by ID) where rownum<=40*3;
MAX(ID)
----------
120
/*
依次类推。正是这样的信息告诉了Oracle数据的分布情况，试想一下，如果连续3个bucket的ENDPOINT_VALUE值都10，
那么也就是说至少有2个bucket中的记录N2字段都是10，也就是说ID=10的记录至少有80条，越多的bucket有越多相同的ENDPOINT_VALUE值，
就表明数据分布越不均匀。
有一点需要额外注意的是：如果某几个bucket的ENDPOINT_VALUE值相同，那么在视图中只会记录最后一个bucket的信息。
*/
--何时应该收集直方图
首先我们要明确直方图信息是有存在的必要的，但是只应该存在在那些应该要存在的列上。或者说我们希望对不不同的字段值走不同的执行计划时
才考虑收集直方图。
--反过来说，什么情况下我们不需要直方图呢？或者说直方图的存在是没有意义的呢？
1. 当此列不用于查询时，也就是这个字段永远不会出现在where条件中（注意：即使是用于表连接那也算是出现在where条件中）。
2. 当此列无论给予什么比较值，我们都希望永远是一种执行计划时。
分享到：
相关推荐
Oracle直方图的详细解析,详细解析Oracle直方图的来龙去脉
Oracle 直方图计算公式，对于Oracle cbo优化器的探究又进入一步
文章内容转载自网络。 从直方图的概念 直方图的作用、使用场合……等内容深入分析了oracle直方图，并给出了操作实例，建议收藏备查。
Oracle 直方图解析.pd
Oracle直方图.pdf
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘 20130429
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘_0321.pdf
用C#绘制直方图，饼图，曲线图，里面谢了一个方法可以连接oracle数据库查询数据，进行绘制。
如果对目标收集了直方图，则意味着CBO不再认为目标列上的数据是均匀分布的。CBO会用该列上的直方图的统计信息计算返回结果集的cardinality. 验证直方图对执行计划的影响步骤： 1、创建一张表T1 2、往表中插入倾斜度...
2015 Oracle 技术嘉年华（OTN）分会场12乔晓阳 - 一个直方图问题引发的思考
其中主要包括收集数据库统计系统、收集列直方图、分析SQL执行计划、如何让CBO优化器选择最优的执行计划，以及如何使用Hint提示认为改变CBO优化器的执行计划等，希望此文档能够帮助大家更深入地理解Oracle优化！
还有我们可以考虑我们的哪些列上需要直方图，对于bucket的个数问题,oracle的默认值是75个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL语句硬解析时...
7.1 直方图 141 7.2 dbms_stats包 147 7.3 动态采样 176 7.3.1 什么是动态采样 176 7.3.2 动态采样的级别 182 7.3.3 什么时候使用动态采样？ 185 7.4 小结 185 第8章 并行执行 186 8.1 并行和olap系统 187 8.2 并行...
7.2.2 Oracle何时忽略直方图 149 7.3 频率直方图 152 7.3.1 伪造频率直方图 155 7.3.2 注意事项 156 7.4 “高度均衡”直方图 157 7.5 重新审视数据问题 163 7.5.1 愚蠢的数据类型 163 7.5.2 危险的默认值 166 7.6 本...
这是一个读取12位RAW图像，并将其转换为BMP文件的程序，供新手学习使用
2019年Oracle Open World大会PPT合集（50份） DBA未来的工作方向及技能要求 Exadata性能诊断 MySQL性能优化可扩展性 ...MySQL 8.0 有了直方图信息可快速检索几十亿商品信息 MySQL8.0 外键增强 等等
7.1 直方图 141 7.2 dbms_stats包 147 7.3 动态采样 176 7.3.1 什么是动态采样 176 7.3.2 动态采样的级别 182 7.3.3 什么时候使用动态采样？ 185 7.4 小结 185 第8章 并行执行 186 8.1 并行和olap系统 187 8.2 并行...
本例使用DBChart为基础，可配置连接Oracle、MySQL、SQL Server以及Access数据库。根据用户配置的语句，生成数据图表。