informix系统管理维护手册

Informix系统管理维护手册

监控实例活动

IDS 实例是指Informix 共享内存、Informix 处理器、Informix 数据库以及分配给Informix 的物理设备。以下是部分需要监控的最重要的实例活动。

操作方式

第一个也是最重要的实例活动当然是IDS 的操作方式。IDS 运行正常还是有问题，或是已当机了？onstat -p 命令捕获了IDS 的当前操作方式，如下所示：

也可以查询sysmaster 数据库中的sysprofile 表来获取同样的统计信息。

输出的第一行显示了当前的IDS 操作方式。本例中，Informix 引擎是“On-Line”。总共有六种操作方式，其中三种特别重要：Off-Line、Quiescent 和On-Line。Off-Line 方式表明IDS 当前没有在运行。Quiescent 方式表明IDS 正在以单用户方式运行，在这种方式下，只有DBA 可以进行管理和维护工作。On-Line 方式表明IDS 正在正常运行，所有用户都可以连接到数据库服务器，并可以执行各种数据库操作。在大多数情况下，IDS 应该始终处于On-Line 方式。如果因为种种原因IDS 当机了或处于Off-Line 方式，那么上面的命令将显

除了当前的操作方式以外，上面的输出还提供了一些重要的Informix 实例性能统计信息。两个%cache 字段表明IDS 目前使用内存高速缓存的效率。第一个%cache 字段显示了读高速缓存比例的百分比，而第二个则显示了写高速缓存比例。读高速缓存比例和写高速缓存比例会随应用程序及正在操作的数据的类型和大小而动态变化。但读高速缓存比例和写高速缓存比例一般都应该在80 到90 个百分点之间。这是十分保守的数字，应该根据具体环境加以调整。如果这些比例始终低于80%，那么您需要考虑提高Informix 配置文件中

BUFFERS 参数的值，以获取较高的读写高速缓存比例。较低的读写高速缓存比例表明IDS 正在进行的磁盘读写操作比它应该进行的要多得多，这会大大降低数据库引擎的整体性能。输出的seqscan 字段表明自数据库启动或联机以来执行了多少次顺序扫描。如果这个数字相当大，比如说超过了100000，并且还在不断增加，那么这可能表明性能有问题，当系统处于OLTP 环境时更是如此。因而，您需要做进一步的调查以搞清楚出现过多顺序扫描的根源。在本文的后面我们将更详细地讨论这一问题。

ovlock 字段表明IDS 在使用了最大数量的锁之后尝试过再使用锁的次数。如果该数字非零，那么您可能需要提高配置文件中LOCKS 参数的值。ovbuf 字段表明IDS 在使用了最大数量的缓冲区之后尝试过再使用缓冲区的次数。如果该数字很大，比如说超过100000，那么您需要提高BUFFERS 参数，以便用户在需要从磁盘访问数据时不必等待缓冲区。这会缩短响应时间，因而可以改善整体性能。我们还需要检查与LRU 有关的参数，将它们的值调整到较低的bufwait。请参考Administrator's Guide for Informix Dynamic Server 以获取更多详细信息。

另一组重要字段包括ixda-RA、idx-RA、da-RA 及RA-pgused 。这些字段组合在一起表明IDS 使用Informix 预读机制的效率。预读是这样一种操作：它在顺序扫描或索引读期间提前将数据页的数目从磁盘读入内存。理想情况是，预读的页数（即ixda-RA、idx-RA 和da-RA 之和）等于顺序扫描或索引读期间所使用的页数（即RA-pgused ）。这表明预读的页百分之百地用于顺序扫描和索引读。如果二者之间存在显著的差异，比如正负差值达到10000 以上，那么IDS 目前就没有很有效地使用预读，而您可能需要调优您的预读参数（即RA_PAGES 和RA_THRESHOLD）以获取更好的性能。请参考Administrator's Guide for Informix Dynamic Server（本文称为Administrator's Guide）以获取有关如何调优这些参数的详细信息。

消息日志

消息日志也称为联机日志。它含有各种有关关键实例活动的信息，如检查点的时间和持续时间、实例启动和停止、备份和恢复状态、逻辑日志备份状态以及对主要配置参数的更改。消息日志还包含关键的错误（Informix 称之为断言失败），如磁盘I/O 错误、镜像错误、当机块、数据完整性错误以及共享内存错误等等。在发生断言失败时，消息日志通常会将我们引向有关断言失败的（“af.xxx”）文件，该文件会记录在数据库引擎当机时有关实例活动的更详细信息，还会就如何解决这一问题给我们提供一些建议。以下内容摘自消息日志：

