Informix调优点滴

影响Informix数据库性能的主要参数

影响CPU使用率的配置参数和环境变量

Online 配置文件onconfig中的下列参数对CPU的利用率有明显的影响:

?NUMCPUVPS

?SINGLE_CPU_VP

?MULTIPROCESSOR

?AFF_NPROCS

?AFF_SPROC

?NUMAIOVPS

?OPTCOMPAND

?NETTYPE

NUMCPUVPS、MULTIPROCESSOR和SINGL_CPU_VP NUMCPUVPS参数规定了Online 开始启动的CPU VP的数量。分配的CPU VP的个数不要超过可以为它们服务的CPU的个数。

?对于单处理器的计算机系统，Informix 建议使用一个CPU VP。

?对于有4个以上CPU，主要用做数据库服务器的多处理器系统，Informix 建议设置NUMCPUVPS的值等于处理器总数减一。

?对于双处理器系统，运行两个CPU VP可能会改善性能。这需要监控操作系统的CPU使用情况。可以使用操作系统命令sar 或vmstat。

如果运行多个CPU VP，应将MULTIPROCESSOR 设置为1，当设置MULTIPROCESSOR为1时，Online 以对应于多处理器的方式执行锁定。否则，设置该参数为0。

注意：如果设置SINGLE_CPU_VP参数为，则NUMCPUVPS 参数的值也必须是1，如果后者大于1，Online就不能初始化并显示下面的错误信息：

Cannot have 'SINGLE_CPU_VP' now-zero and 'NUMCPUVPS' greater than 1

AFF_NPROCS 和AFF_SPROC

在支持Online和客户应用的系统上，可以通过操作系统把应用连接到某些特定的CPU。这样做可以有效地保留剩余的CPU给Online CPU VP使用，它们是用AFF--NPROCES和AFF_SPROC配置参数连接到剩余CPU的。

AFF_NPROCS指定了连接到Online的CPU VP上的CPU的个数。连接一个CPU VP到一个CPU 会引起该CPU VP在这个CPU上的排它性运行。

AFF_SPROC指定了Online把CPU VP连接到CPU上时所启动的CPU。AFF_NPROCS规定了计算机上可以帮定CPU VP的CPU的数目。NUMCPUVPS参数指定了Online将启动的CPU VP的数目，AFF_SPROC参数指定了Online连接第一个CPU序号。例如，某个Online 系统所在的硬件平台有8个CPU，AFF_NPROCS设置为8（即可用于帮定CPU VP的CPU有8个），NUMCPUVPS设置为3，AFF_SPROC设置为5，则3个CPU VP需要帮定到CPU上，是从第五个CPU开始，帮定到第五、六、七个CPU上。需

要注意的是，AFF_SPROC的取值是在0和（AFF_NPROCS - NUMCPUVPS + 1)这两个值之间的，不能大于后者。

NUMAIOVPS

参数NUMAIOVPS指定最初产生的AIO VP的数目。如果所在的操作系统不支持核心异步I/O（KAIO），Online使用AIP VP来处理所有数据库I/O请求。推荐的AIP VP数目取决于Online 使用的硬盘个数。如果所在操作系统不支持或没有使用KAIO，则Informix建议对包含数据库表的每一个磁盘分配一个AIO VP。可以对Online 频繁访问的每六块增加额外的AIO VP。

如果所在的操作系统使用KAIO VP，CPU VP将直接向操作系统发出原始的I/O请求。在这种情况下，可以只配置一个AIO VP，此时AIO VP只处理文件系统方式的chunk。如果文件系统方式的chunk有增加时，可以增大AIO VP 的数目。

分配AIO VP的目的是要分配足够的AIO VP以便I/O请求队列的长度保持很短，即队列中保持尽可能少的I/O请求。

OPTCOMPIND

OPTCOMPIND参数帮组优化程序为应用选择合适的访问方法。

?如果OPTCOMPIND等于0，优化程序给予现存索引优先权，即使在表扫描比较快时。

?如果OPTCOMPIND设置为1，给定查询的隔离级设置为Repeatable Read 时，优化程序才使用索引。

