人们在使用 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 次数可由以下公式估算为：

外层表 card 上的 1944 页 + （外层表 card 的 7896 行 * 内层表 account 上对应外层表每一行所要查找的 4 页） = 33528 次 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 子句分开：

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

得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有 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.     监控8s 数据库性能

 

简介

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

监控工具

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

·   onstat 实用程序

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

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

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

8s 数据库活动可以分为三类：

·   实例活动

·   数据库活动

·   会话活动

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

监控实例活动

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

操作方式

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

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 表来获取同样的统计信息。

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

Shared memory not initialized for GBASEDBTSERVER 'cassprod_shm'

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

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

输出的 seqscan 字段表明自数据库启动或联机以来执行了多少次顺序扫描。如果这个数字相当大，比如说超过了 100000 ，并且还在不断增加，那么这可能表明性能有问题，当系统处于 OLTP 环境时更是如此。因而，您需要做进一步的调查以搞清楚出现过多顺序扫描的根源。在本文的后面我们将更详细地讨论这一问题。

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

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

消息日志

消息日志也称为联机日志。它含有各种有关关键实例活动的信息，如检查点的时间和持续时间、实例启动和停止、备份和恢复状态、逻辑日志备份状态以及对主要配置参数的更改。消息日志还包含关键的错误（ 8s 称之为断言失败），如磁盘 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

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

状态块

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

检查点

检查点是使磁盘上的页与共享内存缓冲池中的页同步的过程。在检查点期间， 8s 数据库 阻止用户线程进入临界会话，并阻止所有的事务活动。因此，如果检查点持续时间过长，那么用户可能会经历系统挂起。在存在几千个事务并且响应时间至关重要的 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/gbasedbt/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 使用情况

8s 数据库管理员要不断了解各个 dbspace 中的空间，这一点十分重要。如果某个 dbspace 缺少空间或把空间用完了，那么 8s 数据库 会碰到麻烦。各种问题都可能出现：无法导入任何数据库，无法创建任何表和索引，甚至无法对任何表和索引执行插入和更新操作。这一点对于生产数据库至关重要。我们需要监控每个 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

输出如下所示：

dbspace allocated free pcused
airgen_idx_dbs 1000000 763405 24
airgen_main_dbs 1500000 295789 80
llog 1000000 9947 99
rootdbs 50000 36220 28
temp1 250000 249947 0
temp2 250000 249939 0

上面的输出有助于我们确定哪些 dbspace 已把空间用完了。要取得主动，请考虑在某个 dbspace 的磁盘使用情况接近 90% 时向该 dbspace 分配额外的磁盘空间；本例中，我们需要特别注意 llog dbspace ，并且可能的话，就给它分配更多空间，以防止它把空间用完。

dbspace I/O

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

select d.name, fname[15,25] path_name, sum(pagesread) diskreads,
sum(pageswritten) diskwrites
from syschkio c, syschunks k, sysdbspaces d
where d.dbsnum = k.dbsnum
and k.chknum = c.chknum
group by 1, 2
order by 1

输出如下所示：

name path_name diskreads diskwrites
airgen_idx_dbs uild95/ltmp 3672 7964
airgen_main_dbs uild95/ltmp 13545 32903
llog uild95/ltmp 19 51633
rootdbs uild95/ltmp 211 43117
temp1 uild95/ltmp 3015 3122
temp2 uild95/ltmp 3218 3317

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

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

select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites)
diskwrites
from sysptprof
order by 3 desc, 4 desc

输出类似于：

dbsname tabname diskreads diskwrites
airgen_10_0 fanout_param 84567 3094
airgen_cm_db sysindices 78381 0
airgen_10_0 ne_nmo_i 75819 5
airgen_10_0 ne_nmo 75440 297
airgen_cm_db sysprocbody 62610 28322
airgen_10_0 systables 37342 466
airgen_10_0 syscolumns 34539 4609
airgen_10_0 457_484 32838 42
airgen_10_0 453_480 30009 1
airgen_10_5_old syscolumns 29531 4550
airgen_10_5 syscolumns 28824 4552
airgen_10_0 456_483 25448 14
airgen_10_0 458_485 23278 177
airgen_10_5_old 452_483

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

共享内存段

太多的虚拟共享内存段（通常多于三个）表明：最初的虚拟共享内存段太小，数据库引擎必须不断分配额外的虚拟共享内存段。这反过来影响了 8s 数据库 性能，并且最终会损害系统的性能。 onstat -g seg 命令显示了 8s 数据库引擎目前拥有多少共享内存段：