上面的输出告诉我们：某个Informix 虚拟处理器终止了，并毁坏了数据库引擎。当用户“omcadmin”登录到名为nvlsys 的机器并执行了一些数据库操作（大部分是未正确执行的SQL 查询），该机器上发生了这一错误。文件/var/tmp/af.35acfeel 记录了出错时有关数据库引擎状态的详细统计信息。

块状态

块是物理存储设备。它们应该始终联机。如果有任何块当机了，那么这表明数据遭到毁坏，需要立即引起注意。onstat -d 命令监控当前的块状态，以下是该命令的输出：

Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 7 days 23:35:56 -- 1654784 Kbytes

Dbspaces

address number flags fchunk nchunks flags owner name

6510c7d0 1 0x1 1 1 N informix rootdbs 65866468 2 0x1 2 4 N informix airgen_idx_dbs 658665b0 3 0x1 3 3 N informix spare

658666f8 4 0x1 4 5 N informix logs 65866840 5 0x1 5 2 N informix pm1 65866988 6 0x1 7 1 N informix pm_gen

65866ad0 7 0x2001 8 1 N T informix temp_dbspace2 65866c18 8 0x1 10 2 N informix pm2

65866d60 9 0x1 11 3 N informix airgen_main_dbs 65866ea8 10 0x1 14 1 N informix mso_meta 65867018 11 0x1 16 2 N informix pm3 65867160 12 0x2001 18 1 N T informix temp_dbspace3 658672a8 13 0x2001 20 1 N T informix temp_dbspace1 658673f0 14 0x1 25 2 N informix pm4 65867538 15 0x2001 29 1 N T informix temp_dbspace4 15 active, 2047 maximum

Chunks

address chk/dbs offset size free bpages flags pathname

6510c918 1 1 0 63069 51985 PO- /usr/informix/dblink 6514b5f0 2 2 65000 750000 1 PO- /usr/informix/dblink 6514b760 3 3 815000 60000 59747 PO- /usr/informix/dblink 6514b8d0 4 4 875000 125000 4947 PO- /usr/informix/dblink 6514ba40 5 5 0 1000000 299290 PO- /usr/informix/dblink1 6514bbb0 6 2 0 1000000 207877 PO- /usr/informix/dblink2 6514bd20 7 6 0 200000 179043 PO- /usr/informix/dblink3 6514be90 8 7 200000 250000 249939 PO- /usr/informix/dblink3 6510ca88 9 3 450000 250000 249997 PO- /usr/informix/dblink3 6510cbf8 10 8 0 1000000 299086 PO- /usr/informix/dblink4 6510cd68 11 9 0 1000000 4 PO- /usr/informix/dblink5 6513c830 12 9 0 500000 10 PO- /usr/informix/dblink6 6513c9a0 13 8 500000 300000 299997 PO- /usr/informix/dblink6 6513cb10 14 10 800000 200000 27596 PO- /usr/informix/dblink6 6513cc80 15 9 0 1000000 782331 PO- /usr/informix/dblink7 6513cdf0 16 11 0 1000000 296827 PO- /usr/informix/dblink8 65865018 17 4 0 400000 9997 PO- /usr/informix/dblink9 65865188 18 12 400000 250000 249947 PO- /usr/informix/dblink9 658652f8 19 5 0 300000 299997 PO- /usr/informix/dblink10 65865468 20 13 300000 250000 249947 PO- /usr/informix/dblink10 658655d8 21 4 550000 150000 14997 PO- /usr/informix/dblink10

上面的输出包含两部分。第一部分列出了所有的dbspace，第二部分则列出了所有的块。在块（Chunk）部分中，我们需要特别注意flags 字段。该字段的第一个字符表明块是主（P）块还是镜像（M）块。第二个字符表明块的当前状态，是联机（O）还是脱机（D）。由于O 和D 看起来很相象，尤其是您匆匆一瞥时，因此您可能想将结果用管道输入到grep PD ，即onstat -d |grep PD ，以确保您不会遗漏任何当机块。如果有任何主块当机，那么您需要立即从备份磁带执行冷或暖恢复，以确保数据完整性。我们也可以查询sysmaster 数据库中的syschunks 和sysdbspaces 表来获取类似的统计信息。

检查点

检查点是使磁盘上的页与共享内存缓冲池中的页同步的过程。在检查点期间，IDS 阻止用户线程进入临界会话，并阻止所有的事务活动。因此，如果检查点持续时间过长，那么用户可能会经历系统挂起。在存在几千个事务并且响应时间至关重要的OLTP 环境中，情况尤其如此。正如上面所解释的那样，可以通过查看消息日志来监控检查点持续时间，但更好更