?如果OPTCOMPIND等于2，优化程序选择基于开销选择查询方式。，即使表扫描可以临时锁定整个表。

NETTYPE

NETTYPE参数为Online实例支持的每个连接类型配置轮询线索。如果sqlhosts文件中支持一个以上的接口或协议的连接，就必须对每个连接类型规定独立的NETTYPE参数。也即，每中与数据库服务器名字有关的连接类型都需要单独指定一个NETTYPE参数。

每个用NETTYPE表项配置或动态加入的轮询线索在不同的VP上运行，轮询线索可以在两类VP上运行：NET VP和CPU VP。为得到最佳性能，Informix 建议使用NETTYPE表项为CPU VP类只分配一个轮询线索，将其余轮询线索轮询线索分配给NET VP。分配给任何一种连接类型的轮询线索不得超过NUMCPUVPS的取值。

单CPU计算机上每个轮询线索的最佳连接个数不超过300，多CPU机上可多达350个。但一个轮询线索最多支持1，024甚至更多的连接。

NETTYPE的配置格式如下：

NETTYPE connection_type，poll_threads，c_per_t，vp_class

?connection_type 标识轮询线索分配的连接协议。

?poll_threads 是分配给该连接类型的轮询线索数目。对任何连接类型，这个值不能超过NUMCPUVPS值。

?c_per_t 是每个轮询线索的连接数目。可以用如下公式计算这个值：

c_per_t = connections / poll_threads

connections 是所希望指定的连接类型支持的最大连接数。对于共享内存连接（ipcshm)，该值应该加倍以获得最好的性能。

?vp_class 是可运行轮询线索的VP类。如果CPU VP上只运行一个轮询线索，那么指定为CPU VP。为了达到最好性能，当要求多个轮询线索时应该指定为NET VP。

如果c_per_t的值超过了350，而当前连接的轮询线索数小于NUMCPUVPS，可以增加轮询线索数目，但不能超过NUMCPUVPS，然后重新计算c_per_t的取值。

注意：每个ipcshm连接需要一个信号量。当c_per_t的值很大时，对于某些操作系统要相应增加信号量。

如何监控系统CPU的使用情况：

1. 使用UNIX的监控工具SAR或VMSTAT来监控CPU的使用情况。

例：sar 5 10

%usr %sys %wio %idle

10:06:22 34 1 0 65

10:06:27 34 2 0 64

10:06:32 34 1 0 65

10:06:37 17 1 0 82

10:06:47 1 1 0 98

连续监控%idle来确认CPU没有超载。如果%sys的值很大则可能应用有问题。

2. 监控CPU VP的方法

可以通过该监控看出CPU忙占用的时间(隔60秒分别监控结果）。如果非常忙，则需要增加CPU VP。

onstat -g rea

Ready threads

tid tcb rstcb prty status vp-class name

如果有大量的线索在等待队列中，则说明需要增加CPU VP。

影响内存使用效率的Online配置参数

?SHMVIRTSIZE

?SHMADD

?BUFFERS

?RESIDENT

?STACKSIZE

?LOCKS

?LOGBUFF

?PHYSBUFF

SHMVIRSIZE

SHMVIRTSIZE参数规定了初始分配给Online的共享内存的虚拟区的大小。共享存储器的虚拟区存储与会话、请求有关的数据及其它信息。虽然Online按处理大型查询或高峰负荷的需要增加共享内存给虚拟区，但共享内存的分配增加事务处理的时间，Informix建议设置SHMVIRTSIZE以提供一个满足一般日常操作需要的虚拟接口。

SHMADD

SHMADD参数规定Online自动加到虚拟区的共享内存增量的大小。在决定该值的大小时有些折中因素。增加共享内存要占用CPU周期：每次的增加量越大，增加次数就越少，留给其它的进程的内存也越少。通常采用大增加量，但当内存负荷很重时，少量增加使其他程序更好的共享内存资源。Informix 有如下建议：

日志文件。缓冲区的大小决定了它被添满的频率，从而决定了它必须被刷新到硬盘上的逻辑文件中的频率。

PHYSBUFF

参数PHYSBUFF指定为两个用来暂时保存将被修改的数据页的缓冲区分别保留的共享内存的数量。缓冲区的大小决定了它被添满的频率，从而也决定了它被写到硬盘上的物理日志的频率。

如何监控内存使用情况：

1. 使用ONSTAT -G SEG命令监控共享内存的SEGMENTS。

onstat -g seg

Segment Summary

(resident segments are not locked)

id key addr size ovhd class blkused blkfree

这里三行分别代表了驻留内存段（class为R)、虚拟内存段(class为V)、消息内存段(class为M)。blkused和blkfree 分别代表使用空间和空闲空间。如果虚拟内存段的blkused 频繁增加，则需要将SHMVIRTSIZE和SHMADD

