在 IBM Informix 中利用片段级统计和智能统计

关系数据库的日常维护工作之一就是定期更新数据库的统计数据。然而,运行 update statistics 本身就会产生一定的开销。在本文中,您将了解 IBM® Informix® V11.7 中的新特性如何帮助您判断需要进行统计更新的最佳时机,从而显著提高工作效率。IBM Informix V11.7 的另一个增强使您能够对片段表收集片段统计数据,从而进一步改进效率。另外,了解这些增强如何帮助您优化数据库和应用程序。

Nita Dembla, 顾问软件工程师, IBM

Nita Dembla 的照片Nita Dembla 在过去十年中一直在 IBM Informix 开发团队工作。她的专长是 SQL 和优化器。



2011 年 7 月 18 日

简介

IBM Informix V11.7 中的新特性使您能够极大地改善运行 update statistics 的性能。 由于运行 update statistics 现在变得更加快捷、更加高效,因此您可以比以前更频繁地使用这个工具。例如,可以每天而不是每周运行 update statistics 命令。根据系统的工作负载和查询,这将改善查询计划并改进系统性能。这些特性将影响并改善 update statistics 为优化器收集信息的方式。

更新统计数据是最重要的管理任务之一,它确保优化器选用最佳的查询执行计划。对大的系统收集统计数据会占用较长的时间。因此,DA 通常会降低运行 update statistics 的频率来避免系统开销。

本文将首先描述智能统计如何加速 update statistics 在整个系统中的运行,然后将介绍片段级统计,该特性将进一步加快 update statistics 对片段表的运行速度。

智能统计

概述

自从最近一次更新过统计数据后,您是否想过您将要运行的 update statistics 命令是否真正必要?有多少数据被修改?对数据的修改是否对列分布产生影响?

智能统计允许服务器判断现有的统计是否可以准确表示当前数据,或者是否需要对它们进行更新。在 IBM Informix V11.7 之前,当运行 update statistics 命令时,统计数据总是会重新创建。Informix V11.7 为 update statistics 引入了 AUTOMATIC 操作模式。在这种模式下,服务器允许您定义一个数据改变阈值,并且只有在数据自最近一次建立后的变化超过了阈值才会刷新统计数据。由于判断统计是否过时的标准变成了表或片段中的数据修改程度,因此将对表和索引维护一组数据修改计数器(更新、删除、插入(UDI))。这些计数器将在存储层上自动维护,不需要进行任何切换。

这意味着如果您的表中的数据修改低于某个较低的百分比,那么这些表上的 update statistics 命令将不会刷新统计数据,并且服务器将认为这些统计数据足以满足优化的目的。

使用 UDI 计数器跟踪数据变化

在 Informix V11.7 中,更新、删除、插入计数器分别为 8 个字节的大小,并被添加到表和索引分区页面中。这些计数器由表和索引分区中发生的各个 DML 操作实现值累加。

在运行 update statistics 命令时,这些计数器的截图被保存到它们各自的系统目录中。连续运行的 update statistics 将使用这些值来判断现有的统计数据是否过时。

下表中列出了捕捉 UDI 计数器的系统目录中的新列,以及在运行统计数据过程中捕捉的额外信息。

表 1. UPDATE STATISTICS LOW 更新的 V11.7 中的新列
描述
sysfragments.nupdates分离的索引片段上的更新计数
sysfragments.ndeletes分离的索引片段上的删除计数
sysfragments.ninserts分离的索引片段上的插入计数
sysindices.ustlowts索引上的最近一次 update statistics low 的时间戳
sysindices.nupdates附加索引的表更新计数
sysindices.ndeletes附加索引的表删除计数
sysindices.ninserts附加索引的表插入计数
sysindices.ustbuildduration在附加索引上建立 low statistics 所需的时间
表 2. 由 UPDATE STATISTICS MEDIUM/HIGH 更新的 V11.7 上的新列
描述
sysdistrib.nupdates在生成统计数据时对表执行的更新计数
sysdistrib.ndeletes在生成统计数据时对表执行的删除计数
sysdistrib.ninserts在生成统计数据时对表执行的插入计数
sysdistrib.ustbuildduration构建表级别列统计数据所需时间

系统设置和配置

要启用 update statistics 的自动模式,将 ONCONFIG 变量 AUTO_STAT_MODE 设为 1。这是默认设置,并将影响整个系统。如果希望该特性仅限于特定数据库,可以在 sysdbopen 程序中针对这些数据库将会话环境 AUTO_STAT_MODE 设为 1