15:49:33 -- 205824 Kbytes
Segment Summary:
id key addr size ovhd class blkused blkfree
0 1381386241 a000000 177209344 220688 R 42984 280
1 1381386242 14900000 8388608 856 V 2048 0
2 1381386243 15100000 1048576 632 M 164 92
3 1381386244 15200000 8388608 856 V 2048 0
4 1381386245 15a00000 8388608 856 V 2008 40
5 1381386246 16200000 8388608 856 V 50 1998
Total: - - 211812352 - - 49302 2410
(* segment locked in memory)

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

操作系统的整体性能

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

load averages: 1.12, 1.02, 1.07 10:17:30
123 processes: 120 sleeping, 1 zombie, 2 on cpu
CPU states: 70.5% idle, 26.5% user, 2.8% kernel, 0.3% iowait, 0.0% swap
Memory: 3072M real, 76M free, 588M swap in use, 440M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
28349 omcadmin 4 0 0 86M 55M cpu10 970:25 6.85% CS_App.prt
17782 gbasedbt 5 30 -10 1631M 1594M sleep 50.0H 4.66% oninit.exe
17784 gbasedbt 5 59 -10 1631M 1594M sleep 102.9H 4.12% oninit.exe
17786 gbasedbt 5 59 -10 1631M 1591M sleep 25.5H 2.53% oninit.exe
571 root 1 58 0 361M 129M sleep 19.0H 1.36% em_mis
17785 gbasedbt 5 59 -10 1631M 1592M sleep 57.8H 1.05% oninit.exe
5470 omcadmin 1 0 0 1960K 1408K cpu15 0:00 0.26% top

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

监控数据库活动

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

表扩展块

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

select t.tabname, count(*) num_ext
from sysmaster:sysextents e, airgen:systables t
where e.tabname=t.tabname
and dbsname = "airgen"
and t.tabname not like "sys%"
group by 1
having count(*) > 1
order by 2 desc

输出如下所示：

tabname num_ext
nmoattrclassmap 14
attrclass 11
networkmoclass 3
fanout_param 3
fanout_comp 2
ne_nmo 2
nenmoclassmap 2
join_map 2

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

索引层

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

select idxname, levels from sysindexes
order by 2 desc

输出如下所示：

idxname levels
objdesc 3
fanout_param_i 3
458_485 3
457_484 3
idxname 2
tabgtor 2
tabgtee 2

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

索引唯一性

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

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

select tabname, idxname, nrows, nunique
from systables t, sysindexes I
where t.tabid =i.tabid
and t.tabid > 99
and nrows > 0
and nunique > 0

输出如下所示：

tabname idxname nrows nunique
bsc_dte bscdte_i 6 6
omcgttready 231_413 1 1
systemrelease 451_478 3 3
neclass 452_479 31 12
sysrelneclassmap 453_480 33 3
proxynemgrmap 454_481 1 1
networkmoclass 455_482 362 199
nenmoclassmap 456_483 492 12
attrclass 457_484 1191 924
nmoattrclassmap 458_485 2901 199
fanout_comp fanout_comp_i 915 199
fanout_comp fanout_comp_i2 915 199
fanout_comp fanout_comp_i3 915 82
fanout_param fanout_param_i 2894 196

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

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

顺序扫描

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

select dbsname, tabname, sum(seqscans) tot_scans
from sysptprof
where seqscans > 0
and dbsname not like "sys%"
group by 1,2
order by 3 desc

输出如下所示：

dbsname tabname tot_scans airgen_10_0 systemrelease 2352
airgen_10_5_old systemrelease 1596
airgen_10_5 systemrelease 1596
airgen_10_0 fanout_comp 1587
airgen_10_5_old sysusers 1248
airgen_10_0 sysusers 1241
airgen_10_5 sysusers 1231
airgen_10_0 join_map 1036
airgen_10_0 fanout_param 958
airgen_10_0 func_call 770
airgen_10_5 nenmoclassmap 586
airgen_10_5_old nenmoclassmap 586

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

 

监控会话活动

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

常规会话统计信息

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

select sid, username, hostname, connected logint_time,
hex(state) s_state
from syssessions

输出类似于：

sid username hostname logint_time s_state