相应调大，调整后重新启动Online。

2. 使用ONSTAT -P

1）ovlock指出分配的locks的不足量，如果该值持续增长，则需要增大参数LOCKS的值。。

2）ovbuf指出分配的buffers的不足量，如果该值持续增长，则需要增大参数BUFFERS的值。。

3）lockwaits/lockreqs * 100应该小于1%，如果这个计算值比较高，则应有如下考虑：。

?是否用了太多的page level locks。如果是，可以考虑用row level locks。?考虑用了table level lock的应用是否可以用其它类型的lock。

?是否有太多的isolation设置为Repeatable Read 和Cursor Stability。确定是否可以使用更多的Dirty Read来替代。

4）bufreads %cached的值指出buffer读的百分比，该值建议大于95%，否则增大BUFFERS，bufwrits %cached的值指出buffer写的百分比，该值建议大于85%，但太大如大于97%则可以将BUFFERS 相应减少些。。

影响I/O的配置参数

?CKPTINTVL

?PHYSFILE

?CLEANERS

?LRUS

?LRU_MAX_DIRTY

?LRU_MIN_DIRTY

CKPINTVL，PHYSFILE

CKPINTVL参数指定检查点之间的时间间隔。当检查点间隔到了，则系统执行检查点操作。但如果这期间的所有数据物理上是一致的，Online可以跳过检查点操作。另外，一旦物理日志（PHYSFILE）的75%已满，检查点也会发生。

通过设置CKPTINTVL为长时间间隔，可以利用物理日志容量来触发基于实际数据库活动而不是任意时间单位的检查点操作。但是，使用长检查点间隔回增加失败事件之后的恢复时间。

LRUS、LRU_MAX_DIRTY和LRU_MIN_DIRTY

LRUS参数指示共享内存缓冲池中设置的最近最少使用（LRU）队列数目。配置较多的LRU队列将允许有更多的页清除器操作，并减少每个LRU队列的大小。对于单CPU系统，Informix建议设置LRUS参数为最小值4。对于多CPU系统，Informix建议设置LRUS为最小值4和NUMCPUVPS的取值之中较大的一个。

可以用LRUS和LRU_MAX_DIRTY及LRU_MIN_DIRTY来控制在满的检查点之间页被刷新到磁盘的频度。在某些情况下，通过设置这些参数，使得在检查点发生时需要刷新的修改的页数量很少，可以达到高的吞吐量；这样，检查点的主要功能是更新物理日志和逻辑日志文件。

CLEANERS

CLEANERS参数指定执行的页清除线索的数目。对于少于20磁盘的系统，Informix推荐CLEANERS的取值为磁盘的个数。对于20至100的磁盘的系统，Informix推荐每两个磁盘分配一个CLEANERS。对于更多的磁盘系统，Informix推荐每四个磁盘分配一个CLEANERS。

如何监控系统的I/O情况

使用onstat -g ioq，onstat -g iof, onstat -d 监控磁盘的负载情况：

如果aio队列很大，则可增加一个AIO VP。

如果某些class 为gfd 所对应的len 和maxlen 非常大，则需要考虑你的数据分布是否合理，记住这些gfd 所对应的hvp-id的值，再通过onstat -g iof 查出是那几个设备:

onstat -g iof

gfd pathname totalops dskread dskwriteio/s

这里gfd的值等于onstat -g ioq 中那几个hvp-id的值所对应的pathname 就是I/O负载较大的设备。用onstat -d可确定是哪个dbspace。则可以考虑重新分配磁盘