如果检查点持续时间始终超过10 秒，那么您可能需要减少LRU_MIN_DIRTY 和LRU_MAX_DIRTY 配置参数的值以获取更短的检查点持续时间。同样，如果onstat -F 的输出显示极高的块写（比如高于10000），并且这个数字还在不断增加，那么这可能表明出现了以下两个问题中的一个：要么检查点时间间隔太短，从而在检查点之间清除程序没有足

够的时间将所有经过修改的缓冲区写入磁盘，要么AIO VP 太少，无法在检查点期间共享繁重的磁盘写。这样，您需要重新检查CKPINTVL、LRUS、CLEANERS 和NUMAIOVPS 配置参数的设置，并相应地增加它们的值。我们可能还需要查看onstat -F 的输出来作为确定那些参数值的参考。有关如何调优那些参数的详细信息，请参考Administrator's Guide。dbspace使用情况

Informix 数据库管理员要不断了解各个dbspace 中的空间，这一点十分重要。如果某个dbspace 缺少空间或把空间用完了，那么IDS 会碰到麻烦。各种问题都可能出现：无法导入任何数据库，无法创建任何表和索引，甚至无法对任何表和索引执行插入和更新操作。这一点对于生产数据库至关重要。我们需要监控每个dbspace 的增长，以便能够对这些问题采取更主动的方法。下面的脚本报告了各个dbspace 的当前空间使用情况，并计算其百分比。

dbspace 的磁盘使用情况接近90% 时向该dbspace 分配额外的磁盘空间；本例中，我们需要特别注意llog dbspace，并且可能的话，就给它分配更多空间，以防止它把空间用完。dbspace I/O

Dbsapce I/O 是由磁盘读和磁盘写来衡量的。如果某些dbspace 有繁重的磁盘读写操作，而另外一些dbspace 几乎不进行任何读写操作，那么系统可能会出现一些磁盘I/O 瓶颈。平衡得较好的dbspace I/O 将减轻系统磁盘I/O 负载，从而会改善系统的整体性能。以下脚本将显示各个dbspace 的当前I/O 统计信息：

输出如下所示：

我们的目标是要使所有的dbspace 都有平衡的磁盘读写操作。在大多数情况下，这是不现实的，但上面的输出至少让您对dbspace I/O 的分配方式有了一个概念，可以帮助您标识“最热门的”dbspace —那些磁盘读写最多的dbspace。如果有些dbspace 的磁盘读写操作相当繁忙而另外一些的读写操作则相当空闲，那么您可能需要为Informix 引擎调整甚至重新安排物理磁盘布局。我们可以使用onstat -D 和onstat -g ioq 获得类似的信息，前者显示各个块的磁盘读和写，而后者显示磁盘I/O 等待队列信息。

您可以通过查询sysmaster 数据库中的sysptprof 表来进一步标识哪些表具有最多的磁盘

根据从这个查询获得的输出，您可能需要在dbspace 间移动一些表以使磁盘I/O 平衡得更好。

共享内存段

太多的虚拟共享内存段（通常多于三个）表明：最初的虚拟共享内存段太小，数据库引擎必

须不断分配额外的虚拟共享内存段。这反过来影响了IDS 性能，并且最终会损害系统的性

如果输出显示虚拟共享内存段多于三个，那么您需要提高配置文件中SHMVERSIZE 参数的值。其思想是，让IDS 在初始化时分配足够的虚拟共享内存，以便在用户登录到系统并执行数据库操作时无需分配更多的虚拟共享内存。您可能还想使用UNIX? ipcs 命令来查看Informix 共享内存的大小。有关如何计算IDS 虚拟共享内存段大小的详细信息操作系统的整体性能

由于Informix 数据库引擎总是安装在某个操作系统（主要是UNIX）上，以准确地监控或评估IDS 性能，因此我们需要将操作系统的行为作为一个整体来考虑，在数据库引擎驻留在非专用数据库服务器上时尤其要这样考虑。如果IDS 占用了太多RAM（例如，如果系统有512MB RAM，而IDS 占用了400MB 或更多作为其共享内存），那么当用户执行内存密集型操作时，操作系统可能会经历频繁的交换和挂起。当内存不足时，系统必须将一些数据页从内存交换到磁盘，以便为新数据腾出更多空间。如果系统内存不足，那么CPU 可能也会遭殃。有不少UNIX 实用程序可以监控操作系统CPU 和内存的整体利用率。以下

上面的输出包含两部分。第一部分为您汇总了操作系统的CPU 和内存的整体使用情况，第

