以前对直方图这个知识点仅限于了解，今天刚好碰到了，就来研究一下：
先来个直观的定义：
直方图是Oracle CBO优化器使用的一种统计数据，比如有一个字段a，取值范围是1－10000，整个表有100万条记录，那么如果你要查询a>10 and a<100的记录，如果这样的记录有100条，那么走索引是最好的，如果这样的记录有90万条，那么走索引肯定不如全表扫描。直方图里面可以看出记 录的分布情况，比如1－100有多少条，101－200有多少条记录，等等。优化器通过使用直方图，可以更准确的判断使用什么执行计划最优。
显然对于存在高度不均匀数据的表，使用柱状图能够产生更好的选择性评估，从而产生更加优化的执行计划。柱状图提供一种有效和简捷的方法来呈现数据的分布情况。
先来构造这样一个表：
SQL> create table tab(a number,b number); Table created. SQL> begin 2 for i in 1..10000 loop 3 insert into tab values(i,i); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed. SQL> update tab set b=5 where b between 6 and 9995; 9990 rows updated. SQL> commit; Commit complete. SQL> select b,count(*) from tab group by b; B COUNT(*) ---------- ---------- 1 1 2 1 3 1 4 1 5 9991 9996 1 9997 1 9998 1 9999 1 10000 1 10 rows selected.
这样表中有10000条数据，对于b列，有10个不同的值，等于5的值有9991个，此时无论是选择b=1或者b=5的数据都必须要进行全表扫描，因为表上没有索引，也即是没有别的访问路径。
SQL> set autot trace SQL> select * from tab where b=1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TAB' SQL> select * from tab where b=5; 9991 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TAB'
我们来创建一个索引，然后看看访问路径有什么不一样
SQL> create index i_tab_b on tab(b);
Index created.
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB'
2 1 INDEX (RANGE SCAN) OF 'I_TAB_B' (NON-UNIQUE)
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB'
2 1 INDEX (RANGE SCAN) OF 'I_TAB_B' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
717 consistent gets
0 physical reads
0 redo size
210834 bytes sent via SQL*Net to client
7981 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
由于没有进行统计信息收集，所以这时候oracle不知道表和索引的任何统计信息，使用的是基于RBO的INDEX RANGE SCAN，注意记住这里的逻辑读代价。
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len from dba_tables where table_name = 'TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
SQL> select index_name,table_name,num_rows,blevel,last_analyzed from dba_indexes where table_name='TAB';
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
BLEVEL LAST_ANAL
---------- ---------
I_TAB_B TAB
下边来收集一下表上的统计信息
SQL> analyze table tab compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len from dba_tables where table_name = 'TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
10000 61 3 13603 0 10
SQL> select index_name,table_name,num_rows,blevel,last_analyzed from dba_indexes where table_name='TAB';
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
BLEVEL LAST_ANAL
---------- ---------
I_TAB_B TAB 10000
1 26-AUG-10
SQL> select num_distinct, low_value, high_value, density, num_buckets, last_analyzed, sample_size from dba_tab_columns where table_name = 'TAB';
NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE
------------ ---------- ---------- ---------- ----------- --------- -----------
10000 C102 C302 .0001 1 26-AUG-10 10000
10 C102 C302 .1 1 26-AUG-10 10000
SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_tab_histograms where table_name = 'TAB';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
TAB A 0 1
TAB B 0 1
TAB A 1 10000
TAB B 1 10000
再来执行一下上边的两个查询,虽然执行计划没变，但是已经是CBO了
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=6000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=2 Card=1000 Bytes=6000)
2 1 INDEX (RANGE SCAN) OF 'I_TAB_B' (NON-UNIQUE) (Cost=1 Card=1000)
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=6000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=2 Card=1000 Bytes=6000)
2 1 INDEX (RANGE SCAN) OF 'I_TAB_B' (NON-UNIQUE) (Cost=1 Card=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1352 consistent gets
0 physical reads
0 redo size
210834 bytes sent via SQL*Net to client
7981 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
现在进入到了今天学习的核心，来创建b列的直方图，让oracle的优化器知道b列中每个值的分布情况，进而选择更优的执行计划。
SQL> analyze table tab compute statistics for columns b size 10; Table analyzed. SQL> set autot off; SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_tab_histograms where table_name = 'TAB'; TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------ ---------- --------------- -------------- TAB B 1 1 TAB B 2 2 TAB B 3 3 TAB B 4 4 TAB B 9995 5 TAB B 9996 9996 TAB B 9997 9997 TAB B 9998 9998 TAB B 9999 9999 TAB B 10000 10000 TAB A 0 1 TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------ ---------- --------------- -------------- TAB A 1 10000 12 rows selected.
直方图中的ENDPOINT_VALUE表示列值，ENDPOINT_NUMBER表示累积的行数。比如ENDPOINT_VALUE=2，ENDPOINT_NUMBER=2，因为ENDPOINT_NUMBER是个累积值，实际上2的 ENDPOINT_NUMBER应该是2减去上一个值的ENDPOINT_NUMBER，也即是2-1=1。同理，5的 ENDPOINT_NUMBER=9995-4=9991。
再来看看执行计划吧：
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=2 Card=1 Bytes=6)
2 1 INDEX (RANGE SCAN) OF 'I_TAB_B' (NON-UNIQUE) (Cost=1 Card=1)
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9991 Bytes=59
946)
1 0 TABLE ACCESS (FULL) OF 'TAB' (Cost=7 Card=9991 Bytes=59946
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
717 consistent gets
0 physical reads
0 redo size
210834 bytes sent via SQL*Net to client
7981 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
这时可以看出，不同值的分布导致了Oracle优化器选择了不同执行计划。对于b=5的查询来说，全表扫描比之索引范围扫描更加的合理，优化器正是根据直方图的统计信息做出的正确的判断。
上述的例子描述了一种理想的状况，因为我们为每一个不同的值创建了bucket。在实际的生产系统中，一张表可能包含很多的唯一值，我们不可能为每一个唯一值创建bucket，这样开销将是巨大的。
下面的例子描述了唯一值大于buckets的情况。
SQL> analyze table tab compute statistics for columns b size 8;
Table analyzed.
SQL> set autot off
SQL> select table_name, column_name, endpoint_number, endpoint_value
2 from dba_histograms
where table_name = 'TAB';
3
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
TAB A 0 1
TAB A 1 10000
SQL> analyze table tab compute statistics for columns b size 7;
Table analyzed.
SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_histograms where table_name = 'TAB';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
TAB B 0 1
TAB B 6 5
TAB B 7 10000
TAB A 0 1
TAB A 1 10000
ENDPOINT_NUMBER是实际的bucket编号，ENDPOINT_VALUE是根据列值决定的该bucket的endpoint值。上面的输出中，bucket 0存放着b列的低值，为了节省空间没有显示出1-6号的bucket。但是我们能够理解，bucket[1-7]里存放着的endpoint=5，而 bucket8里存放endpoint=10000。因此，实际上bucket0里包含了1-5之间的所有值，而bucket8里包含了5-10000之间的所有值，在本例中也就是9996-10000这5个数值。
综上所述，假如数据是均衡的，没有必要使用直方图。如果使用唯一值数量来创建直方图，Oracle为每个值创建一个bucket；但是假如实际的生产系统中，不能够为每一个唯一值分配一个bucket时，Oracle采用合适的算法尽可能将值平均分布到每个bucket中，剩余的值放入到最后的bucket。
对于数据分布均匀的列，直方图没意义，对应列中数据分布比较倾斜的列（不均匀），直方图就非常有用。CBO就可以估计大概的数据分布，计算查询的选择率和基数也更精确。
最后提示一点：如果要删除直方图统计信息，那么只需指定size 1即可：
SQL> analyze table tab compute statistics for columns b size 1;
Table analyzed.
SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_histograms where table_name = 'TAB';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
TAB A 0 1
TAB B 0 1
TAB A 1 10000
TAB B 1 10000
SQL> exec dbms_stats.gather_table_stats('TEST','TAB',cascade=>false,method_opt=>'for columns b size 1');
PL/SQL procedure successfully completed.
SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_histograms where table_name = 'TAB';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
TAB A 0 1
TAB B 0 1
TAB A 1 10000
TAB B 1 10000
转自：http://www.dbasky.com/oracle/oracle_histogram.html
转载于:https://blog.51cto.com/369day/1262804
497
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
