关于作者：
Jonathan Lewis是Oracle世界的知名人物，拥有将近25年的使用经验。他出版了三本关于Oracle的书，并参与写作其他的三本。他最近的著作是《Oracle核心》(Apress, ISBN 978-1430239543)，这是一本描述Oracle数据库引擎的中心工作原理的书。他运行着几个网站并且经常为各种新闻组、论坛、用户组杂志和全世界的各种活动撰稿。
在这一系列关于直方图的短文中，我们将会察看为什么需要直方图的理由，和ORACLE用于产生直方图的方法。我们将会检查产生直方图的代价，以及它们引入的潜在的负面开销，然后回顾一下它们可能给你的执行计划带来的稳定性问题。这个回顾仅限于Oracle 12c之前的版本；在12c中已经出现了新的直方图类型以及收集方法，旨在消减开销和改善稳定性。
一个简单的例子
不久前，我的一个客户在他们的一个看起来很简单的应用中看到一些奇怪的性能问题。他们处理的是一个在线销售系统，在一天之中他们需要打印如下形式的常规报表：“显示已经下单但是还未分发的订单”。这个需求转换为一条非常简单的SQL:
select {列名清单}
from orders
where
order by
;
在一天之中的任意一个时刻，符合这个条件的订单仅仅是少数——在几百万行的表中仅有一两百条。在status(状态)列上有一个索引，可以提供有效的存取路径，我们可以假设查询会毫无疑问地执行得很快，理由很简单，新订单会被机械地存放在最近添加到表里的那些数据块，并且这些块会被缓存。问题是有些时候这个报表的运行要花几十秒，而不是理论上的瞬间返回。
当然，最初的诊断就是检查执行计划，看看是不是预期的那个——查询是不是在做有效率的事情。没有必要再次做诊断——当查询运行很快时，Oracle使用了预期的索引，当它运行很慢时，Oracle执行的是表扫描操作。所以问题就从“为什么查询很慢”变成了“为什么优化器有时候会认为表扫描是一个好主意”。
通过阅读业务活动的描述，以及本文的标题，你可能已经有了很好的认识。数据集是非常倾斜的，当优化器“看到”这个倾斜的现象，我们就得到了正确的计划，当它看不到倾斜时我们就得到了错误的计划。
这里有个查询（基于数据的模型）揭示了此类问题：
select status, count(*)
from orders
group by status
order by
;
可以看到，大部分数据最终处于两种状态之一（取决于它最终如何发给客户），很小的一部分数据分布于一系列的其他值。当你看到这样的数据并且需求是要访问“罕见”或者“不常见”的值（后者是Oracle推荐的术语），你的思路可能就会往两个方向转化：虚拟列（这就意味着基于函数的索引，或者11g所实现的虚拟列，甚至是11g的“扩展统计信息”），或者直方图。
虚拟列及其他
我能想到的最好解决方案来自虚拟列（或者11g之前的基于函数的索引），因为这允许我们维护一个非常小的索引，而无视数据集的大小。所以我们可以创建如下的东西:
create index ord_new on orders(
);
begin
end;
/
虽然我必须收集包含了索引定义的隐含列上的统计信息，在创建索引之后收集所有隐含列的统计信息可能是一种昂贵的手段——我可以检查user_tab_cols视图来得到最近定义的列名，它可能是类似sys_nc00037$这样的东西，然后仅仅在这个特定的列上收集统计信息。（注意：从技术上讲“else null”是多余的——但是我喜欢把这个最终选项显式地包含进去。）
当然，我可能需要为其他不常见值运行类似的查询，所以我可以创建另外两个类似上面的索引，我也可以创建一个索引包含了这三个值——这里是一个11g虚拟列方法的例子：
alter table orders
add (
)
/
begin
end;
/
create index ord_is on orders(interesting_status);
不管你用的是虚拟列还是基于函数的索引的方法，都有一个限制：你必须修改应用代码来利用它, 例如下列的函数索引方法——11g中“适当”的虚拟列使得代码看起来比函数索引的代码更加整洁（译者注：虚拟列或者视图都可以使得你在WHERE子句中直接引用列名而不是复杂的表达式），但是仍然有一个修改：
select {列名清单}
from orders
where
order by
;
直方图
那么，假如无法修改代码，我们还能怎么办？我们必须确保优化器能够意识到这个问题，因为假如我们不这么做，优化器的基本模型就会产生一个对基数（行数）的错误估计值，并且选择了糟糕的执行计划。我们为优化器收集的最简单的级别的统计信息会这么说：表里有1,030,000行数据，这个列有5个不同的值，没有空值，值从'C'到'X'均匀分布。在这个信息的影响下，优化器为谓词“status = 'C'”的基数估算就会被推导为：总行数/不同值的个数=206,000。当然，这里的假设是你用了100%取样率(estimate_percent => 100)来收集统计信息，如果你用的版本比11g更旧，或者尚未转化到11g的“近似 NDV”机制，那么结果就比较难以预料（译者注：Approximate NDV是11g的一个新特性，为了减少开销而采用HASH算法来取代排序算法计算COUNT DISTINCT得到近似不同值的个数NDV即Number of Distinct Values）。
这就是直方图发挥作用的地方——它们允许我们为优化器提供更详细的关于数值在列中分布的信息。在12c之前，有两个品种：频率直方图和等高直方图——在我们的例子中我们需要的是频率直方图。（注意：12c有两种新的直方图：Top-N和混合直方图。）
原则上说，一个频率直方图就是数据在某个时间点的确切映像(这“某个时间点”非常重要)，而等高直方图则是数据分布的近似映像，它
试图捕获的是常见值以及其他值的不均匀分布的详细信息。一个频率直方图仅当一个列包含的不同值不超过254个时才能被创建（在12c中是2,000个）,而等高直方图就更不精确，不能捕获超过127个常见值的信息。在本文剩下的篇幅中我将只限于讲述频率直方图。
频率直方图
在我们的例子中仅仅有五个不同的值，我的数据模型也只包含100多万行的数据。通过使用如下的method_opt参数设定来收集表统计信息，我可以要求Oracle在一个列上收集直方图：for columns status size 254 (注意，虽然我值得总共才有五个值，我也可以要求最大值，Oracle会发现5个已经足够了)。如果我还把estimate_percent设为100, 我将最终在user_tab_histograms视图中得到这个列的下列数据:
select
from
select
from
where
and
order by
/
S
- ---------- -------------------------------
C
P
R
S
X
我展示了一种方法用来将endpoint_value从数值转换为等价的字符，因为这里保存的是字符型列的信息，如果你手头有ASCII码表你会发现大量的空格(0X20)被追加到endpoint值的十六进制格式中。更多的关于这个转换如何运作的细节可以参见：
http://jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/
注意直方图是以累计频率的方式高效存储的，我用分析函数lag()将其解开，这样就允许你看到Oracle在数据中保存了每个不同的值的准确计数。
有了这个信息，并且假设我的SQL确实用了字面常量，当优化器从谓词“status = 'R'”计算基数的时候，它可以查到这是一个直方图中具有的值，并且返回它找到的在那里保存着的计数值。在这个特定的例子中，频率直方图提供了巨大的帮助，你可能会问，为什么我们不是简单地为应用中每一个列都创建直方图呢（或者仅仅是为WHERE子句中出现的列创建）？
频率直方图的危害
直方图总共有四种主要的害处，我用下面的大纲列了出来，然后我将会按顺序对它们进行查看：
我说过如果你使用字面常量的话，优化器能够从直方图中选择正确的数据。如果你在这个查询中用了绑定变量，那么优化器将会在首次解析的时候使用“绑定窥视”，并且仍然产生正确的基数（以及执行计划）；但是在11g的“自适应游标共享”和12c的“自适应执行计划”出现之前，那个计划（本质上）就是你在所有后续执行该查询时会一直使用的计划，不管绑定变量的值怎么改变。在我的例子中，配合我客户的系统，绑定变量不会有问题，因为在这个表上仅有的查询都是非常简单的“status = {罕见值}”，为状态'R'生成的计划对'P'和'S'也适用——但通常你不会这么走运。如果你在一个列上建立了直方图，那么你就希望在应用中能够做些事情来让优化器很好地处理直方图——那就意味着在WHERE子句中使用字面常量（这本身就有问题），它还可能意味着要用点其他的伎俩，比如写应用代码来检查用户的请求，然后根据不同情况在一个短小的列表中选取最合适的SQL来运行。
假设你已经想出了如何最好地在你的代码中利用频率直方图，你仍然有一个问题，就是必须确保优化器要读取直方图的时候，它是准确的。当我告诉Oracle以100%作为取样比例时它就会执行下面这个SQL（译者注：这是收集直方图过程中内部产生的SQL）：
select
from
order by
;
确切的查询取决于Oracle的版本，以及Oracle是否认为这个列需要一个频率直方图或者等高直方图，但基本原则是你将会看到一个聚合查询，它将会咀嚼大量的数据，而且对于每一个你认定为直方图目标的列都会出现一个这种查询的变体。收集直方图是一项昂贵的操作（至少，精确收集是昂贵的）。
你可以利用取样，而不是计算，来降低收集直方图的代价。当你这么做的时候，你就会看到类似的SQL, 虽然会有些变化，特别是Oracle经常会拷贝一些原始数据的样本到一个为此目的创建的全局临时表，然后在该临时表上执行此查询。这可能会导致建立直方图所需要做的工作减少很多——但它也引入了另外一个害处。当我在收集直方图的时候给了Oracle“自动取样比例(auto_sample_size)”的选项，我的原始数据上的直方图的内容就会像下面这样：
S
- ---------- -------------------------------
C
P
S
X
如果你将数字相加起来，你就会看到Oracle在表中取样了5,498行——因此当它在估算任意给定值的行数的时候，它就会检查直方图并且乘以 1,030,000/5498（分子是根据user_tables.num_rows减去user_tab_cols.num_nulls得到的数字），所以status ='S'的估算值就会是187, 对于'P'则是375——这两个值都相当合理（特别是相比较我们在缺乏直方图的情况下得到的数字1,030,000/5而言）。
但我们要对状态值'R'怎么办——它没有出现在样本中，因此也没有出现在直方图中？在这种情况下，优化器会简单地将它在直方图中看到的最不常用的值的基数对半分——所以基数会被计算为94。再次强调，在这个例子中，这并不算太坏，而且它也不会改变关键的执行计划，但是假如Oracle每天取样的数据行让你不太走运，你的执行计划可能会无规律地改变，这时你就会领略到它的好处了。你能否在这个特定的数据集中看到主要的威胁所在？
假如Oracle在取样的时候看不到“任何一个”不常见值，最终得到的直方图说，数据大约在C和X之间平分为50/50，每种大约500,000 行，那又怎么样？一个带status = 'R'条件的查询将会使用“最不常见值的一半”——得出一个大约为250,000的估算值; 这就是我客户发生的情形。统计信息收集程序时不时地在此表上收集统计信息（这就是10g缺省的夜间对过时统计信息的收集），并且错过了所有不常见的值，从而使得下一个24小时（或者直到下次统计信息的收集为止），优化器会决定在一个很大的表上使用扫描而不是使用一个高度精确的索引。
在直方图中无法捕获关键信息的这个想法将我们引入直方图的最后一个关键问题——当你在收集统计信息的时候关键信息缺失要怎么办。想象一下，假如我的订单处理系统中那些不常见值仅仅在6:00 am 到 6:00 pm 之间出现, 到10:00 pm之前它们全部被处理出了系统。当缺省的统计信息收集程序在晚间的某个时间运行，表中的值只有'C'和'X'，但是当查询在白天运行时，我们所感兴趣的那些值恰好就是统计信息被收集的时候不存在的值。即使用了100%的取样比例，你系统中的某些部分仍然有可能得到有误导性质的统计信息，假如你收集的时间是错误的。你必须对你的系统相当了解，才知道什么时候应用代码本身应该对统计信息的质量负责。这可能意味着你写代码来在一天的某个特定时刻收集统计信息；也可能意味着你写代码来直接操纵存储的统计信息（当我们查看等高直方图的时候，会顺便查看这种策略）。
结论
当数据的分布严重倾斜的时候，假如你想确保优化器不会产生很恶劣的执行计划，你就必须采取一些措施。如果你能控制应用代码，例如虚拟列或者基于函数的索引，可能对你处理这些特殊的值有帮助；如果你改不了应用代码，你可能需要依赖直方图。即使有直方图，绑定变量也能够轻易地导致问题——甚至11g和12c的新功能“自适应游标”和“自适应执行计划”也无济于事。
对频率直方图（比较简单的那种）的一个简短的检查，向我们展示了直方图对于一个具有少数不同值的列是多么的有用——特别是当你的SQL使用字面常量的时候。直方图，即使是简单的频率直方图，采集的成本可能很昂贵，除非它只对数据的一小部分取样，但如果我们真正感兴趣的数据只是全体数据中的极小部分，而且转瞬即逝，部分取样的直方图可能会引入不稳定性。事实上，即使你用100%的取样比例，但是时机不对，结果的直方图仍然可能导致问题，这要归咎于优化器对缺失值的处理方式。
直方图系列文章(一): 为什么?
Jonathan Lewis 发表于2013年8月28号
原文链接：
http://allthingsoracle.com/histograms-part-1-why/
转载地址：http://blog.sina.com.cn/s/blog_475839a50101ezag.html
扫一扫
1106
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