查询优化技术

1．合理使用索引

索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：

●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。

●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。

●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

●如果待排序的列有多个，可以在这些列上建立复合索引(compound index)。

●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

2．避免或简化排序

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：

●索引中不包括一个或几个待排序的列；

●group by或order by子句中列的次序与索引的次序不一样；

●排序的列来自不同的表。

为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表(尽管有时可能影响表的规范化，但相对于效率的提高是值得的)。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。

3．消除对大型表行数据的顺序存取

在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。

还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：

SELECT ＊FROM orders WHERE (customer_num=104 AND

order_num>1001) OR order_num=1008

虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：

SELECT ＊FROM orders WHERE customer_num=104 AND

order_num>1001

UNION

SELECT ＊FROM orders WHERE order_num=1008

这样就能利用索引路径处理查询。

4．避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

5．避免困难的正规表达式

MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：

SELECT ＊FROM customer WHERE zipcode LIKE “98_ _ _”

即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊FROM customer WHERE

zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。另外，还要避免非开始的子串。例如语句：

SELECT ＊FROM customer WHERE zipcode[2，3] >“80”

在where子句中采用了非开始子串，因而这个语句也不会使用索引。

6．使用临时表加速查询

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：

SELECT https://www.wendangku.net/doc/5d8163681.html,，rcvbles.balance，……other columns

FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

AND cust.postcode>“98000”

ORDER BY https://www.wendangku.net/doc/5d8163681.html,

如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：

SELECT https://www.wendangku.net/doc/5d8163681.html,，rcvbles.balance，……other columns

FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

ORDER BY https://www.wendangku.net/doc/5d8163681.html,

INTO TEMP cust_with_balance

然后以下面的方式在临时表中查询：

SELECT ＊FROM cust_with_balance

WHERE postcode>“98000”

临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。

注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

7．用排序来取代非顺序存取

非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。

有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。

下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括3个表，模式如下所示：

1．part表

零件号零件描述其他列

(part_num) (part_desc) (other column)

102，032 Seageat 30G disk ……

500，049 Novel 10M network card ……

……

2．vendor表

厂商号厂商名其他列

(vendor _num) (vendor_name) (other column)

910，257 Seageat Corp ……

523，045 IBM Corp ……

……

3．parven表

零件号厂商号零件数量

(part_num) (vendor_num) (part_amount)

102，032 910，257 3,450,000

234，423 321，001 4，000，000

……

下面的查询将在这些表上定期运行，并产生关于所有零件数量的报表：

SELECT part_desc，vendor_name，part_amount

FROM part，vendor，parven

WHERE part.part_num=parven.part_num

AND parven.vendor_num = vendor.vendor_num

ORDER BY part.part_num

如果不建立索引，上述查询代码的开销将十分巨大。为此，我们在零件号和厂商号上建立索引。索引的建立避免了在嵌套中反复扫描。关于表与索引的统计信息如下：

表(table) 行数量

(row

size)

行尺寸

(Row count)

每页行数量

(Rows/Pages)

数据页数量

(Data

Pages)

part 150 10，000 25 400 Vendor 150 1，000 25 40 Parven 13 15，000 300 50

索引(Indexes) 键尺寸

(Key

Size)

每页键数量

(Keys/Page)

页面数量

(Leaf Pages)

part 4 500 20

Vendor 4 500 2

Parven 8 250 60

看起来是个相对简单的3表连接，但是其查询开销是很大的。通过查看系统表可以看到，在part_num上和vendor_num上有簇索引，因此索引是按照物理顺序存放的。parven表没有特定的存放次序。这些表的大小说明从缓冲页中非顺序存取的成功率很小。此语句的优化查询规划是：首先从part中顺序读取400页，然后再对parven表非顺序存取1万次，每次2页(一个索引页、一个数据页)，总计2万个磁盘页，最后对vendor表非顺序存取1.5万次，合3万个磁盘页。可以看出在这个索引好的连接上花费的磁盘存取为5.04万次。

实际上，我们可以通过使用临时表分3个步骤来提高查询效率：