二部分则提供了有关每个处理器的详细信息。其它实用程序（如vmstat、iostat、ps -ef 和sar ）在收集操作系统当前的性能统计信息方面也很有用。vmstat 显示目前操作系统交换了多少内存；iostat 和sar 显示了当前在所有物理磁盘中磁盘I/O 的分配；而ps -ef 打印出当前各个处理器的登录时间、CPU 及内存使用情况的详细信息。此外也有许多图形工具可用，这些工具使您能够绘制操作系统资源利用率和性能的动态变化。

监控数据库活动

对数据库活动进行监控的目的在于确保每个数据库时刻都将其能力发挥到了极致。这意味着：您必须留意潜在的性能问题，确定其根源并将其消灭在萌芽状态。以下是要留意的几个方面。

表扩展块

扩展块是一块物理上连续的页。然而，如果一个表有多个扩展块，那就不能保证这些扩展块是连续的；扩展块可能会散布在表所驻留的整个dbspace 上。物理页的连续性对于性能十分重要。当数据页连续时，访问磁盘数据所用的时间就最短，而数据库也能连续地读取行。如果表有太多扩展块，那么那些扩展块极有可能相互交错。这极大地影响了性能，因为当您检索某个表的数据时，磁头需要对属于该表的多个非连续扩展块进行寻道，而不是对具有连续物理页的一个大扩展块进行寻道。这会显著地降低磁盘寻道速度。下面的脚本检测具有多

输出如下所示：

如果除了大型分段表以外，任何表的扩展块超过了10 个，那么您应该考虑重新构建这些表以合并扩展块。对于较大的数据库或者大小设置不是很好的数据库，我们可能还会关注扩展块的最大数目，或者会担心针对索引的32GB 限制。有关如何对表估计和分配数据块大小的详细信息，

索引层

索引的层数也可能会对性能产生不利影响。索引层越多，IDS 到达索引叶节点所需的探测也就越多。而且，如果叶节点被拆分或合并，那么整个索引对这一变化进行调整将要花费更多的时间。例如，如果索引只有两层，那么只需要调整两层，但如果索引有四层，那么相应地就需要对所有四层进行调整。用于这一调整的时间当然也就长得多。在OLTP 环境中会进行频繁的插入、删除和更新，这些操作会导致不断地对索引进行拆分和合并，因此上述问题也就格外明显。下面的脚本标识每个索引的层数：

输出如下所示：

如果哪个索引超过了4 层，您可能就需要考虑删除和重新构建该索引，从而合并其层，以获取更好的性能。

索引唯一性

索引的重复程度很高会严重地影响更新和删除的性能。假定表customer 的customer_type 列上有一个索引，而可能的customer_type 代码只有五种。如果这个表有一百万行，那么可能有200000 行具有相同的customer_type 代码。B-树存储键值，其后跟一个指向每个物理行的指针列表。在必须删除或更新任何键值时，问题出现了。IDS 必须找遍所有的重复内容，直到找到要删除或更新的正确键为止！

下面的脚本用来标识重复程度很高的索引：

在理想情况下，nunique列中所出现的全部值都应该与nrow列中的全部值相等，即索引中的每个键都是唯一的。根据行数（上面的nrows列）和唯一键数（上面的nunique列），我们可以计算每个索引唯一性的百分率：

百分率越高，索引的唯一性就越高。为了避免因索引重复程度很高而引起的性能瓶颈，您可以使用复合索引来替换原来的索引，复合索引结合了重复程度很高的列与唯一性比较高的列。利用上面的示例，您可以将主键列customer_id 添加到原来的索引，将它变成一个复合列（例如，“create index index_name on customer ( customer_type , customer_id )”）。

顺序扫描

对表进行顺序存取有时会降低性能，因为数据库引擎必须扫描整个表以选取满足查询条件的行。如果表很小，比如说几百行，那么顺序存取不会对性能造成什么影响；因为当数据库引擎第一次扫描它时，该表会驻留于内存中，而当数据库引擎下一次扫描它时，可以直接从内存检索该表中的所有数据。这实际上是使用顺序扫描的有效方式。但如果表很大，比如说超过了100000 行，那么重复的顺序扫描会对性能造成致命的影响。下面的脚本将标识具有多重顺序扫描的表：

从上面的输出可以看出airgen_10_0 表的顺序扫描数很高。如果它是一个具有几千甚至几百万行的大表，那么您可能需要考虑向该表添加一些索引，或者考虑使用程序伪指令来强制内部查询优化器为访问该表中的数据选择索引而不是顺序扫描。

监控会话活动

有关会话活动的统计信息在确定潜在的性能问题及故障诊断方面很有用。使用本文前面讨论的监控工具，我们可以收集哪些会话活动统计信息呢？