与智能统计有关的另一个 ONCONFIG 参数是 STATCHANGE。STATCHANGE 是指系统中所有表的修改阈值,采用百分比形式。ONCONFIG 参数值为 0 到 100 之间的整数,默认值为 10%。此外,STATCHANGE 还可以通过使用 SQL 语句会话变量 STATCHANGE x 设置,其中 x 是介于 0 和 100 之间的整数。当在会话中设置 STATCHANGE 后,它将影响该会话中在其上运行 update statistics 的表。

当使用 SET ENVIRONMENT 语句 对 AUTO_STAT_MODE 和 STATCHANGE 设置会话级别值时,它们将覆盖对应的 ONCONFIG 值。

                SET ENVIRONMENT STATCHANGE "5";
                UPDATE STATISTICS LOW FOR TABLE tab1;

在前例中,将修改阈值设置为 5% 来确定是否对 tab1 运行 update statistics low

STATCHANGE 表属性

如果希望在表级别而不是在系统级别控制修改阈值,可以使用表属性 STATCHANGE。该表属性用于针对特定表及其索引设置修改阈值。与 ONCONFIG 和会话环境属性类似,该属性的值也为 0 到 100 之间的整数值。该表属性可用于覆盖系统(ONCONFIG 和/或会话)设置。

可以在创建表时就设置该表属性,也可以在稍后的时间进行修改。STATCHANGE 属性在 CREATE TABLE 和 ALTER TABLE 语句中的语法为: STATCHANGE [integer | AUTO]

可以使用整数值或使用 AUTO 将其设置为系统默认设置。

                CREATE TABLE tab1(cid serial, lname varchar(32), fname varchar(32))
                STATCHANGE 5;
                ALTER TABLE tab1 STATCHANGE AUTO;

从前面的例子中可以看到,智能统计对于所有表和索引都是通用的,不管是否是片段表或索引。

注意:由于统计数据会影响优化器,因此建议将 STATCHANGE 设置为一个合理的值,从而确保查询持续优化。

使用 update statistics 命令简化和/或覆盖智能统计

一旦系统级配置使用 AUTO_STAT_MODE 和 STATCHANGE 在系统或会话级别完成配置后,智能统计就可以确保只针对需要新统计数据的那些表收集统计信息。对于其他的表,不会执行 update statistics 命令。然而,有些情况下,不管表数据发生多大程度的修改,您都希望刷新统计数据。可以在 update statistics 命令中使用 AUTO 和 FORCE 关键字,指定是否需要使用智能统计。

AUTO 意味着使用 AUTOMATIC 模式的 update statistics,从而执行智能统计。

FORCE 意味着不管设置的修改阈值是多少,都将强制重建所有统计数据。

注意:AUTO 和 FORCE 关键字需在 update statistics 的 ALL 模式下使用,并且不会对程序统计产生影响。

该命令将忽略 STATCHANGE 计算并重建所有表 tab1 统计。

                UPDATE STATISTICS HIGH FOR TABLE tab1 FORCE;

AUTO 选项使用 STATCHANGE 计算仅检测和构建过时的统计,例如:

UPDATE STATISTICS LOW FOR TABLE tab1
AUTO;

智能统计的效果

智能统计可以极大地缩短对大型系统更新统计数据所需的时间。

  • update statistics 期间,数据修改小于所设置的 STATCHANGE 的表和索引将被直接跳过。
  • 由于每个索引(片段)都有自己的 UDI 计数器,因此将根据索引(片段)的修改程度判断是否跳过或是重建索引统计。
  • 如果表具有片段级统计,那么将在片段的层面上判断是否执行统计刷新,因此只有片段被修改或出现新片段的情况下重建统计。

可以在运行 UPDATE STATISTICS LOW 之前和之后查询 systablessysindices 目录表中的 ustlowts,查看是否跳过或重建了统计。对于 MEDIUM 和 HIGH 模式,查询 sysdistribsysfragdist 目录中的 constr_time 列。


片段级统计

概念

片段级统计将影响 UPDATE STATISTICS MEDIUM/HIGH 对片段表收集数据和生成列分布的方式。UPDATE STATISTICS LOW 在表和索引上生成的统计将在片段的级别上进行维护。这些统计保存在 sysfragments 中。

在 MEDIUM 或 HIGH 模式下,将创建列数据的柱状图。列柱状图的表现形式为表示列值的箱状或桶状图形。