1．从parven表中按vendor_num的次序读数据：

SELECT part_num，vendor_num，price

FROM parven

ORDER BY vendor_num

INTO temp pv_by_vn

这个语句顺序读parven(50页)，写一个临时表(50页)，并排序。假定排序的开销为200页，总共是300页。

2．把临时表和vendor表连接，把结果输出到一个临时表，并按part_num 排序：

SELECT pv_by_vn，＊vendor.vendor_num

FROM pv_by_vn，vendor

WHERE pv_by_vn.vendor_num=vendor.vendor_num

ORDER BY pv_by_vn.part_num

INTO TMP pvvn_by_pn

DROP TABLE pv_by_vn

这个查询读取pv_by_vn(50页)，它通过索引存取vendor表1.5万次，但由于按vendor_num次序排列，实际上只是通过索引顺序地读vendor表(40＋2=42页)，输出的表每页约95行，共160页。写并存取这些页引发5＊160=800次的读写，索引共读写892页。

3．把输出和part连接得到最后的结果：

SELECT pvvn_by_pn.＊，part.part_desc

FROM pvvn_by_pn，part

WHERE pvvn_by_pn.part_num=part.part_num

DROP TABLE pvvn_by_pn

这样，查询顺序地读pvvn_by_pn(160页)，通过索引读part表1.5万次，由于建有索引，所以实际上进行1772次磁盘读写，优化比例为30∶1。笔者在Informix Dynamic Sever上做同样的实验，发现在时间耗费上的优化比例为5∶1(如果增加数据量，比例可能会更大)。

小结

20％的代码用去了80％的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。我们的优化要抓住关键问题，对于数据库应用程序来说，重点在于SQL的执行效率。查询优化的重点环节是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。

人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分别进行总结：

为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过１秒的均表示为（< 1秒）。

测试环境

?主机：HP LH II

?主频：330MHZ

?内存：128兆

?操作系统：Operserver5.0.4

?数据库：Sybase11.0.3

一、不合理的索引设计

例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：

1.在DATE上建有一个非群集索引

select count(*) from record where date > '19991201' and date < '19991214'and amount > 2000 (25秒)

select date,sum(amount) from record group by date (55秒)

select count(*) from record where date > '19990901' and place in ('BJ','SH') (27秒)

分析：

date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。

2.在DATE上的一个群集索引

select count(*) from record where date > '19991201' and date < '19991214' and amount > 2000 （14秒）

select date,sum(amount) from record group by date （28秒）

select count(*) from record where date > '19990901' and place in ('BJ','SH')（14秒）

分析：

在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。

3.在PLACE，DATE，AMOUNT上的组合索引

select count(*) from record where date > '19991201' and date < '19991214' and amount > 2000 （26秒）