常规会话统计信息

sysmaster 数据库中的syssessions 表存储各个会话的常规信息，如登录名、登录时间、会话所登录的主机机器、操作系统的进程标识和当前状态等等。可以使用以下查询来查询该表来获取全部此类信息：

s_tate 是一个指出会话的当前活动状态的十六进制数，以获取对这其中每个活动状态的详细描述。login_time 是一个指出会话登录时间的整数，使用C 程序很容易将它转换成常规时间格式。可以向Informix 技术支持请求以获取该C 程序。

sysmaster 数据库中的syssesprof 表提供了各会话的更多详细信息。使用以下查询，您可以

输出如下所示：

access 字段显示了会话命中数据库的次数。locksheld 显示各会话正使用多少锁。seqscans 表明各会话使用顺序扫描访问数据的次数；如果该数字太高，比如说高于100000，那么您可能要质疑会话是否曾经使用过索引来检索数据，而且要更仔细地检查其查询执行计划以确定它是否最佳。total_sorts 和dsksorts 表明各会话使用内存进行排序操作的效率。您可能

该百分数越高，排序操作效率也就越高。您可以将syssessions 表与syssesprof 表连接以进一步确定每个会话的用户名和主机机器名，以便了解哪些地方可能会出现数据库和系统问

您也可以将syssessions 表与syslocks 表连接以获取关于锁的更多详细信息，如哪个会话当前锁定了哪个数据库中的哪个表，以此来帮助您确定各用户之间潜在的锁冲突：

输出类似于：

问，那么您可以方便地确定该锁的所有者，并根据用户的优先级发出onmode -z sid 命令来杀死会话，然后释放该锁；sid 这个编号是从上面输出中的owner 字段中获取的；请注意，只有用户“Informix”可以执行该命令。

查询统计信息

查询统计信息对于故障诊断和查询优化至关重要。onstat -g sql sid 命令捕获当前会话的查询及相关统计信息；其中sid 是会话标识，它可以通过硬编码手工插入，也可以通过UNIX shell 脚本动态插入。例如，如果您想知道某个会话正在执行什么查询，那么您可能要首先使用命令onstat -g ses 来查明其会话标识，然后将该标识插入上面的命令。例如，如果您想监控的会话标识为28953，那么您可以使用onstat -g sql 28953 来捕获其当前查询。

输出如下所示：

输出的第一部分给出了关于正在执行的查询的一些常规统计信息，如对哪个数据库执行查询、其隔离级别以及锁方式。最有趣的两个字段是SQL error 和ISAM error 。如果这些字段非零，就表明查询出了一些问题，没有正确地执行。可以使用Informix finderr 实用程序来查明到底是什么问题，随后在解决了该问题之后，杀死该会话并重新执行该查询。“Current SQL statement”部分显示了正在执行的查询的完整SQL 语法。它对于诊断问题查询和查询优化非常有帮助。如果您发现了该查询的一些问题，如响应时间太长，消耗了太多的系统CPU 或内存，那么您可以按其显示状态为该查询制作一个副本，以便稍后研究和分析之用。您随后可以对Informix dbaccess 实用程序运行同一个查询，以获取该查询性能方面更多详细的统计信息（如其执行计划和连接策略），从而确定问题的根源。根据研究所收集的统计信息，您可以进一步优化查询以获取更好的性能。“Last parsed SQL statement”部分显示了在内存中已经过解析的查询。由于在内存中已经解析过的查询在大多数情况下都是正在执行的查询，因此这一部分与前一部分在大多数时候都是相同的。

调优Informix SQL

调优标准

调优标准基本上是依据个人的业务需求而定的。一般，主要涉及系统和数据库的性能。那么您又该如何配置系统和数据库性能呢？有多方面的考虑，但是最关键的就是响应时间和资源利用率。响应时间指的是用户等待他们的请求（即SQL 查询）得到完成的时间。资源利用率指的是执行SQL 查询时，CPU、内存和磁盘等系统资源的使用。

通常，如果完成调优过程之后，调优之后的SQL 查询的响应时间变短了，而系统资源的利用率降低了，那么您就可以充满信心地推断：您已经调优该SQL 查询，并获得了更高的效率。如何测量SQL 查询的响应时间和资源利用率呢？Unix 和Informix 提供了一些工具和实用程序，帮助我们科学、定量地测量响应时间和资源利用率

调优工具

Unix工具

Time和timex

您可以使用time 实用程序来报告一些系统操作的响应时间，例如文件传送、数据库的查询执行以及其他活动。下面的实例展示了如何使用time 实用程序来测量一个简单数据库查询