图 1 显示了如何对片段表生成表级别的统计数据。注意,来自所有片段的数据被一同存储,形成了表级别的统计数据,被编码并存储到系统目录 sysdistrib 中。表级别统计被解码并加载到数据分布缓存中,供优化器使用。

图 1. 显示针对片段表生成表级别统计的样例图
表级别统计

简单来说,片段级统计允许按片段存储列数据分布,并从组成的片段中构建表级别。片段级列统计被表示为迷你箱状图形,因为每一个迷你箱状图形占最终的列箱状图形 的一小部分。

图 2 显示了片段级统计的构建和存储方式。每一个片段的数据都被分别存储,构建迷你箱状分布图并存储到 sysfragdist 目录。最终,将所有这些片段统计合并起来就构成了表级统计数据,并存储到 sysdistrib

图 2. 显示针对片段表生成片段级统计的样例图
片段级统计演示

列的片段统计通常通过 tabidfragidcolno 共同识别。表 3 显示了 sysfragdist 的模式。

表 3. sysfragdist 目录的模式
描述
tabid唯一地识别表代码(= systables.tabid)
fragid唯一地识别片段代码( = sysfragments.partnum)
colno唯一地识别列代码( = syscolumns.colno)
seqno顺序号(用于跨多个行的分布)
modeUPDATE STATISTICS 模式(H = 高,或 M = 中等)
resolution样例在每个箱状图中的平均百分比
confidence预估 MEDIUM 模式样例值等于精确的 HIGH 模式结果的可能性
rowssampled样例的行数
ustbuildduration计算该列的片段分布所需时间
constr_time记录分布时的时间
ustnrows计算分布时片段中的行
minibinsize仅供内部使用
nupdates对片段执行更新的次数
ndeletes对片段执行删除的次数
ninserts对表执行插入的次数
encdist加密的片段分布
dbsnum唯一识别 sbspace 的代码,其中存储了 encdist
version预留,供以后使用

注意事项

您需要在以下两种主要情况下考虑执行片段级统计:

  • 您的数据库模式是周期性的,数据更改主要针对具有特定列值的行。例如,按照事务日期列分段的销售表,每个月都将添加新的行来存储当月的销售数据。
  • 您具有片段表,并经常使用 ALTER FRAGMENT ATTACH/DETACH 添加或从中移除数据。

按照片段打断列分布将允许在片段级别上实现统计管理。例如,可以在片段级别上刷新统计数据,可以向表统计添加或从中移除片段统计。

片段级统计的系统设置

执行下面的操作,将服务器配置为构建和使用片段级统计:

  • 将 ONCONFIG 参数 AUTO_STAT_MODE 设置为 1。这是一个默认设置,将对整个系统产生影响。如果希望仅对特定的数据库使用这一特性,可以在 sysdbopen 程序中针对特定的数据库将会话环境 AUTO_STAT_MODE 设置为 1
  • 确保 ONCONFIG parameter SYSSBSPACENAME 被设置为一个有效的 smartblob 空间,并使用 onspaces –c –S 命令分配该 smartblob。

    例如:onspaces -c -S sbspace -p /work/dbspaces/sbspace -s 100000 -o 0

    列的片段统计所需的空间量取决于其数据类型、片段数和 update statistics 中使用的解析率。列的数据类型决定了一个迷你箱状图所需的实际字节数。暂且将该数值定为 x,分布迷你箱状图可以表示的最大值的数量为 y,计算方式为:

    y = Round(总行数×解析率×0.01 / 片段数 × 100)

    对于具有一致分布的表,迷你箱状图的总数量可以通过以下方式计算:

    z = Round(总行数 / 迷你箱状图的值的数量(y))

    最后,所需的总的空间量的计算方式如下:

    总的空间量 = x * z

    也可以使用 V11.7 中的 Storage Provisioning 特性来确保在需要时自动分配块。

    下表给出了每种数据类型的迷你箱状图的大小,同时还给出了解析率和片段数。


    数据类型迷你箱状图的大小,单位为字节片段数解析率所需的空间,单位为千字节
    整数2221044
    整数2231066
    整数2231.0773
    字符类型最大 278 210556
    字符类型最大 278 310834
    字符类型最大 278 31.08340
    小数(10,2)2721054
    小数(10,2)2731081
    小数(10,2)2731.0810
    小数(32)59210118
    小数(32)59210177
    小数(32)592101770
    Datetime/Interval year to fraction(5)4021080
    Datetime/Interval year to fraction(5)40310120
    Datetime/Interval year to fraction(5)4031.01200
    Datetime/Interval year to fraction3821076
    Datetime/Interval year to fraction38310114
    Datetime/Interval year to fraction3831.01140
    Datetime/Interval year to day2421048
    Datetime/Interval year to day2431072
    Datetime/Interval year to day2431.0720
    日期2221044
    日期2231066
    日期2231.0660
    浮点数3221064
    浮点数3231096
    浮点数3231.0960

    采样的行的数量将影响迷你箱状图包含的值的数量,但是不会直接影响片段统计所需的总空间量。然而,由于数据的倾斜性或一致程度不同,溢出的迷你箱状图的数量以及大小可能会有所不同。

  • 考虑增加逻辑日志来存放 sysfragdist 目录中额外的片段级统计行。因为片段统计比表统计更加细粒度,因此会占用更多的空间。

