【沃趣科技】直方图系列1

墨蓝 2023-06-24 12:24 8阅读 0赞

沃趣科技作为国内领先的数据库云平台解决方案提供商，一直致力于企业级数据库云平台产品的研发，为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台，满足客户对极致性能、数据安全、容灾备份、业务永续等需求。沃趣科技凭借专业的团队，优质的产品，前沿的技术，贴心的服务赢得了客户的信任与尊重，也获得了市场的认同。

--------------------

在这个关于直方图的简短系列中，我们将讨论为什么需要直方图以及Oracle创建它们的方法。我们将评估创建它们的成本以及给我们带来不必要的问题，然后我们将检查它在您的执行计划中给您带来潜在的问题。这篇文章的概述将局限于12c之前的Oracle版本，其中出现了在减少开销和提高稳定性的新型直方图和收集方法。

### **一个简单的例子** ###

不久前，我的一个客户在他们的应用程序的一个非常简单的部分中发现了一些奇怪的性能问题。他们在处理一个在线销售系统，在一天的过程中，他们需要运行表单的常规报告:“向我显示最近下的订单，但尚未发出”。这个需求变成了一个非常简单的SQL:

select {list of columns}
    from orders
    where      status = ‘R’
    order by
    order_id
    ;

在一天中的任何一个时刻，只有少量的订单与这个谓词匹配——一个包含数百万行的表中有一两百个订单。在status列上有一个索引，以允许有效的访问路径，由于简单的原因，新订单将位于表的一部分中，该部分是添加到表中的最新块组，并且这些块将被缓存在内存中，因此假设查询非常快的执行。问题是，在某些时间，该报告需要几十秒才能运行出结果，而不是即时出现结果的。

当然，第一个要检查的是检查执行计划是否符合预期，即查询是否试图做一些有效的事情。当查询快速运行时，Oracle正在使用预期的索引，当查询缓慢运行时，Oracle正在执行表扫描。所以问题从“为什么查询速度慢？”到“为什么优化器有时认为表扫描是个更好的建议呢”？这两个方面来思考这个问题。看过了以上业务活动描述，以及给出了文章的标题，您可能已经有了一个非常好的想法：这个数据集非常的倾斜、当优化器“看到”倾斜时，我们得到了正确的计划，当优化器没有看到倾斜时，我们得到了错误的计划。下面的查询（针对数据模型）突出显示了问题的类型：

select status, count(*)
    from orders
    group by status
    order by
        status
    ;
    
        S       COUNT(*)
        C       529,100
        P           300
        R           300
        S           300
        X       500,000

如您所见，大多数数据最终都处于两种状态之一（取决于订单最终到达客户的方式），少量数据分散在其他几个值上。当您看到这样的数据并知道您需要访问“稀有值”或“热数据”值时，您的想法可能会转向两个方向之一：虚拟列（这可能意味着基于函数的索引，或虚拟列的11g实现，甚至11g“扩展统计”）或直方图。

### **虚拟列** ###

在我看来，最好的解决方案来自虚拟列（或11g以前的基于函数的索引），因为这允许我们维护一个非常小的、精确定位的索引，尽管数据集很大。因此，我们可以创建如下索引并收集统计信息：

create index ord_new on orders(
        case status when 'R' then 'R' else null end
    );
    
    begin
        dbms_stats.gather_table_stats(
            user,
            'orders',
            method_opt => 'for all hidden columns size 1'
        );
    end;
    /

尽管我需要收集包含索引定义的隐藏列的统计信息，但是在创建索引后收集所有隐藏列的统计信息代价可能很高，这样我就可以检查user\_tab\_cols 列以获取最新的列名，这将类似于sys\_nc00037$，并仅收集该特定列的统计信息列。（注意：技术上，“else null”是多余的，但我更喜欢显式地包含最终选项。） 当然，我可能希望对其他不常访问的值运行类似的查询，以便可以再创建两个类似于上面的索引，或者创建一个包含这三个值的索引—下面是使用11g虚拟列方法的示例：

alter table orders
    add (
        interesting_status    generated always as (
            case status
                 when 'C' then null
                 when 'X' then null
                        else status
            end
        ) virtual
    
    )
    /
    
    begin
        dbms_stats.gather_table_stats(
            user,
            'orders',
            method_opt => 'for columns interesting_status size 1'
    
        );
    end;
    /
    
    create index ord_is on orders(interesting_status);

基于虚拟列/函数的索引方法（无论您使用哪种方法）有一个限制—您必须更改应用程序代码以利用它—11g中的“适当”虚拟列使代码看起来比FBI代码更整洁，但仍必须进行更改，例如（对于我给出的FBI示例）：

select {list of columns}
    from orders
    where      case status when 'R' then 'R' else null end = ‘R’
    order by
        order_id
    ;

### **直方图** ###

