文章整理来源于:
剑破冰山之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. 当此列无论给予什么比较值,我们都希望永远是一种执行计划时。