233989 omcadmin localhost 1041348844 0x00080021
233982 omcadmin gcsys-e1 1041348608 0x00080001
233981 omcadmin nysys4-e1 1041348608 0x00080001
233980 omcadmin nysys5-e1 1041348608 0x00080001
233979 omcadmin ffsys-e1 1041348608 0x00080001
233973 omcadmin nysys1 1041348608 0x00080001
233781 wsadmin2 gcmmi 1041346036 0x00080001
233697 omcadmin localhost 1041344008 0x00080001
233694 wsadmin4 nymmi1 1041343932 0x00080001
233693 wsadmin4 nymmi1 1041343932 0x00080001
230550 omcadmin nysys1 1041293396 0x00080001
230476 omcadmin ffsys-e1 1041292665 0x00080001
230421 omcadmin gcsys-e1 1041292365 0x00080001
230278 omcadmin nysys5-e1 1041291208 0x00080001

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

sysmaster 数据库中的 syssesprof 表提供了各会话的更多详细信息。使用以下查询，您可以更好地理解各个会话是如何与数据库交互的：

select sid, (isreads+bufreads+bufwrites+pagreads+pagwrites) access, locksheld,
seqscans, total_sorts, dsksorts
from syssesprof

输出如下所示：

Sid access locksheld seqscans tot_sorts disksorts
233982 246 0 2 0 0
230421 7789 12 456 1000 0
225679 9981 213 669 876 2
247869 10098 440 578 98 2
78 70 45 6 0 0
447 46 89 6 0 0

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

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

select username, hostname,
(isreads+bufreads+bufwrites+pagreads+pagwrites) access, locksheld, seqscans,
total_sorts, dsksorts
from syssessions s, syssesprof f
where s.id =f.sid

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

select owner, username, hostname, dbsname, tabname, type
from syssessions s, syslocks l
where sid = owner
and tabname not like "sys%"

输出类似于：

Owner username hostname dbsname tabname type

1422 wsine apple prod customer S
1567 jlinder sys3524 dev products S
2237 ejhonson case prod orders X
6679 cjz020 sys4800 dev shipment S
889654 jfjianing omega test prices X
77622 hong build50 test items S

如果在锁使用方面存在某些冲突，例如某个用户需要对已被别的用户锁定的表进行专有访问，那么您可以方便地确定该锁的所有者，并根据用户的优先级发出 onmode -z sid 命令来杀死会话，然后释放该锁； sid 这个编号是从上面输出中的 owner 字段中获取的；请注意，只有用户 "gbasedbt" 可以执行该命令。

查询统计信息

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

输出如下所示：

11:26:22 -- 1654784 Kbytes

session #RSAM total used
id user tty pid hostname threads memory memory
134709 omcadmin 3 29580 localhos 1 65536 61120

tid name rstcb flags curstk status
147311 sqlexec 6511e728 Y--P--- 1648 6511e728 cond wait(netnorm)

Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
134709 V 669b9020 65536 4416 162 6

name free used name free used
overhead 0 1648 scb 0 96
opentable 0 6000 filetable 0 920
log 0 2152 temprec 0 1608
keys 0 192 ralloc 0 20480
gentcb 0 1256 ostcb 0 2520
sort 0 56 sqscb 0 11960
sql 0 40 rdahead 0 640
hashfiletab 0 280 osenv 0 1584
buft_buffer 0 4272 sqtcb 0 3240
fragman 0 2176

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
134709 SELECT mso_db CR Not Wait 0 0 9.03

Current SQL statement :
SELECT ne_type, config_set_version FROM ne WHERE ne_inst = 46176 AND
msospace_id = 1

Last parsed SQL statement :
SELECT ne_type, config_set_version FROM ne WHERE ne_inst = 46176 AND
msospace_id = 1

输出的第一部分给出了关于正在执行的查询的一些常规统计信息，如对哪个数据库执行查询、其隔离级别以及锁方式。最有趣的两个字段是 SQL error 和 ISAM error 。如果这些字段非零，就表明查询出了一些问题，没有正确地执行。可以使用 8s finderr 实用程序来查明到底是什么问题，随后在解决了该问题之后，杀死该会话并重新执行该查询。

"Current SQL statement" 部分显示了正在执行的查询的完整 SQL 语法。它对于诊断问题查询和查询优化非常有帮助。如果您发现了该查询的一些问题，如响应时间太长，消耗了太多的系统 CPU 或内存，那么您可以按其显示状态为该查询制作一个副本，以便稍后研究和分析之用。您随后可以对 8s 数据库 dbaccess 实用程序运行同一个查询，以获取该查询性能方面更多详细的统计信息（如其执行计划和连接策略），从而确定问题的根源。根据研究所收集的统计信息，您可以进一步优化查询以获取更好的性能。 "Last parsed SQL statement" 部分显示了在内存中已经过解析的查询。由于在内存中已经解析过的查询在大多数情况下都是正在执行的查询，因此这一部分与前一部分在大多数时候都是相同的。