如果我们不能改变应用程序代码该怎么办？我们必须确保优化器知道这个问题，因为如果我们不知道，那么基本优化器模型将对基数（行计数）产生错误的估计，并选择错误的执行路径。在最简单的级别上，我们为优化器收集的统计信息将显示：“表中有1030000行，此列有5个不同的值，没有空值，并且这些值从‘C’均匀分布到‘X’。”。有了这些信息，优化器对谓词“status='C'”的基数估计将派生为：总行数/非重复值数=206000。当然，假设使用100%个样本（估计百分比＝＝100）来收集统计数据；如果使用大于11G的版本，或者11G中还没有转换成“近似NDV”机制，则结果可能稍微不那么可预测。这就是直方图发挥作用的地方——它们允许我们向优化器提供有关列中值分布的更详细信息。在12c之前，它们有两种类型：频率直方图和高度平衡直方图——在我们的例子中，我们需要一个频率直方图。（注：12c有两种新的直方图类型：Top-N和hybrid）。原则上，频率直方图是一段时间内数据的精确图像，而高度平衡直方图是数据分布的近似图像，它试图捕捉频繁访问值的细节和其余部分的不均匀部分。当一列包含的不同值不超过254个（12c中为2048个）时，就可以创建一个频率直方图，而高度平衡直方图的精确度要低得多，并且不能真正捕获超过127个频繁值的信息。在本文的其余部分，我将直接使用频率直方图而不使用高度平衡直方图。

### **频率直方图** ###

在我们的示例中，我们只有5个不同列值，而模型数据集仅包含超过1百万行。我可以让Oracle通过收集具有以下方法参数设置的表stats来收集列的直方图：“for columns status size 254”。（注意，虽然我知道只有5个值，我也可以要求最大值，Oracle会发现5是足够的）。如果我还将estimate\_percent设置为100，则在该列的“user\_tab\_histograms ”视图中将出现以下结果：