的响应时间：

上面输出的最后一部分给出了详细的操作时间统计数据，该操作在本例中就是数据库查询：

?“real”字段告诉您从查询开始到结束时所经过的时间。

?“user”字段告诉您用户处理器为该操作而占用的CPU 时间总和。

?“sys”字段告诉您整个系统占用的CPU 时间总和。

我们最感兴趣的是“real”字段；该字段表示操作的响应时间。让我们仔细查看一下上面的这个例子。通过输出，我们可以确定该查询的响应时间是0.09 秒。

Timex 只是time 实用程序的另一个变种，它将以更易读的格式显示时间。下面是同一查询

Vmstat

vmstat 实用程序深入挖掘系统并报告系统资源使用的统计数据，主要涉及运行的处理器、虚拟内存、磁盘I/O、中断和CPU。它还显示一行系统重启或启动以后的虚拟内存活动的摘要信息。下面是一个输出示例：

vmstat 实用程序接收两个整数参数：time interval 和count。Time interval 指定vmstat 刷新的间隔，而count 指定它将刷新的最大次数。如果未指定任何参数，vmstat 就只会列出系统的前一次统计数据，而且不会进行刷新。本例中，它所提供的统计数据是不准确的，也不是最新的。如果指定了interval，vmstat 将立即反复按照最新的间隔总结系统活动的概要信息。如果给定了count，vmstat 就会重复获取系统的统计数据，直到达到了所指定的count 次数。

输出中最让我们感兴趣的是“r”、“po”和“id”字段。

?“r”字段告诉我们系统就绪队列中有多少任务在等待要执行的资源。

?“po”字段告诉我们当前内存中有多少页面被换出。如果该数字极其庞大，并持续增

长，这通常表示物理存储器或RAM 不足，您可能需要安装更多存储器。

?“id”字段告诉我们当前使用了多少系统CPU 资源。

这些字段一起将让您较好地了解当前系统资源的使用情况。

Informix工具

Informix 提供的用来收集详细SQL 查询计划和执行统计数据的最全面的工具是SET EXPLAIN 实用程序。该实用程序将生成一个名为sqexplain.out 的文件，并详细记录查询的每个执行步骤。此外，它还提供估算的查询成本，并预计查询结果。通过检查SET EXPLAIN 输出文件，您可以确定是否可以采取措施来提高该查询的性能。以下示例展示了一个极其复杂查询的set explain 输出：

A VOID_FULL ( daphn )

DIRECTIVES NOT FOLLOWED:

Estimated Cost: 8

Estimated # of Rows Returned: 1

1) root.idachn: SEQUENTIAL SCAN

2) root.daphn: INDEX PA TH

(1) Index Keys: dap_hn_inst (Serial, fragments: ALL)

Lower Index Filter: root.daphn.dap_hn_inst = root.idachn.idac_hn_inst

NESTED LOOP JOIN

3) root.vsgw_hn: SEQUENTIAL SCAN

NESTED LOOP JOIN

4) root.omchn: INDEX PA TH

Filters: root.vsgw_hn.vsgw_hn_inst = root.omchn.omc_hn_inst

(1) Index Keys: omc_hn_inst (Serial, fragments: ALL)

Lower Index Filter: root.idachn.idac_hn_inst = oot.omchn.omc_hn_inst

NESTED LOOP JOIN--+

5) root.ihlrhn: INDEX PA TH

(1) Index Keys: hlr_hn_inst (Serial, fragments: ALL)

Lower Index Filter: root.ihlrhn.hlr_hn_inst = root.omchn.omc_hn_inst

NESTED LOOP JOIN

可将以上输出分成三个部分：

?第一部分展示了查询语法。

?第二部分展示了该查询的估算成本。

?第三部分详细地解释了所执行查询的每一步。

我们最感兴趣的是第二和第三部分。估算成本是优化器用来比较查询计划的成本单元。这些单元不直接转换成时间；它们表示的是典型磁盘访问的相对时间。优化器将选择该查询计划，因为其执行的估算成本是所有被估计划中最低的。与具有较低估算成本的运行相比，具有较高估算成本的查询的运行时间通常更长一些。第三部分对于查询调优是至关重要的，因为它提供了大量有用的信息，例如查询所用的数据访问方法和连接方法。以上示例展示了如何使用连续扫描和索引来进行数据检索，以及如何使用嵌套循环连接方法来连接所有表。稍后我将在本文中详细讨论这一点。

该实用程序易于使用。如果需要知道某个SQL 查询的详细查询执行计划，只需在最初的查询之前添加如下所示SET EXPLAIN ON 语句即可：