完成这些操作后,数据库服务器现在可以识别出需要执行片段级统计的表,并在运行 update statistics 时自动执行。这些表通过使用以下规则识别:

  • 表按照表达式、LIST 或 INTERVAL 策略分段。
  • 表包含一百万个行。

此外,可以显式地指出要对哪些表执行片段统计,只需对下一小节介绍的表属性 STATLEVEL 进行设置即可实现这个目的。

STATLEVEL 表属性

STATLEVEL 是一种列分布级别或粒度。STATLEVEL 可通过 CREATE TABLE 和/或 ALTER TABLE 命令指定。

  • TABLE – 在表级别创建分布。
  • FRAGMENT – 在片段级别创建和维护分布。
  • AUTO – 在 Automatic 模式下,应用规则以判断是否需要创建片段级统计,否则默认为表级别分布。

下面的例子在创建时设置了 STATLEVEL,将强制使用片段级统计。

                CREATE TABLE tab1(col1 integer, col2 char(10)
                FRAGMENT BY EXPRESSION
                (col1 >= 0 AND col1 < 1000) IN dbspace1,
                (col1 >= 1000 AND col1 < 2000) IN dbspace2,
                (col1 >= 2000 AND col1 < 3000) IN dbspace3,
                remainder in rootdbs
                STATLEVEL FRAGMENT;

注意:STATLEVEL 的 FRAGMENT 选项可以应用于任何片段表,不管使用何种策略。

此外,在稍后的时间使用以下 ALTER 命令也可以达到同样的效果:

                 ALTER TABLE tab1 STATLEVEL FRAGMENT;

您现在已经完成了全部的操作。在对表运行 UPDATE STATISTICS MEDIUM/HIGH 时,将创建新的片段级统计并保存到 sysfragdist 系统目录中。


统计和 ALTER FRAGMENT

在对启用了片段级统计的片段表上执行 ALTER FRAGMENT ATTACH/DETACH 操作时,服务器将自动刷新有关表的列统计。统计刷新在后台运行,ALTER FRAGMENT 命令在 alter 操作完成后将立即返回。统计刷新可以在 alter 操作完成后开始,或者,如果您正处于一个事务中,可以在提交事务后开始统计刷新。

  • 对于 ATTACH 操作:将对新的片段构建片段统计,并利用所有的片段统计重新构建表级统计。任何列统计过时的现有片段也将在此时重新构建。
  • 对于 DETACH 操作:生成的表的表级统计是使用片段统计重新构建的。

注意:由于采用了智能统计,任何良好的片段统计都将被直接用于合成表级统计。这与在运行 ALTER FRAGMENT 后的表上执行完整的统计刷新是不同的。

执行统计刷新的后台任务称为 refresh_table_stats,在 sysadmin 数据库的 ph_task 表中定义。如果发现任何错误,将在 online.log 输出错误。


结束语

通过使用智能统计特性,您可以对服务器进行配置,从而在运行 update statistics 命令时自动检测并刷新已过时的统计数据。智能统计特性避免了不必要的统计更新操作,因此缩短了系统的统计维护时间。您还可以根据系统需求在不同的级别灵活配置,包括实例、会话和表。通过采用片段级统计,服务器可以在片段级别创建和管理列统计。这将允许服务器仅针对特定的片段刷新统计数据。服务器还可以在执行 ALTER FRAGMENT ATTACH 或 DETACH 后有效地重新构建表列统计。即使您配置并启用了智能统计,update statistics 命令的 AUTO 和 FORCE 增强允许您指定是否对过时的统计或全部统计进行刷新。

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=733489
ArticleTitle=在 IBM Informix 中利用片段级统计和智能统计
publish-date=07182011