|
|
直方图系列文章(三): 何时?
Jonathan Lewis 发表于2013年10月16号
原文链接：
http://allthingsoracle.com/histograms-part-3-when/
在第二篇中我们查看了Oracle收集和使用等高直方图的方法，并提出了一个问题，就是直方图对于取样者中或者数据中的细微修改是如何的敏感。在文章的结尾我们建议你可以自己产生“近似”的，然而可能已经“足够好”的频度直方图数据来帮助优化器做出正确的事情。
然后，在第三篇中，我们将会查看几个例子，这时允许Oracle自己做主可能是合理的且充足的产品环境策略。作为一个普遍的指导原则，我发现仅仅当你使用等高直方图来“拿掉”很少的一部分频度很高的值时，才是合理且安全的——实际上这时就是允许优化器来为剩下的“感兴趣”的数据(即未拿掉的那些非频繁出现的值)做优化。然而，有那么几种设计错误，在那些情况下你可以使用直方图作为一种止损的机制。
我一直想针对如何将直方图的收集包含到完整的统计信息收集策略中发表一些看法，但这篇文章比我预想的要长，所以我将关于统计收集的实现方法的想法推迟，以后再说。
数据库不可知论的应用系统
有那么一些原生态的应用，它们写出来就是要能在“任何”数据库上运行，它们也就试图将对任何数据库功能的依赖性最小化。这样的后果就是，“在任何数据库上运行”就意味着“在任何数据库上拙劣地运行”。
从纯粹的数据角度来说，不可知论的两个最普遍的特点是：
我们不用NULL——我们用一个特殊的（极端）的值。
我们不用日期数据类型。
这在Oracle的优化器上可能带来灾难性的副作用。
我们不用NULL
这里有个脚本，它创建了一个简单的数据集来证明这个论点。这份数据代表5年的数据（从2008年1月1日开始），每天100行，每1000行就有一行被设为“伪NULL”值4000年12月31日。创建数据之后我收集了统计信息，但没有生成直方图，也即使用了参数 method_opt => 'for all columns size 1'
create table t1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 2000
)
select
/*+ ordered use_nl(v2) */
decode(
mod(rownum - 1,1000),
0,to_date('31-Dec-4000'),
to_date('01-Jan-2008') + trunc((rownum - 1)/100)
) date_closed
from
generator v1,
generator v2
where
rownum <= 1827 * 100
;
我不担心索引或者优化的存取路径的细节，现在我感兴趣的只是优化器的基数计算，它离正确答案有多接近。基于这个SQL, 你能看出一个“2010年所有数据”的查询应该返回36,500行——所以我们来写一个查询索取这些数据，然后检查结果的执行计划中的行数预测：
set autotrace traceonly explain
select
*
from t1
where date_closed between to_date('01-Jan-2010','dd-mon-yyyy')
and to_date('31-Dec-2010','dd-mon-yyyy')
;
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 291 | 2328 | 52 |
|* 1 | TABLE ACCESS FULL| T1 | 291 | 2328 | 52 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_CLOSED">=TO_DATE(' 2010-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE(' 2010-12-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
优化器预测的行数是291, 看起来偏差系数有125倍; 在大部分情况下，这么大的错误可能导致执行计划的错误选择。
为什么会这样？基本的优化器模型假设你的数据在低值和高值之间是均匀分布的，所以Oracle的估算是（忽略几个边界舍入的细节）：
“表中非空值的行数” * “你需要的区间” / “最高值 – 最低值”
在我的例子中是这样（近似地）：
182700 * 365 / 727928 + 200
Oracle以为我要从2,000年的数据中取出一年，而不是5年中取一年(带有两个100行的修正，这是考虑到有界区间的端点值) 。
在这时候我可以简单地写一小段PL/SQL，它调用dbms_stats.set_column_stats()来将这个列的最高值修改为更接近“合适”的最高值。如果偏僻的数据量（指年份为4000的“伪NULL”数据）很小，这就是一个合理的策略，你只需要记住，对于“伪NULL”的查询将会超出已知的区间很远，以至于优化器很可能为那个值给出行数为1的基数估算，不管你实际上有多少数据存在。
然而，不同于操纵统计信息的方法，我将会在此列上创建一个直方图。典型情况下我的目标是254桶（11g最大值），但是作为高效性的演示，我实际上会展示仅有11个桶（每两年一个，还有一个空的）直方图的效果——这是改变过后的计划：
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 36420 | 284K| 52 |
|* 1 | TABLE ACCESS FULL| T1 | 36420 | 284K| 52 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_CLOSED">=TO_DATE(' 2010-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE(' 2010-12-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
优化器的估算值36,420几乎是完美的。当对数据进行取样时，Oracle发现从2012年7月到4000年12月的数据只有十一分之一，而十一分之十的数据在2008年1月到2012年7月之间均匀分布，所以直方图给了它“理解”数据在我要求的日期区间内真正的面貌所需的信息（在7月20日之后的日期就不那么准确了，这就是我为什么实际上想要使用254个桶）。
注意：你看到的关于直方图的大部分文章描述了它们是如何帮助优化器来处理数据中的“尖峰”或者“频繁值”。其实，对数据中的任何模式，只要不是单调，平直，连续和统一的，直方图都会有帮助。在这个例子中，直方图告诉优化器，在数据的分布中有一个大缺口。
这就是视图user_tab_histograms关于这个数据集的内容（对于日期型，存储的值是儒略日格式）：
ENDPOINT_NUMBER ENDPOINT_VALUE EPV_CONVERTED
--------------- -------------- -------------
0 2454467 01-Jan-2008
1 2454633 15-Jun-2008
2 2454799 28-Nov-2008
3 2454965 13-May-2009
4 2455132 27-Oct-2009
5 2455298 11-Apr-2010
6 2455464 24-Sep-2010
7 2455630 09-Mar-2011
8 2455797 23-Aug-2011
9 2455963 05-Feb-2012
10 2456129 20-Jul-2012
11 3182395 31-Dec-4000
我们不用日期型
有些应用将日期存为字符串，要么是“YYYYMMDD”格式，要么是等价的数值型。这允许日期可读，同时保留正确的排序顺序。这是个样本数据集——还是从2008年1月运行到2012年12月，但这次每天只有一行，也没有“伪NULL”值：
create table t1
as
select
d1,
to_number(to_char(d1,'yyyymmdd')) n1,
to_char(d1,'yyyymmdd') v1
from (
select
to_date('31-Dec-2007') + rownum d1
from all_objects
where
rownum <= 1827
)
;
我将会写出差不多是我所能写出的最脏乱的查询来证明一个论点——我将会先在任何一列都没有直方图的情况下测试它，然后在直方图下测试。查询只是想问：“在2011年12月30日和2012年1月5日之间有多少天？”见多识广的DBA会算出答案是7(也可能是5,或6, 取决于他们如何解释这个“之间”)。优化器又会怎么想？
这就是我查询日期型列得到的答案：
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 184 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 184 | 3 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D1">=TO_DATE(' 2011-12-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "D1"<=TO_DATE(' 2012-01-05 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
它相当地接近——错误是由于优化器对区间的处理细节，它在选择性上加了1/num_distinct, 这是考虑到有界区间的端点值。
这是等价的VARCHAR2()类型值的查询计划（除非你是在很老的Oracle版本上运行）：
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 9108 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 396 | 9108 | 3 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1">='20111230' AND "V1"<='20120105')
答案偏差了79倍——这是不可接受的，并且很可能会生成一些劣质的执行计划。顺便说一下，如果你在数值型的列上尝试同样的查询，你会得到同样的结果——我想有一段“特殊的”优化器代码，在处理看起来像数字的字符串数据时，它使用了数值型的算法（也即当高，低，谓词的值看起来全部都像数值）。
错误是怎么来的呢？Oracle可以做日期算术，所以它能够准确地算出我们需要的区间：
2012年1月5日 - 2011年12月30日 = 6 这就是将计算引入正轨的数字。另一方面，如果你查看的是8位数字的数值型列：20120105 – 20111230 = 8,875 你就会开始怀疑，一些奇怪的基数可能会出现。
如果你将日期想像为一系列的数字，你得到的是（大致）30个连续的数字，然后跳过大约70个数字到下一个月，这样重复11次，然后又有一个大约9000的间隙进入下一年。如果优化器以为你的数据在20080131 和 20121231之间均匀分布，它的基数预测将十分散乱。
那么就创建一个直方图——这次我选了120个桶，为了捕获每个月的细节我们确实需要许多的桶（在产品系统上我会用254）——然后来看看效果：
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 345 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 15 | 345 | 3 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1">='20111230' AND "V1"<='20120105')
结果好多了——还不完美，但是可能足够精确让你得到所需要的计划。再说一次，你可能会注意到，我们并没有使用直方图来突出数据中的“尖峰”——每个单独的值都出现一样多的次数——我们利用直方图向优化器指出在数据中从低值到高值之间的那些间隙。
关于这个例子还有一点必须阐明：这个基数问题的理想解决方案是创建一个虚拟列（在早期版本的Oracle中可能是一个基于函数的索引）, 它将存储的数据转换为正确的类型，例如：
alter table t1
add (
d1_virtual generated always as (to_date(v1,'yyyymmdd')) virtual
)
;
begin
dbms_stats.gather_table_stats(
user,
't1',
method_opt=>'for columns d1_virtual size 1'
);
end;
/
只要你能够修改SQL来引用虚拟列（或者匹配基于函数的索引），你现在得到了一个正确的数据类型的列，还有正确的统计信息，使得优化器有最佳的机会可以计算出正确的基数。
结论
在缺乏直方图的情况下，优化器假设你的数据是从低值到高值平均分配的，中间没有间隙，也没有尖峰；如果你把你的数据画成图，看到的是一条连续平直的线，那么这就是优化器对你的数据的映像。如果你的数据的图显示出很大的间隙，或者是一种大幅度偏离了直线的图案，那么直方图就可能有用，尽管如同我们在第二篇中看到的，它们可能对细小的修改很敏感，并且在你的执行计划中引入了不稳定因素。
有那么几种情形，直方图可以用来解决你应用的拙劣设计——使用极端的值来取代NULL，或者将数据存为错误的类型（最显著的例子是将日期保存为字符串或者数值型）。在这些情况下，理想的策略是纠正设计的缺陷，但是修改数据库结构或者应用程序经常是办不到的事，所以直方图可能会帮助优化器处理这些设计带来的副作用。
|
|
楼主: newkid
IP卡
狗仔卡
发表于 2013-10-17 15:46
显身卡
楼主