最后的一些技巧

要想在监控方面采取更主动的方式，您可以在 8s 数据库 安装期间修改由 8s 数据库 提供的警报程序。警报程序实际上是一个 UNIX shell 脚本，在发生某些错误时 8s 数据库 会自动调用该脚本。 8s 数据库 将所有的实例错误分为五个严重性级别：第一级最低，第五级最高。您可以设置警报程序，使之能够在发生实例断言失败时向 DBA 发送电子邮件，或者向 DBA 的寻呼机发送消息。有关如何修改警报程序和样本程序的详细信息，

此外，您还应该执行一些必要的维护例程，以确保数据库健康运行。一些基本的例程是：

·   验证和修复数据及索引完整性

·   为查询优化器更新内部统计信息

·   回收无用的共享内存段

 

通过本文中所描述的这些有效的监控实践，我们将能够成功地在最初阶段确定系统和数据库问题，这样我们就能够采取更为主动的方式来进行故障诊断和性能调优。

 

利用索引提高查询速度

 

如果查询结果仅为一行或很少几行时（高选择性 high selectivity) ，利用索引进行查询会大大提高效率。相比之下，如果没有索引，查询则只能顺序扫描整个表。在 OLTP 环境下，事务处理在很大程度上依赖于索引。只有在表很小时，才会顺序扫描表。系统会根据 SQL 语句中的 WHERE 子句判断是否使用索引。顺序扫描表会使系统性能受到严重影响。 sysmaster 中 sysptntab 表中的 pf_seqscnas 列显示了所进行的顺序扫描。 SET EXPLAIN 命令同样可以提供关于 SQL 语句如何访问数据库中的重要信息。

DSS 环境中的应用经常会查询出大量数据（低选择性 low selecviity) ，甚至整张表。顺序扫描对于这样的查询更为适合，因为此时顺序扫描可以利用 light scan 。 light scan 缓冲区位于共享内存的虚拟段与驻留段无关。关于 light scan ，以后章节中还将详述。

建立索引的代价

虽然索引可以很大地提高高选择性查询的性能，但维护这些索引是需要付出代价的。 以 INSERT 语句为例，在进行插入时系统首先将读取被插入表的索引以定位新记录关键字的位置。然后系统在将新记录写入数据页的同时还必须将新索引项写入索引节点。如果导致索引节点分裂，系统则必须多次写索引页

与 INSERT 语句相似， DELETE 语句也要求读入整个索引以定位索引节点位置，并置上删除标志。在删除索引时还需要处理索引节点合并、整理等问题。

在执行 UPDATE 语句时，必须首先定位并且删除旧的关键字然后插入新的关键字。所以在 UPDATE 语句必须两次读取索引。

在实际系统中通常把索引的根节点和第一级节点读入共享内存中，但如果需要访问更低层次的索引节点则必须进行磁盘操作。

索引类型

通常建立分离索引（ detached) 或基于表达式的索引分片（ expression based fragmented ）。分离索引和分片索引可以使得索引的 extent 内页连续，因而能提高性能。而对于不分片的表来说，附加索引（ attached) 在建立索引时，索引页和数据页交叉存放在一起，因而会增加磁头寻找时间。

对于中、小型表应该建立分离索引。对于经常访问的大表应建立基于表达式的分片索引，以减少索引页的数量和提高检索速度。索引分片不宜太多，以免表达式计算开销过大。根据索引的大小，一般可以分为 4 片。索引分片不支持轮转法（ round robin) 。

分离式索引与数据页分别存放在不同的数据空间中。例如：

CREATE INDEX index1 ON TABLE table_name(col1) in idxldbs;

基于表达式的分片索引在 FRAGMENT BY EXPRESSION 子句指定的数据空间中创建。例如：

CREATE INDEX index1 on TABLE table_name(col1) FRAGMENT BY EXPRESSION
col1>=0 and col1<=25000 in idxldbs1 col1>=25001 and col1<=50000 in idxldbs2
col1>=50001 and col1<=75000 in idxldbs3 col1>=750001 and col1<= 100000 in idxldbs4;

隐式索引

在定义约束条件时如果没有可利用的索引，系统将创建隐式索引。用户不能对隐式索引指定数据空间的位置、分片策略或者填充因子。隐式索引创建在数据库（而非表）所在地数据空间中，这给磁盘管理和性能带来了一些影响。

用户如果需要建立约束条件，最好能先创建与约束条件完全匹配的显式索引然后用 ALTER TABLE 命令增加约束条件。这样该约束条件就可以利用显式索引而不必再建立隐式索引。例如按如下方式：

