深入理解Oracle优化器(1)：倾斜列(skew)和histograms

㈠ Histograms

柱状图？直方图？其实这俩是一个概念，在这里Think直接用histograms来称呼
histograms可以这么理解就是一个列上数值的大致分布的密度(density)和范围(range)
通俗一些就是CBO用histgrams来更加准确的判断按照某个条件对每一列查询能返回多少记录

histograms有两种类型

① 基于高度的histograms：每个范围包括相同数量的值，根据每个范围的终点的列值来判断数据的分布
② 基于数值的histograms：当列中不同的值的数量少于或等于histograms的buckets数量时，建立数值histograms
这种histograms列中每个值都有对应的bucket，根据每个值对应的bucket的个数来判断数据的分布

我们可以从视图dba_histograms/user_histograms,dba_tab_histograms查询

① 为什么需要histograms？

我们对经常在WHERE子句中使用的且数据的分布十分不均匀的列使用histograms

直方图究其根本实际上就是一个数据分布的图示，这个图示是为了在生成SQL执行计划的时候给Oracle的CBO更多的信息
也就是说，当在where条件中的某些列可能由于列值的不同而希望CBO制定出不同的执行计划时，我们需要直方图

那么，什么情况下我们不需要直方图呢？

⑴ 当此列不用于查询时，也就是这个字段永远不会出现在where条件中
⑵ 当此列无论给予什么比较值，我们都希望永远是一种执行计划时，比如，col1我们希望永远是用该列上的索引扫描
col2我们希望永远是全表扫描，这样的执行计划的制定，只要有表级别的统计信息就足够了
直方图信息的出现不但不会对制定正确的执行计划有帮助，甚至会出现奇怪的现象导致执行计划不稳定
⑶ 列中数据均匀分布，比如身份证号，QQ号，主键等
⑷ 列中数据唯一且只使用相等作为判定条件
⑸ 对这个列所有的判定条件都使用了绑定变量


② 如何搜集histograms

只有我们的DBA才最知道哪些列上应该收集直方图，这实际上已经远远不仅仅是技术问题了
而是一个业务问题，因此DBA应该去熟悉业务，DBA应该知道自己的应用的数据分布特点，应该知道哪些列会常被用在where条件中


Ⅰ analyze


histograms事实上它描述的就是数据在存储桶的分布范围！
如何生成histograms？可以通过对表做分析！
analyze table table_name compute statistics：不仅分析了表和索引，而且分析了表上的所有列，并生成了列的histograms
analyze table table_name compute statistics for table：仅仅产生表的statistics，不生成列的histograms
analyze table table_name compute statistics for all indexed columns：分析了表，并仅对表上的索引列产生histograms
analyze table table_name compute statistics for all columns：分析表，同时生成所有列的histograms
对所有列都生成histograms是没有意义的，只有对where语句中用到的列生成histograms是有意义的
默认列的histograms只有一个桶，可以对列分析时指定多个桶：
analyze table table_name compute statistics for columns column_name size n;

Ⅱ dbms_stats.gather_table_stats

⒈ 第一次收集统计信息时，设置method_opt=>FOR ALL COLUMNS SIZE 1，这意味删除所有列上的直方图
⒉ 在测试阶段或者在真实生产环境中，在调优SQL的过程中，DBA将会逐渐得知每个需要直方图信息的字段
在这些字段上人工收集统计信息，method_opt=>FOR COLUMNS SIZE AUTO [COLUMN_NAME]
如果你能够明确知道应该收集多少个bucket而手工指定SIZE值那更好。保留收集所有这些字段的脚本
以备数据库系统升级或者迁移时候使用
⒊ 在每次数据分布有所变化的时候，更新统计信息，使用method_opt=>FOR ALL COLUMNS SIZE REPEAT
这样只会收集已经存在了直方图信息的字段
重复2，3步骤，直到系统稳定。这是一个可控的步骤，只有可控，才可能避免不可预知的错误




㈡ skew

Oracle在界定skew时并不是用重复值来判断的
Oracle认为的倾斜列是指在最大值和最小值之间分布不均匀，即使它是唯一的

测试：

SQL> create table numa as select rownum a from histest;

Table created

Executed in 3.938 seconds

SQL> select count(*) from numa;

  COUNT(*)
----------
   1024384

Executed in 0.297 seconds

SQL> delete from numa where a between 9999 and 1024383;

1014382 rows deleted

Executed in 73.86 seconds

SQL> commit;

Commit complete

Executed in 0 seconds
SQL> select count(*) from numa;

  COUNT(*)
----------
     10001

Executed in 0.187 seconds

SQL> alter table NUMA
  2    add constraint UK_NUMA unique (A);

Table altered

Executed in 0.219 seconds

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'NUMA',method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed

Executed in 0.328 seconds

SQL> SELECT COUNT(*), column_name
  2    FROM dba_tab_histograms
  3   WHERE table_name = 'NUMA'
  4     AND column_name = 'A'
  5   GROUP BY column_name
  6   ORDER BY column_name DESC
  7  /

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
       201 A

Executed in 0.078 seconds