select
        ep_let             status,
        endpoint_number - ep_lag   ct,
        ep_val
    from
      (
    select
        to_char(endpoint_value,'FMxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')       ep_val,
        chr(
              to_number(
              substr(to_char(endpoint_value,'FMxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),1,2),
              'XX'
              )
        )     ep_let,
        endpoint_number,
        lag(endpoint_number,1,0) over(order by endpoint_number) ep_lag
    from
        user_tab_histograms
    where
        table_name = 'ORDERS'
    and      column_name = 'STATUS'
        )
    order by
        ep_let
    /
    
    S         CT EP_VAL
    - ---------- -------------------------------
    C     529100 43202020202009e7ff86dab2800000
    P        300 50202020202016f62c60904c200000
    R        300 52202020202029a671551080e00000
    S        300 53202020202032fe93cf509b400000
    X     500000 5820202020202b817684cb40800000

我已经展示了一种将endpoint\_value从其内部数字形式转换为等效字符形式的方法，如果你的手边有ASCII码，你会发现端点值的十六进制表示后面附加了很多空格（0x20）-有关转换的实际执行方式的详细信息，请参见：[http://jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/][http_jonathanlewis.wordpress.com_2010_10_05_frequency-histogram-4]请注意，直方图有效地存储为累积频率，我使用lag（）分析函数对其进行了分解，该函数允许您看到Oracle为数据中的每个不同值保存精确的计数。当优化器根据谓词“status='R'”计算基数时，有了这些信息（并假设我的SQL确实使用了文本值），它可以检查这是直方图中的一个值，并报告它在其中记录的计数。在这种特殊情况下，频率直方图是一个巨大的帮助，所以，您可能会问，为什么我们不简单地为应用程序中的每一列（或者可能只是出现在where子句中的列）创建直方图呢？

### **具有频率直方图的威胁** ###

直方图有四个主要的缺陷，我可以用以下要点来标记，然后我将依次检查：

*  它们不能很好地与绑定变量混合。
 *  它们的计算成本很高。
 *  取样时它们可能非常不稳定。
 *  你必须在适当的时候收集它们。

我的观点是，如果查询使用文本值，优化器就能够从直方图中选择正确的条目。如果您对此查询使用了绑定变量，那么优化器将在第一个解析调用上使用“bind peeking”，并仍然生成正确的基数（和执行计划）；但是直到11g中的“自适应游标共享”和12c中的“自适应执行计划”出现，这一个计划（本质上）是您为查询的所有后续执行保留的计划，无论绑定变量的值如何更改。在我的例子中，bind变量本来是可以的，因为对这个表的唯一查询是“status=\{rare value\}”的非常简单的查询，而状态“R”的计划对于“P”和“s”来说是可以的，但通常情况下你不会那么幸运。如果您已经在列上创建了直方图，那么您应该期望在应用程序中做一些事情，让优化器能够很好地处理直方图，这可能意味着在where子句中使用文本，这可能意味着要做一些更微妙的事情，比如编写应用程序代码来检查用户的请求，并从一个简短的列表中选择最合适的SQL在每种情况下运行。假设您已经找到了如何在代码中最好地使用频率直方图的方法，那么在优化器希望读取直方图时，您仍然存在无法确保直方图准确的问题。下面是我告诉它以100%的样本量收集上述数据的频率直方图时运行的SQL Oracle：

select
        substrb(dump(val,16,0,32),1,120) ep,
        cnt
    from     (
        select
            /*+
                no_expand_table(t) index_rs(t)   no_parallel(t)
                no_parallel_index(t) dbms_stats cursor_sharing_exact
                use_weak_name_resl dynamic_sampling(0) no_monitoring
                no_substrb_pad
            */
            max("STATUS") val,
            count(*) cnt
        from
            "TEST_USER"."ORDERS" t
        where
            "STATUS" is not null
        group by
            nlssort("STATUS", 'NLS_SORT = binary')
        )
    order by
        nlssort(val,'NLS_SORT = binary')
    ;

准确的查询将取决于Oracle的版本以及Oracle是否认为该列需要频率直方图或高度平衡直方图，但一般原则是，您将看到一个聚合查询，它将处理大量数据，并且将为您标识的每一列显示查询的变体作为直方图的目标。收集直方图是一项的操代价很高的操作。您可以通过采样而不是计算来降低收集直方图的成本。在执行此操作时，您将看到类似的SQL出现，尽管有一些变化，特别是Oracle经常会将原始行的样本复制到它为此目的创建的全局临时表中，然后对全局termporary表运行查询。这可能导致构建直方图所做的工作要少得多，但它带来了不同的威胁。下面是我给Oracle提供“auto\_sample\_size”选项来收集直方图时原始数据的直方图内容：

S         CT EP_VAL
    - ---------- -------------------------------
    C       2868 43202020202009e7ff86dab2800000
    P          2 50202020202016f62c60904c200000
    S          1 53202020202032fe93cf509b400000
    X       2627 5820202020202b817684cb40800000

如果你把这些数字加起来，你会发现Oracle从表中提取了5498行样本，所以当它估计任何给定值的行数时，它会检查直方图并乘以1030000/5498（分子是根据用户表计算的行数。行数减去用户表的行数。行数减去用户表的行数。），因此，status='S'的估计值为187，status='P'的估计值为375，这两个值都是相当合理的（尤其是与没有直方图的1030000/5相比）。但我们该怎么处理“R”状态呢？–它没有出现在样本中，所以没有出现在直方图中。在这种情况下，优化器只需将直方图中最不受欢迎的值的基数减半，因此基数将计算为94。同样，在这种情况下，这也不算太糟，也不会改变关键的执行计划，但是如果您在Oracle每天采样的行中运气不好，您可以理解，您的执行计划可能会相当随机地改变。你能在这组数据中找出主要的威胁吗？如果Oracle在对数据进行采样时没有发现任何罕见的值，最后显示一个直方图，该直方图显示数据在C和X之间以大约50/50的比例分割，每行大约50万行，会发生什么情况？对status='R'的查询将使用“最不常访问值的一半”–估计约为250000；这正是发生在我的客户身上的情况。stats（正在进行默认的10g过时统计数据的夜间收集）会收集此表上的统计数据，并忽略所有罕见的值，在接下来的24小时内（或者直到下一个stats集合），优化器将决定在一个非常大的表上使用一个tabscan，而不是使用一个非常合适的索引。未能在直方图中捕获关键信息的想法将我们引向直方图的最后一个关键问题——如果在收集统计数据时关键信息永远不存在，会怎么样。想象一下，我的订单处理系统中的罕见值只出现在早上6:00到下午6:00到晚上10:00之间。它们都已经从系统中处理出来了。当默认的stats集合在深夜运行时，表中的唯一值是“C”和“X”，但是当查询在白天运行时，我们感兴趣的唯一值正是收集统计数据时不存在的值。即使是100%的样本，如果你在错误的时间收集数据，你的系统中也可能有部分数据会误导你。您需要对系统有足够的了解，以便知道应用程序代码本身应该对统计数据的质量负责。这可能意味着您编写代码以在一天中的特定时间收集统计数据；这可能意味着您编写代码以直接操作存储的统计数据（我们将在查看高度平衡直方图时查看该策略）。

### **结 论** ###

当数据值的分布高度倾斜时，如果要确保优化器不会因此产生非常糟糕的执行计划，您需要对此做些什么。如果您可以控制应用程序代码特性（如虚拟列或基于函数的索引），则可能有助于处理特殊值；但如果无法更改代码，则可能需要依赖直方图。不过，即使有直方图，绑定变量也很容易导致问题——即使有11g和12c中的新特性，用于自适应游标和自适应执行计划。对频率直方图（更简单的类型）的简要检查向我们展示了它们对于具有少量不同值的列的用处，特别是如果您的SQL使用文本。柱状图，即使是简单的频率柱状图，对于Oracle来说，创建柱状图的成本也很高，除非它对一小部分数据进行采样，然后，如果真正感兴趣的数据是暂时的，并且只占总数的一小部分，柱状图可能会引入不稳定性。事实上，即使您在错误的时间使用了100%的样本，由于优化器对缺失值的处理，产生的直方图仍然可能导致问题。

### **| 译者简介** ###

**汤健·沃趣科技数据库技术专家**

沃趣科技数据库工程师，多年Oracle数据库从业经验，深入理解Oracle数据库结构体系，现主要参与公司一体机产品安装、测试、优化，并负责电信行业数据库以及系统运维。

[http_jonathanlewis.wordpress.com_2010_10_05_frequency-histogram-4]: https://link.zhihu.com/?target=http%3A//jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/