然后，Informix 服务器将在用户home 目录中生成一个名为sqexplain.out 的文件，正如上面谈到的，其中将记录详细的查询执行计划及其成本。该文件是累积的，换言之，如果SET EXPLAIN ON 语句之后有多个SQL 查询，每个查询的执行计划及其成本都会被追加到文件中，直到将之删除。同样，对于存储过程，您需要对原始的存储过程执行UPDATE STA TISTICS，以获取详细的执行计划，因为在更新统计数据时，存储过程只可以更新它们的查询执行计划。例如，如果您需要查看存储过程dap_int 的详细执行计划，就需要进行下列操作：

从版本9.3 开始，该实用程序得到了较好的提高；您可以获取详细的查询执行计划，而不必执行该查询。这使您可以在真正的生产环境中获取查询执行计划。要使用这项新功能，需要在SET EXPLAIN ON 语句中使用关键字A VOID_EXECUTE，如下所示：

通用方法

那么我们如何在SQL 查询的调优中应用那些工具呢？不同的人可能有不同的方法，但是，一般应按照下列方法和步骤进行：

?收集关于原始SQL查询的统计数据。在这一步中，需要使用上面讨论的工具来获

取该查询的统计数据：其响应时间、详细的执行计划和成本，以便稍后进行深入分

析。

?分析统计数据。在这一步中，需要挖掘上面所收集的统计数据，并仔细查看查询执

行计划。如上所述，性能是查询调优中主要关心的问题。在检查查询计划时，需要

考虑影响性能的所有因素：访问方法、连接方法、子查询、表和索引区段、表和索

引分段等。稍后我将在本文的第2 部分中逐个详细地讨论这些因素。

?设置测试环境。这是一个极其重要的步骤。就硬件和软件配置而言，应将测试环境

设置为与运行查询的生产系统完全相同或极其相似。例如，如果生产机器有6个

400HM的CPU，那么测试机器也应该有6个400HM 的CPU；否则，随后的测试

将是无效且不可靠的。请记住，所有那些查询最终都将在生产中运行。

?修改并测试新的查询。这是调优中的一个主要步骤，同时也是最冗长乏味的一个步

骤。一次修改原始查询的一个地方，并进行测试，以查看是否提高了性能（减少了

响应时间）。记录测试细节，例如您所做的修改、响应时间和执行计划。进行了修

改之后，如果查询性能没有优于您原始查询的，就撤销修改。测试必须是有效且可

靠的；换言之，测试必须是可重复的。例如，如果对同一查询进行两次相同的测试，第一次输出了一个极佳的响应时间（比如说10 秒），但是第二次，响应时间就增

加到30 秒，那么您的测试就是不可重复的，因为响应时间的差异过大。您需要重

新检查测试过程，并识别那两次测试之间的差异。如果测试是可重复的，那么测试

结果之间的差异应该是极小的。

?分析测试结果。在分析测试结果时，我们需要检查测试结果的有效性和可靠性。我

们需要检查硬件、软件、工作负载以及其他所有因素，以确保测试结果是有效且可

靠的。

?在生产系统中实现改进。在实现之前，您需要进行最新的详细检查，并确保新的查

询在生产中不会导致任何问题

优化器及其角色

与Oracle和SQL Server 等其他关系数据库管理系统一样，Informix 也有内部优化器，负责选择最佳的查询执行计划。分析了SQL 查询之后，优化器将通过分析磁盘I/O 和CPU 成本等因素，考虑所有执行查询的可能方法。然后，它会用由下至上、宽度优先（bottom up, breadth-first）的搜索策略，同时构造所有可行的计划。换言之，优化器首先将构造所有可能的连接计划，然后删除所有花费较高的冗余对（redundant pair），这些冗余对是包含相同表以及产生与另一连接对相同的行集的连接对。如果某查询使用了附加的表，那么优化器就将每个剩余对连接一个新表，以形成所有可能的连接三元组，排除代价更为昂贵的冗余三元组，如此一来就连接了每个附加的表。当生成了非冗余的可能连接组合集时，优化器就选择看上去具有最低执行成本的计划。例如，优化器必须确定是否应使用索引。如果该查询包含了连接，那么优化器就必须确定连接计划（hash、sort merge 或nested loop），以及评估或连接表的次序。

优化器将根据在每个表中检索的行数来评估查询成本。被评估的行数则基于WHERE 子句里所使用的每个条件表达式的选择性。优化器使用UPDATE STATISTICS 所生成的数据分布信息来计算查询中筛选器的选择性。然而在缺乏数据分布信息的情况下，优化器将基于表索引计算不同类型筛选器的选择性。例如，如果索引列包含字面值和NULL 值，那么其选择性就等于索引中不同的键的数目。关于在缺乏数据分布的情况下，优化器用以计算选择性的详细表，请参阅Performance Guide 的第10 章。但是，使用该方式计算的选择性不如使用数据分布计算的选择性准确。