CREATE TABLE table_name( col1 INTEGER, col2 INTEGER, col3 CHAR(25), ) in tableldbs;
CREATE unique INDEX index1 ON TABLE table_name(col1) in idxldbs;
ALTER TABLE table_name ADD CONSTRAINT PRIMARYKEY(col1);

而不要按如下方式：

CREATE TABLE table_name( col1 INTEGER, col2 INTEGER, col3 CHAR(25), PRIMARY KEY (col1 ） ) in tableldbs;

FILLACTOR( 填充因子）

对于 SELECT 操作，将 FILLFACTOR 设为 100

对于 SELECT 和 DELETE 操作将 FILLFACTOR 设为 100

对于 INSERT 和 UPDATE 操作将 FILLFACTOR 设为 50 到 70

CREATE INDEX index1 ON TABLE table_name(col1) in idx1 dbs FILLFACTOR 70;

填充因子决定了在建立索引时每一索引页的填充度。该参数是可以改变的。参数 FILLFACTOR 缺省值为 90 。如果只是改变某个索引的设置，请使用 CREATE INDEX 的 FILLFACTOR 子句。

设置较大的填充因子可以使索引更紧凑、使缓冲区更有效同时也可以减少检索记录时读取的页数。对于只读表应将 FILLFACTOR 设置为 100 。

对于只执行读和操作的表，如果将 FILLFACTOR 设置为 100, 在删除记录时可以减少合并索引节点、整理索引树的可能性。

对于有大量插入和更新操作的表，应该将 FILLFACTOR 设置为 50 到 70 。由于填充因子较小，在插入和更新操作时将延迟索引节点（页面）的分裂，从而提高系统性能。 有如下的 SQL FILLFACTOR 例句：

CREATE INDEX index1 ON TABLE table_name(col1) in idxldbs FILLFACTOR 70;

建立索引的步骤

建立索引必须遵循如下步骤：

·   确定需要建立的索引

·   决定索引的类型，分离索引还是分片索引。如果是分片索引，确定表达式

·   为每一索引确定填充因子

·   计算索引所需空间

·   决定索引数据空间在磁盘上的位置

·   为索引创建数据空间

·   定髓临时数据空间大小和位置。临时数据空间最好能分布存储。

·   利用 DBSPACETEMP 设置临时数据空间的大小，利用 onspace 命令的 -t 选项创建临时数据空间

·   设置环境变量 PDQPRIORITY 和 PSORT_NPROCS

·   创建相应的配置文件。其中各选项的最优设置参见并行排序和载入环境

配置参数

建立索引时参数的优化设置与上一章载入环境中的设置相同。在此作一简单回顾：

NUMCPUVPS     设置为 CPU 数目

BUFFERS     设置尽量多的缓冲区。初始时应为缓冲区分配最多至 25 ％的内存。 ?

SHMVIRTSIZE     赋予初始段最大值。最多至可用内存的 75 ％

CKPTINTVL    3000 。由物理日志决定何时生成检查点。

LRUS     一个 LRU 队列对含 500 － 700 个缓冲并允许 LRU 队列对最大至 128

LUR_MAX_DIRTY     设置为 80

LUR_MIN_dirty     设置为 70 ??

RA_PAGES     设置为 128 ，

RA_THRESHOLD     设置为 120

DBSPACETEMP     设置多个大小相同的临时数据空间分布在不同的设备上

DS_TOTAL_MEMORY    90 ％ *SHMVIRTSIZE

DS_MAX_SCANS     待建索引表的最大分片数

下面是对环境变量的简单回顾：

PSORT_NPROCS     设置为 CPU 数目，最大至 10

PDQPRIORITY     设置为 100

数据聚集

如果经常根据索引从表中读取大量的数据，那么最好对这些数据建立聚集。通过建立聚集可以减少读取页的数量，缩短检索时间，并可以充分利用预读功能顺序扫描数据。

数据的聚集通过创建聚集索引（ clustered index) 完成。利用 ALTER INDEX ...TO CLUSTER 语句可以将原有索引改为聚集索引，原有索引中的数据记录顺序将作重排。如果直接创建聚集索引，系统也会将数据记录排序。请注意，在 ATLTER INDEX ...TO CLUSTER 和 CREATE CLUSTER INDEX 语句执行后，系统将建立新表并删除旧表。所以必须有足够的空间来容纳新旧两张表。

如果可能，在载入数据前先对其按索引顺序排序，这样建立索引时不必设置 CLUSTER 子句，从而不必对数据进行聚集操作，避免建新表删旧表过程的开销。

 

 

官网：