3 直方图--优化主题系列

直方图当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方图，直方图最大的桶数(Bucket)是254。收集直方图是一个很耗时的过程，如无必要，千万别去收集直方图。

 

请注意在 OLTP 系统中

如果没有必要

千万不要去收集直方图统计

 

因为你收集了直方图

可能遇到绑定变量窥探

9i,10g里面几乎没有好办法解决

11g里出了一个自适应游标解决了这个但也有风险

之所以有绑定变量窥探

就是因为收集了直方图

 

我们来举个例子探究下直方图到底是干什么的

create table test as select * from dba_objects;

BEGIN

 DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',

                               tabname          => 'TEST',

                               estimate_percent => 100,

                               method_opt       => 'for all columns sizeskewonly',

                               no_invalidate    => FALSE,

                               degree           => 1,

                               cascade          => TRUE);

END;

/

select a.column_name,

       b.num_rows,

       a.num_distinct Cardinality,

       round(a.num_distinct /b.num_rows * 100, 2) selectivity,

       a.histogram,

       a.num_buckets

  from dba_tab_col_statistics a, dba_tables b

 where a.owner = b.owner

   and a.table_name = b.table_name

   and a.owner = 'SCOTT'

   and a.table_name = 'TEST';

为了讲解直方图，我收集统计信息的时候是method_opt => 'for all columns size skewonly',

正式的生产环境中，最好别用allcolumns方式收集直方图，因为allcolumns几乎会对所有列都收集直方图信息，但是有些列并不会出现在where条件中，我们去收集并不会出现在where条件中的列就浪费了资源，并且OLTP环境中，能不收集直方图就不要收集直方图。

Oracle的直方图有两种：

一种是频率直方图(FREQUENCYHISTOGRAM)，当列中Distinct_keys较少(小于254)，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于Distinct_Keys。

 

owner这列基数等于多少？发现这个规律没？

当一个列的基数小于254

那么直方图的桶数就等于列的基数

这种直方图就叫频率直方图

 

另外一种是高度平衡直方图(HEIGHTBALANCED)，当列中Distinct_keys大于254，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建高度平衡直方图。

 

下面来看下直方图到底有何作用，基于刚才创建的表test创建一个索引idx在owner列

create index idx on test(owner);

select 列,count(*)from table group by列order by 2 desc;

大家觉得走全表扫描是对的还是错的？

select 31148/72834 from dual;

返回了42%的数据因此走全表扫描、不该走索引是对的

 

select * from test where owner='SCOTT'; （执行计划截图略）

这个执行计划是对的还是错的

之前对test表收集了直方图 

 

在对列owner进行检查的时候

oracle会根据列的分布

CBO很聪明会自动的选择走索引或者不走索引

 

现在删除直方图统计，看看CBO是否还能做出正确的选择

BEGIN

 DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',

                               tabname          => 'TEST',

                               estimate_percent => 100,

                               method_opt       => 'for all columns size 1',

                               no_invalidate    => FALSE,

                               degree           => 1,

                               cascade          => TRUE);

END;

/

method_opt=> 'for all columns size 1' 表示所有列都不收集直方图

select a.column_name,

       b.num_rows,

       a.num_distinct Cardinality,

       round(a.num_distinct /b.num_rows * 100, 2) selectivity,

       a.histogram,

       a.num_buckets

  from dba_tab_col_statistics a, dba_tables b

 where a.owner = b.owner

   and a.table_name = b.table_name

   and a.owner = 'SCOTT'

   and a.table_name = 'TEST';

没有直方图了来看看刚才的两个查询

是不是走索引逻辑读反而大了很多？？

说明查询SYS走索引不合理

为什么查询SCOTT走索引查询SYS也走索引呢

看下执行计划ROWS这列 CBO认为应返回2428条记录

而总条数为72834OWNER列的基数是30

从七万多条数据查出2428条  走索引还是全表扫描？

查询SYS时的执行计划ROWS列也是2428？？

实际查询返回行31148 但CBO仍然认为返回2428行

返回了42%的数据

也就是说CBO认为无论查询哪个条件都返回2428行

没收集直方图 CBO认为这些列的值是平均分布的

 

总结：直方图是告诉CBO每个列的值的分布情况

如上收集完此列直方图后

CBO估算出来的和返回的真实行数一致CBO估算更为准确

 

频率直方图基数小于254最为精准

因为它能完全的体现出这个值对应多少条几记录

 

但高度平衡直方图则不然

因为最大的桶数只有254个某列基数超过254

这个时候记录某个值有多少记录就会共享记录

就是某些数据共享记录在同一个桶里因此并非精准

 

统计一下表的SEGMENT_SIZE或者你来统计一下表一共有多少个块

select sum(blocks) from dba_segments where owner='SCOTT' andsegment_name='TEST';--1152

多块读的个数是不是16？？？那么一共要消耗的I/O是多少？

select 1152/16 from dual; --72 是不是全表扫描只需要 72次 IO 就搞定了？

我们来计算索引扫描的I/O 次数 

SQL优化最最核心的思想 就是减少 I/O 扫描次数

索引扫描一般的是不是单块读？除了 INDEXFAST FULL SCAN

INDEX RANGE SCAN 是不是单块读？

刚才是不是要返回 2W多行数据

假设索引的高度是2那么你扫描索引最少都要扫描 2个块是不是耗费了 2次I/O?

刚才是不是索引返回2W多行数据

我们假设索引一个索引块能存100个记录

2W 条记录返回是不是200个块？

扫描 200个块是单块读现在是不是要进行 200多次 I/O 扫描次数？

索引里面是不是要存储 ROWID？

2w 多行数据2w多个 ROWID是不是也要消耗几百个 I/O?

是不是扫描索引要耗费几百次的 I/O？？？走全表扫描只需要72次

假设我有 1000W行的表返回 500W行

假设 1000W 行的表有多大？你们觉得1000W行的表的SEGMENT_SIZE有多大？

5W 行6.5MB乘以200就ok了？ 1300 MB

我们来算一下 1300 MB 走全表扫描要消耗多少次I/O??? 8k多块读参数16

1W 多次I/O对吧 selectceil(1300*1024/8/16) from dual; --10400

我们来算索引扫描要多少次I/O?

1000w行返回 500W行索引的高度3一个索引块存100条记录

是不是扫描索引块要扫描 5W次？有500W个ROWID要回表回表也是单块读啊也要几万个I/O

那是不是接近 10W次 I/O???走全表扫描是不是 1W次I/O

等待事件是什么？db file sequential read?

你们去监控一个SQL跑N久不出结果是不是有可能本来该全表扫描的但是走了索引扫描？？

AWR 中db file sequential read 排名第一应该咋搞？？？是不是想到把SQL优化了？？

我问一下一次 I/O多块读与一次I/O单块读时间差别大不大？

现代的存储多块读其实 是多个存储读的存储底层做了条带化

一次多块读在多个磁盘同时的读一次单块读在同一个磁盘读速度差别不是太大

以后我们就认为 多块读与单块读速度一样 

所以 SQL 优化就只关心扫描次数了多块读与单块读 具体的时间差别体现在搬运时间与磁盘寻道寻址

这些时间都是 0.00秒计算的只有 I/O扫描次数上了几千万次

SQL优化核心思想就是减少I/O扫描次数

直方图搞明白是干嘛的了没？如果一个列不去收集直方图

那么基数算法一样的块读是不是性能要高??1倍时间I/O次数多