因此，选择性估算的准确性显然基于您运行UPDA TE STATISTICS 的频率。如果频繁地运行UPDATE STA TISTICS，优化器就会更准确地计算出选择性，因为每次运行UPDATE STA TISTICS 时，都会更新数据分布，除了运行带有low 选项的UPDATE STA TISTIC 之外。

当优化器创建查询计划时，它将使用下列系统目录信息：

?从最新的UPDATE STATISTICS 语句起，表中的行数。

?将哪一列限定为惟一的。

?当请求UPDATE STA TISTICS 语句中带有MEDIUM 或HIGH 关键字时，列值

的分布情况。

?包含行数据的磁盘页的数目。

?存在于表上的索引，包括它们索引的列，哪些是升序或降序的，以及哪些是群集的。

?索引结构的深度（衡量执行索引查找所需的工作量）。

?索引项占用的磁盘页数目。

?索引中的惟一项数目，可用于估算等式筛选器所返回的行数。

?索引列中第二大和第二小的键值。

优化器的行为受三个关键因素的影响：Informix 配置文件中OPTCOMPIND 参数的值、查询命令和内部统计数据的准确性。

OPTCOMPIND 参数

OPTCOMPIND 是一个环境变量，或者是Informix 配置文件中的一个参数。优化器用其值来选择数据访问方法。其值是0、1 和 2 这三个中的一个，分别表示下列意思：

?如果将其值设置为0，那么当存在合适的索引时，优化器就选择索引扫描而非表扫

描，甚至不考虑估算成本。

?如果将其值设置为1，那么当事务隔离模式不是可重复读（Repeatable Read）时，

优化器的行为就与值为0 时一样。如果事务隔离模式是可重复读（Repeatable

Read），那么优化器会将其选择完全基于估算成本。

?如果将其值设置为2，优化器就会使用估算成本来确定执行计划，而不管事务隔离

模式如何。

您可以将OPTCOMPIND 设置为环境变量，也可以设置为配置文件中的参数，但是，将之设置为参数将优先执行。

查询命令

影响优化器的另一种方式就是使用查询命令。查询命令是SQL 查询中的提示，指示优化器如何执行查询。一共有 4 种类型的查询命令，如下所示：

?访问计划指示，强制优化器使用指定的访问方法进行数据检索，要么是连续扫描，

要么是索引扫描。

?连接次序指示，强制优化器按照指定次序连接表。

?连接计划指示，强制优化器使用指定的连接方法来连接查询中的表，要么是嵌套循

环连接、分类合并连接，要么是动态哈希连接。

?目标指示，强制优化器使用指定的规则来返回查询结果。

内部统计数据

通过内部统计数据，我指的是系统目录中的统计数据，优化器可以确定最低成本的查询执行计划。为了确保优化器选择最佳的查询计划，使内部统计数据保持为最新并且最准确的十分重要。数据库服务器将初始化表、索引、存储过程和触发器等数据库对象的统计配置文件，并在创建数据库表时，将数据分布置于系统目录中，但不自动更新那些统计数据。

要使统计配置文件保持最新，您需要定期运行UPDATE STA TISTICS；否则，您系统的统计配置文件可能无法反映您系统的当前状态，而优化器则可能无法在众多查询执行计划中做出正确选择。UPADTE STA TISTICS 有三种运行模式，一般每次执行修改了大量表数据的大量批作业之后，以及每次向表添加索引之后，您都需要运行UPDA TE STATISTICS。关于如何执行UPDATE STA TISTICS 的详细信息，请参阅IBM Informix Dynamic Server Administrator's Guide, Version 9.4。其经验法则就是：越频繁地运行UPDATE STATISTICS，系统的统计配置文件将越新、越准确，而优化器则越可能选择最佳的查询执行计划。

虽然如上面所谈论的，优化器的行为受OPTCOMPIND 和查询命令的影响，但优化器通常是基于下列指导方针来选择查询计划：

?如果查询从一个表检索大量数据，优化器就不会使用索引。例如，如果您公司的客

户非常均匀地分布在所有50 个州中，而您需要检索除了纽约之外的每个州的客户

信息，那么您需要执行下列查询：

优化器将立刻检测您可能要检索的表中的98% 的数据，它认为连续读或扫描表要比遍历索引（以及随后的数据页）更加高效，然后它将检索相关数据。