select date,sum(amount) from record group by date （27秒）select count(*) from record where date > '19990901' and place in ('BJ, 'SH')（< 1秒）

分析：

这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL 没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。4.在DATE，PLACE，AMOUNT上的组合索引

select count(*) from record where date > '19991201' and date < '19991214' and amount > 2000(< 1秒)

select date,sum(amount) from record group by date （11秒）select count(*) from record where date > '19990901' and place in ('BJ','SH')（< 1秒）

分析：

这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。

5.总结：

缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：

?有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by 、group by发生的列，可考虑建立群集索引；

?经常同时存取多列，且每列都含有重复值可考虑建立组合索引；

?组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

二、不充份的连接条件：

例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：

select sum(a.amount) from account a, card b where a.card_no = b.card_no（20秒）

将SQL改为：

select sum(a.amount) from account a, card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）

分析：

在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：

外层表account上的22541页+（外层表account的191122行*内层

表card上对应外层表第一行所要查找的3页）=595907 次I/O

在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account上的索引，其I/O次数可由以下公式估算为：

可见，只有充份的连接条件，真正的最佳方案才会被执行。

总结：

1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。

2.查看执行方案的方法-- 用set explain on，打开explain选项，就可以看到连接顺序、使用何种索引的信息。

三、不可优化的where子句

1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：select * from record where substring(card_no,1,4)='5378'(13秒) select * from record where amount/30< 1000（11秒）

select * from record where

convert(char(10),date,112)='19991201'（10秒）

分析：

where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL 重写成下面这样：

select * from record where card_no like '5378%'（< 1秒）

select * from record where amount < 1000*30（< 1秒）

select * from record where date= '1999/12/01' （< 1秒）

你会发现SQL明显快起来！

2.例：表STUFF有200000行，ID_NO上有非群集索引，请看下面这个SQL：

select count(*) from stuff where id_no in('0','1') （23秒）

分析：

where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据explain）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。

实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：

得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程：

create proc count_stuff as

declare @a int

declare @b int

declare @c int

declare @d char(10)

begin

select @a=count(*) from stuff where id_no='0'

select @b=count(*) from stuff where id_no='1'

end

select @c=@a+@b

select @d=convert(char(10),@c)

print @d

直接算出结果，执行时间同上面一样快！

总结：

可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。

1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含

索引。

3.要善于使用存储过程，它使SQL变得更加灵活和高效。

从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

1. 监控IDS性能

简介

不少书籍和文章都对Informix Dynamic Server（IDS）及其体系结构和性能调优进行了详尽论述，但专门讨论监控这一主题的却很少。但在IDS管理中有效的监控却至关重要。它能帮助我们收集系统和数据库性能方面有价值的统计信息，还能帮助我们很早就确定问题，以便我们能够在故障诊断和性能调优方面取得主动。在成功地安装和配置Informix Dynamic Server并实现了Informix数据库以后，对Informix Dynamic Server进行监控就成为了数据库管理员的头等大事。

本文将详细讨论如何在各个级别有效地监控Informix Dynamic Server，同时会就确定Informix引擎和数据库问题提供一些常规技巧。文章将同时涵盖故障诊断和性能调优这两个方面。

监控工具

Informix提供了两个主要的工具来监控系统和数据库性能：

?onstat实用程序

?sysmaster数据库中众多的系统监控接口（SMI）表，该数据库是在IDS 首次初始化时自动创建的

onstat实用程序和SMI 表都通过检查IDS 共享内存活动来监控IDS 性能，但它们给出那些统计信息的方式却有所不同。onstat 实用程序总是以固定的方式给出统计信息，而使用SMI 表则允许您以更有意义、更可读的格式重新组织那些统计信息。

需要注意的一点是，无论是通过onstat 收集还是在SMI 表中收集，这些统计信息都是从系统重新引导或IDS 初始化开始累积而来的。因此，对于那些统计信息我们必须格外小心，并且总是要考虑IDS 运行时间。例如，服务器运行超过一个月所累积的100000 条bufwait 与一天所累积的100000 条bufwait 就完全不同。要获取当前的统计信息，我们必须执行onstat -z 以清除旧值。

Informix 还提供了一个图形监控工具- onperf。onperf 收集IDS 服务器的性能统计信息，并将它们描绘成度量值。它还可以将那些统计信息保存为文本文件以供日后分析。请参考Performance Guide for Informix Dynamic Server 以获取更多有关onperf 实用程序的详细信息。

IDS 活动可以分为三类：

?实例活动

?数据库活动

?会话活动

通过使用上面讨论的工具，我们可以有效地监控所有那些IDS 活动。

监控实例活动

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

操作方式

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

Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 01:01:17 --

1654784 Kbytes

Profile

dskreads pagreads bufreads %cached dskwrits pagwrits

bufwrits %cached

86923 101304 3116565 97.21 1651 15022 26196 93.70

isamtot open start read write rewrite delete commit rollbk 2585879 118500 286631 1032967 1972 914 2 2 0

gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs

0 0 0 0 0 0 0

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes

0 0 0 478.11 71.63 13 26

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans

3502 0 7065882 0 0 0 1266 11280

ixda-RA idx-RA da-RA RA-pgsused lchwaits

10120 51 69387 79557 482

我们也可以查询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 方式，那么上面的命令将显示下面的消息：

Shared memory not initialized for INFORMIXSERVER

'cassprod_shm'

在这种情况下，您需要检查消息日志或Informix 联机日志，以进一步确定问题的根源（请参阅消息日志）。

除了当前的操作方式以外，上面的输出还提供了一些重要的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"）文件，该文件会记录在数据库引擎当机时有关实例活动的更详细信息，还会就如何解决这一问题给我们提供一些建议。以下内容摘自消息日志：

00:57:53

00:57:53 Assert Failed: Unexpected virtual processor termination, pid =586, exit = 0x9

00:57:53 Who: Session(13709, omcadmin@nvlsys, 6538, 654709000)

Thread(13740, sqlexec, 2704a558, 1)

00:57:53 Results: Fatal Internal Error requires system shutdown

00:57:53 Action: Restart OnLine

00:57:53 See Also: /var/tmp/af.35acfee1

00:57:53 Stack for thread: 13740 sqlexec

上面的输出告诉我们：某个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

dbspace s

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_dbspace 2

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_dbspace 3

658672a8 13 0x2001 20 1 N T informix temp_dbspace 1

658673f0 14 0x1 25 2 N informix pm4

65867538 15 0x2001 29 1 N T informix temp_dbspace 4

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

65865748 22 4 0 350000 4997 PO- /usr/informix/dblink11 658658b8 23 11 350000 300000 299997 PO-

/usr/informix/dblink11

65865a28 24 2 0 1000000 999997 PO- /usr/informix/dblink12 65865b98 25 14 0 1000000 299014 PO- /usr/informix/dblink13 65865d08 26 2 0 750000 749997 PO- /usr/informix/dblink14 65865e78 27 4 750000 250000 39997 PO-

/usr/informix/dblink14

65866018 28 14 0 300000 299997 PO- /usr/informix/dblink15 65866188 29 15 300000 250000 249939 PO-

/usr/informix/dblink15

658662f8 30 3 550000 50000 49997 PO- /usr/informix/dblink15 30 active, 2047 maximum

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

检查点

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

查看消息日志来监控检查点持续时间，但更好更快的方法是使用onstat -m命令。以下是该命令的样本输出：

15:25:10 Checkpoint Completed: duration was 0 seconds.

15:25:10 Checkpoint loguniq 231, logpos 0x1bb2018

15:35:30 Checkpoint Completed: duration was 19 seconds.

15:35:30 Checkpoint loguniq 231, logpos 0x31b9018

Fri Dec 20 11:48:02 2002

11:48:02 Checkpoint Completed: duration was 7 seconds.

11:48:02 Checkpoint loguniq 231, logpos 0x32e5018

14:27:37 Logical Log 231 Complete.

14:27:40 Process exited with return code 142: /bin/sh /bin/sh -c /usr/informix/etc/log_full.sh 2 23 "Logical Log 231 Complete." "Logical Log 231

Complete."

14:28:24 Checkpoint Completed: duration was 22 seconds.

14:28:24 Checkpoint loguniq 232, logpos 0x458018

14:38:46 Checkpoint Completed: duration was 7 seconds.

14:38:46 Checkpoint loguniq 232, logpos 0x10f5018

如果检查点持续时间始终超过10 秒，那么您可能需要减少

LRU_MIN_DIRTY 和LRU_MAX_DIRTY 配置参数的值以获取更短的检查点持续时间。同样，如果onstat -F 的输出显示极高的块写（比如高于10000），并且这个数字还在不断增加，那么这可能表明出现了以下两个问题中的一个：要么检查点时间间隔太短，从而在检查点之间清除程序没有足够的时间将所有经过修改的缓冲区写入磁盘，要么AIO VP 太少，无法在检查点期间共享繁重的磁盘写。这样，您需要重新检查CKPINTVL、LRUS、CLEANERS 和NUMAIOVPS 配置参数的设置，并相应地增加它们的值。我们可能还需要查看onstat -F 的输出来作为确定那些参数值的参考。

dbspace 使用情况

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

select name dbspace , sum(chksize) allocated, sum(nfree) free, round(((sum(chksize) - sum(nfree))/sum(chksize))*100) pcused from sysdbspaces d, syschunks c

where d.dbsnum = c.dbsnum

group by name

order by name

输出如下所示：