调优 DB2 UDB v8.1 及其数据库的最佳实践(2)

　2008-09-17 16:28:01　来源：WEB开发网　　　

核心提示： SORTHEAP (DB)这个参数指定为私有排序使用的最大私有内存页数，或者指定为共享排序使用的最大共享内存页数，调优 DB2 UDB v8.1 及其数据库的最佳实践(2)，每个排序都有一个独立的排序堆，这是由数据库管理器在需要的时候分配的，则可能导致最严重的性能下降，在 UNIX 中可以用 netpmon

SORTHEAP (DB)

这个参数指定为私有排序使用的最大私有内存页数，或者指定为共享排序使用的最大共享内存页数。每个排序都有一个独立的排序堆，这是由数据库管理器在需要的时候分配的。

通常大家都理解得很好的是，当一个排序所需的内存量超过了 SORTHEAP 时，就会发生排序溢出。然而理解得不够好的一点是，如果统计信息已过时，或者数据有偏差，并且没有收集到发布统计信息，这时一旦 DB2 请求一个太小的堆，而实际的排序操作超出了所请求的量，就会发生溢出。因此，使统计信息保持时新十分重要。此外，应确保排序不是一个丢失的索引的结果。

对于 OLTP，一开始最好是设为 128，对于 OLAP，则设置在 4096 - 8192 之间。如果有很多的 "Sort overflows" （两位数）那么很可能需要增加 SORTHEAP。如果 "Number of hash join overflows" 不为 0，则按照 256 逐次增加 SORTHEAP，直到它为 0。如果 "Number of small hash join overflows" 不为 0，则按 10% 的速度增加 SORTHEAP，直到小散列连接溢出数为 0。

CHNGPGS_THRESH (DB)

使用这个参数来指定缓冲池中被更改页面所占的百分比，此时将启动异步的页面清除器将更改写入到磁盘，以便在缓冲池中为新的数据空出空间。在只读环境下，不使用页面清除器。在 OLTP 中，使用 20-40 这样的一个值应该可以提高性能（在更新活动庞大的情况下使用 20），因为使这个值更低一些将使 I/O Cleaners 在从脏缓冲池页面写出数据时更具有侵略性，但是每次做的工作却变少了。如果没有很多的 INSERT 或 UPDATE，则对于 OLTP 和 OLAP 来说，缺省的 60 应该就比较好了。

如果 "Dirty page steal cleaner triggers"是一个两位数，则试着降低之。如果 "Buffer pool data writes"较高，而 "Asynchronous pool data page writes"较低，则试着降低这个参数。

从 FixPak 4 起，有另一种页面清除算法，这种算法可以提高特定缓冲池的性能。您需要令概要注册表变量 DB2_USER_ALTERNATE_PAGE_CLEANING=YES，这样忽略 CHNGPGS_THRESH。确保 NUM_IOSERVERS 至少为 3，否则它会拖新算法的后腿。

NUM_IOCLEANERS (DB)

这个参数指定一个数据库的异步页面清除器的数量，异步页面清除器将更改后的页面从缓冲池写到磁盘。一开始将这个参数设为等于系统中 CPU 的数量。当触发了 I/O Cleaners 时，它们会同时启动，因此您不希望有那么多的清除器，以致影响性能和阻塞其他处理过程。

如果 Asynchronous Write Percentage (AWP) 是 90% 或更高，则减少 NUM_IOCLEANERS，如果 Asynchronous Write Percentage (AWP) 小于 90%，则增加 NUM_IOCLEANERS。

AWP = (( "Asynchronous pool data page writes"+ "Asynchronous pool index page writes") * 100) / ( "Buffer pool data writes"+ "Buffer pool index writes")

NUM_IOSERVERS (DB)

I/O 服务器用于执行预取操作，而此参数则指定一个数据库的 I/O 服务器的最多数量。非预取 I/O 是从数据库代理调度的，因此不受此参数的约束。一开始将该参数设置为等于数据库所跨的物理磁盘数（即使是一个磁盘阵列中的许多磁盘或者一个逻辑卷中的许多磁盘） + 1 或 2，但是不大于 CPU 的 # 的 4-6 倍。

如果您很快看到 "Time waited for prefetch (ms)"，那么您或许想添加一个 IO Server，以查看性能是否有提高。

MAXFILOP (DB)

这个参数指定每个数据库代理所能打开的最大文件数。如果打开一个文件时被打开的文件数超出了这个值，则要关闭该代理正在使用的一些文件。过度的打开和关闭都会降低性能。SMS 表空间和 DMS 表空间文件容器都是视作文件来对待的。通常 SMS 使用的文件要更多一些。

增加该参数的值，直到 "Database files closed"为 0。

LOGPRIMARY、LOGSECOND 和 LOGFILSZ (DB)

LOGPRIMARY 指定要预先分配空间的主日志文件的数量，而 LOGSECOND 是按照需要来分配空间的。LOGFILSIZ 定义每个日志文件的大小。

如果 "Secondary logs allocated currently"的值很大，那么就可能需要增加 LOGFILSIZ 或 LOGPRIMARY （但是要确保 LOGPRIMARY + LOGSECOND 不超过 256）。还可以使用 "Maximum total log space used (Bytes)"来帮助指出对日志文件空间（主日志 + 从日志）的依赖性。

日志文件的大小对灾难恢复有一定的影响，因为在灾难恢复中要使用日志发送（log shipping）。日志文件比较大时，性能会更好些，但是可能潜在地增加丢失事务的程度。当主系统崩溃时，最近的日志文件及其事务可能无法发送到从系统，因为在失败之前没有关闭该文件。日志文件越大，随着日志文件的丢失，丢失事务的程度也越大。

LOGBUFSZ (DB)

这个参数允许指定用作在将日志记录写到磁盘之前的缓冲区的数据库堆（DBHEAP）的数量。当提交一个事务或者日志缓冲区已满的时候，就要将日志记录写入磁盘。对日志记录进行缓冲将导致将日志记录写入磁盘的活动不那么频繁，但每次要写的日志记录会更多。对于 OLTP，一开始以至少 256 页为佳，对于 OLAP，则以 128 页为佳。如果常常看到多于一对的 "Log pages read"，那么可能需要增加这个值。如果发生了回滚，也可能要读取日志页。

如果在试图增加 LOGBUFSZ 时收到一个错误，那么可以按相同数量增加 DBHEAP，然后再次尝试。

PKGCACHESZ (DB)

这个包缓存用作静态和动态 SQL 语句的缓存部分。缓冲包允许数据库管理器减少内部开销，因为它消除了在重新装载一个包时访问系统编目的需要；或者，对于动态 SQL，消除了重新编译的需要。

PKGCACHESZ 应该大于 "Package cache high water mark (Bytes)"。如果 "Package cache overflows"不为 0，那么可以尝试通过增加 PKGCACHESZ 来使这个计数器变为 0。

Package Cache Hit Ratio (PCHR) 应该尽可能接近 100%（而不从缓冲池中获取所需的内存）。用下面的公式来计算：

PCHR = (1-( "Package cache inserts"/ "Package cache lookups"))*100

CATALOGCACHE_SZ (DB)

这个参数用于缓存系统编目信息，例如 SYSTABLE、授权和 SYSROUTINES 信息。缓存编目信息十分重要，尤其是在使用 DPF 的情况下更是如此，因为不必为获得先前已经检索过的信息而访问系统编目（编目分区），从而减少了内部开销。

不断增加该值，直到对于 OLTP 的 Catalog Cache Hit Ratio (CCHR) 达到 95% 或更好的值：

CCHR = (1-( "Catalog cache inserts"/ "Catalog cache lookups"))*100

如果 "Catalog cache overflows"的值大于 0，也要增加该参数的值。还可以使用 "Catalog cache high water mark (Bytes)"来确定编目缓存曾消耗过的最多内存。如果 High water mark 等于允许的 Maximum 大小，那么就需要增加编目缓存堆的大小。

实验： DBM 和 DB 配置

下面的参数可能带来额外的性能。然而，快照中的特定监视器并不是直接报告出它们的影响。相反，可能需要一次更改一个参数，然后测量应用程序的总体性能。最好的测量方法是从几个快照中检查更改前后 SQL 的执行次数。

INTRA_PARALLEL (DBM)

该参数指定数据库管理器是否可以使用内部分区并行性（intra-partition parallelism）。缺省值 NO 对于并发连接较多的情况（主要是 OLTP）最好，而 YES 对于并发连接较少的情况以及复杂 SQL （OLAP/DSS）来说最好。混合的工作负载通常可以得益于 NO。

当启用该参数时，就会导致从共享内存中分配排序内存。此外，如果并发程度显著增加的话，还可能导致过多的系统开销。如果系统是非 OLTP 的，则 CPU 数对分区数的比例是 4:1，而 CPU 负载运行的平均百分比是 50%，INTRA_PARALLEL 很可能会提高性能。

DFT_QUERYOPT (DB)

用于指定在编译 SQL 查询时所使用的缺省优化级别。对于混合的 OLTP/OLAP，使用 5 或 3 作为缺省值，对于 OLTP，使用一个更低的级别，而对于 OLAP，则使用一个更高的级别。对于简单的 SELECTS 或短的运行时查询（通常只需花不到 1 秒钟就可以完成），使用 1 或 0 也许比较合适。如果有很多的表，有很多相同列上的连接谓词，那么尝试级别 1 或 2。对于超过 30 秒钟才能完成的长时间运行的查询，或者如果要插入一个 UNION ALL VIEW（这是在 FixPak4 中加进来的），那么可以尝试使用级别 7。在大多数环境下都应该避免使用级别 9。

UTIL_HEAP_SZ (DB)

该参数指定 BACKUP、RESTORE 和 LOAD 实用程序可以同时使用的最大内存数。如果正在使用 LOAD，那么对于每个 CPU 将 UTIL_HEAP_SZ 设置成至少 10000 页。

NEWLOGPATH (DB)

该参数指定最长 242 个字节的一个字符串，用于更改日志文件写和存储的位置。这可以指向一个全限定路径名，或者指向元设备。将日志路径更改到一个独立的本地高速磁盘（只用于日志记录）可以显著地提高性能。

进一步的 SQL 分析

Design Advisor

如果有一个针对特定问题的查询或者一组查询，那么可以将该工作负载输入到 DB2 Design Advisor (db2advis) 中，由它去推荐一组有效的索引。如果不知道 SQL，也可以

使用快照捕获动态 SQL。

用一个语句事件监视器收集在一段时间内发出的所有 SQL。

从 SYSCAT.STATEMENTS 编目视图中提取静态 SQL。

语句事件监视器的使用将在本节稍后一点讨论。

可以从 DB2 Control Center 使用 Design Advisor，或者从 CLP 命令行使用该工具。下面讨论这两种界面。

使用 DB2 Control Center

在 Control Center 中，展开对象树，直到发现感兴趣的数据库。右键单击数据库名，并从弹出菜单中选择 Design Advisor'。现在您就可以通过查看最近执行的 SQL，检查包，或者手动地添加 SQL 语句来构造工作负载了。

使用 DB2 CLP

当使用 CLP 时，输出被显示到屏幕，这可以捕捉到一个脚本中并执行。下面是一些常见的例子。

要为一个特定的针对 'example' 数据库的 SQL 语句推荐索引，并且要在 1 分钟内标识出索引：

db2advis -d sample -s "select count(*) from sales where region = 'Quebec'" -t 1

要为多条语句推荐索引，我们可以构建一个文本文件，该文件看上去是这样的：

db2advis -d sample -s "--#SET FREQUENCY 10 SELECT * FROM SALES; --#SET FREQUENCY 2 SELECT FIRSTNME FROM EMPLOYEE WHERE EMPNO = ?;

其中 frequency 是该 SQL 语句与输入文件中其他 SQL 语句相比其执行次数所占的比重。生成结果：

db2advis -d dbname -i sqlstmts_file > recindexes.out

由于输出显示到了屏幕上，我们使用一个重定向将索引定义捕捉到一个文件中，然后该文件就可以作为一个 DB2 脚本来运行了。

您还可以通过管道将动态 SQL 从一个快照发送到 Design Advisor 中：

get snapshot for dynamic SQL on dbname write to file

这样将以一种内部文件格式保存快照。然后就可以用下列语句将结果插入到一个 Design Advisor 表中：

insert into advise_workload(select 'myworkload',0,stmt_text,cast(generate_unique() as char(254)), num_executions, 1,1,0,0,cast(null as char) from table (snapshot_dyn_sql(' dbname', -1)) as snapshot_dyn_sql)

在一个工作负载中，每条 SQL 语句的缺省 frequency 是 1，缺省的 importance 也是 1。generate_unique() 函数将一个惟一的标识符指定给语句。用于可以将这两列更新为更有意义的值。要生成索引：

db2advis -d dbname -w myworkload

一旦执行了 Design Advisor，它就会填充 advise_index 表。您可以通过下面的查询来查询这个表，以列出 Design Advisor 的所有建议：

SELECT CAST(CREATION_TEXT as CHAR(200)) FROM ADVISE_INDEX

对 SQL 的事件监视

CREATE EVENT MONITOR 语句定义一个监视器，在使用数据库的时候，该监视器将记录所发生的某些事件。每个事件监视器的定义还会指定数据库应该将事件记录在哪里。我们可以创建事件监视器来记录跟下列类型的事件有关的信息：DATABASE、TABLES、DEADLOCKS [WITH DETAILS]、TABLESPACES、BUFFERPOOLS、CONNECTIONS STATEMENTS 和 TRANSACTIONS。

清单 10和清单 11展示了可用于收集事件监视器输出的脚本：

清单 10. getevmon.ksh (UNIX)

#!/usr/bin/ksh #　create an event monitor and capture its output #　parameters: (1) database name #　　　　　　　(2) monitor output file #　　　　　　　(3) interval between iterations (seconds) #　Note: You may receive an error about the monitor heap being too small. You may want to set #　　　　mon_heap_sz to 2048 while monitoring. if [ $# -ne 3 ] 　then echo "Requires 3 Parameters: dbname monitor_output_file interval_in_#seconds"; exit fi MON=evmon # "nonblocked" may cause loss of data but has less impact on system than default "blocked". MONTYPE=nonblocked SLEEP=$3 DB=$1 #EVENTS="deadlocks with details" #EVENTS="tables, statements, deadlocks with details, connections" EVENTS="statements" OUTFILE=$2 OUTDIR="TMPEVMON" mkdir $OUTDIR chmod 777 $OUTDIR cd $OUTDIR db2 connect to $DB db2 -v drop event monitor $MON db2 -v create event monitor $MON for $EVENTS　　　write to file "'`pwd`'" buffersize 64 $MONTYPE db2 -v set event monitor $MON state = 1 echo "" echo "Event Monitor active at `date`; sleeping for $SLEEP seconds before turning it off." sleep $SLEEP db2 -v set event monitor $MON state = 0 cd .. db2evmon -db $DB -evm $MON > $OUTFILE db2 -v drop event monitor $MON db2 terminate rm -fr $OUTDIR echo echo db2evmon output is in $OUTFILE

清单 11. getevmon.bat (Windows)

@echo off REM　create an event monitor and capture its output REM　parameters: (1) database name REM　　　　　　　(2) monitor output file REM　　　　　　　(3) interval to monitor for (seconds) REM　Note: You may receive an error about the monitor heap being too small. You may want to set REM　　　　mon_heap_sz to 2048 while monitoring. :CHECKINPUT IF ""=="%3" GOTO INPUTERROR GOTO STARTPRG :INPUTERROR echo %0 requires 3 parameters: dbname filename sleep_interval echo e.g. "%0 sample evmon0302.out 60" GOTO END :STARTPRG SET dbname=%1 SET outfile=%2 SET sleep_interval=%3 SET MON=evmon REM "nonblocked" may cause loss of data but has less impact on system than default "blocked". SET MONTYPE=nonblocked REM SET EVENTS="deadlocks with details" REM SET EVENTS="tables, statements, deadlocks with details, connections" SET EVENTS="statements" SET OUTDIR="c:　　empevmon" mkdir %OUTDIR% db2 connect to %dbname% db2 -v drop event monitor %MON% db2 -v create event monitor %MON% for %EVENTS% write to file '%OUTDIR%' buffersize 64 %MONTYPE% db2 -v set event monitor %MON% state = 1 echo Sleeping for %sleep_interval% seconds before turning off. sleep %sleep_interval% db2 -v set event monitor %MON% state = 0 db2evmon -db %dbname% -evm %MON% > %OUTFILE% db2 -v drop event monitor %MON% db2 terminate rmdir /s /q %OUTDIR% echo db2evmon output is in %OUTFILE% :END

输出将包含所有动态 SQL 语句的文本。不过，对于静态 SQL 语句，输出将列出包名和节号。具有包名和节号的 db2expln 可用来提取语句文本，或者也可以查询 syscat.statements 视图来提取文本。

清单 12展示了在语句事件监视器的输出中捕获到的一个事件：

清单 12. 示例语句事件监视器输出

42) Statement Event ... 　Appl Handle: 16 　Appl Id: *LOCAL.DB2.010746204025 　Appl Seq number: 0003 　Record is the result of a flush: FALSE 　------------------------------------------- 　Type　　 : Dynamic 　　　　　Operation: Close 　Section　: 201 　Creator　: NULLID　　Package　: SQLC2E03 　Consistency Token　: AAAAAJHR 　Package Version ID　:　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Cursor　 : SQLCUR201 　Cursor was blocking: TRUE 　　　　　Text　　 : select * from staff 　------------------------------------------- 　　　　　Start Time: 10-06-2003 17:27:38.800490 　　　　　　　　Stop Time:　10-06-2003 17:27:38.806619 　　　　　　　　Exec Time:　0.006129 seconds 　Number of Agents created: 1 　　　　　user CPU:　 Not Available 　System CPU: Not Available 　Fetch Count: 35 　Sorts: 0 　Total sort time: 0 　Sort overflows: 0 　　　　　Rows read: 35 　Rows written: 0 　Internal rows deleted: 0 　Internal rows updated: 0 　Internal rows inserted: 0 　SQLCA: 　　　　　sqlcode: 0 　 sqlstate: 00000

由于可能存在数千个语句事件，找出问题的最容易的方法是使用 grep (UNIX) 或 findstr (Windows)。下面是在输出中用于搜索的一些有用的字符串：

" sqlcode: -"

这对于发现错误很有用，例如发现 -911 RC 2，即死锁，又例如 RC 68，即锁超时。

grep -n " sqlcode: -" stmtevmon_output findstr /C:" sqlcode: -" stmtevmon_output

" Rows read: "

这标识了一条语句读取的行数（没有将索引项或直接表读计算在内）。如果这个数字很大，则意味着需要一个索引，或者统计信息已过时。

" Exec Time: "

这是语句的实际执行时间，包括锁等待的时间。有时候，可以方便地从事件监视器数据的最后开始，向前搜索 "Exec Time"，并查看一条开销较大的 SQL 语句是否存在某种模式或存在重复。然后可以用 EXPLAIN 检查 SQL 语句，看看是什么问题。

" Sort overflows:"

显示开销很大的排序溢出发生在哪里。这可能表明需要使用索引、运行 RUNSTATS 或加大 SORTHEAP。

" Fetch Count:"

对于查看对结果集执行了多少 fetch 操作很有用。这里并不是记录每个 FETCH 操作。DB2 通过加大该字段，在语句级上跟踪这些 FETCH 操作。可以用 FETCH FIRST 子句限制 FETCH 操作。

标识了想要进一步观察的一些元素之后，打开语句事件监视器的输出，并按您感兴趣的字符串进行搜索。一旦定位到语句，下面的一些字段将很有用：

" Operation: "

该字段可以提供一般语句流。它标识 Prepare、Open、Fetch、Close、Commit 等等。

" Text : "

这是用于动态 SQL 的语句文本。对于静态 SQL，查找 "Section :"和 "Package :"。

" Start Time: " 或 " Stop Time: " 或 " Time: "

这可以帮助标识起始时间、终止时间或者同时标识这两个时间。Stop time 和随后的 Start time 还可以说明语句之间的间歇时间有多长，如果您怀疑 DB2 在其他某个地方花费了时间（可能是存储过程开销），那么这一点就比较有用。

对访问计划的解释

SQL 解释工具是 SQL Compiler 的一部分，用于显示一条语句的访问计划，以及编译该语句时所在的环境。Explain 信息可以以很多方式来捕捉和显示。

这种信息可以帮助您：

理解为一个查询选择的执行计划。

辅助设计应用程序的程序。

确定应何时重新捆绑应用程序。

辅助数据库设计。

在获得一条 SQL 语句的解释数据之前，必须使用跟调用 explain 工具的授权 ID 相同的模式定义一组 explain 表。请查看 DB2 安装目录下的 'qllib/misc/explain.ddl' 或 'qllibmiscexplain.ddl'，以找到 Explain Tables 的 DDL。

要清除 explain 表，发出：

delete from schema.explain_instance

这样所有其他的 explain 表也将被清除，这是由于参照完整性连锁删除功能造成的。

当分析 explain 输出时，应识别是否出现下列情况：

对相同的一组列和基本表使用的 ORDER BY、GROUP BY 或 DISTINCT 操作符将从索引或物化查询表（MQT）中受益，因为消除了排序。

代价较高的操作，例如大型排序、排序溢出以及对表的大量使用，都可以受益于更多的排序空间、更好的索引、更新的统计信息或不同的 SQL 。

表扫描也可以从索引中受益。

完全索引扫描或无选择性的索引扫描，其中不使用 start 和 stop 关键字，或者使用这两个关键字，但是有一个很宽的取值范围。

Visual Explain

Visual Explain 使用起来非常简单和直观。它可以解释包含参数标记（记为 "?"）的语句，但是如果要与其他共享结果可能就比较困难，因为它是基于 GUI 的。在 Control Center 中，右键单击想要为其解释一条 SQL 语句的数据库，并选择 "Explain SQL'。您可以在 SQL 文本框中输入 SQL 语句，然后单击 "OK" 来生成图。图 2展示了这种图的一个例子看上去的样子：

图 2. Visual Explain Results 屏幕

调优 DB2 UDB v8.1 及其数据库的最佳实践(2)

您可以通过双击任何节点来获得更详细的分析结果。

基于文本的 Explain

db2exfmt 和 db2expln 的 text-based 选项不易于读（一开始），但是与他人共享起来就容易多了，因为您可以简单地向他们发送输出文件。

通用，在捕捉新数据之前清除 explain 表是一个好习惯。发出：

delete from schema.explain_instance

最后所有其他 explain 表都将被清除，这是由于参照完整性的连锁删除功能造成的。

所有 explain 输出（包括 Visual Explain）都是从下往上读的。

图 3. Text Explain 屏幕

调优 DB2 UDB v8.1 及其数据库的最佳实践(2)

这里不像 Visual Explain 那样将所有细节显示在不止一个屏幕上，而是将所有细节列在一个输出文件中。在上图中每个操作符都编了号，当您往下处理该文档时，每个操作符都将被详细解释。例如，图中的一个操作符可以作如下解释：

清单 13. 读 Text Explain 操作符

　　　5.7904　　- # of rows returned (based on statistics calculation) 　　　HSJOIN　　　- type of operator 　　　(　 2)　　- operator # 　　　75.536　　　- cumulative timerons 　　　　 3　　　　- I/O costs　　

返回的行数、timeron (cost) 数和 I/O 都是优化器估计的，在某些情况下可能与实际数字不符。timeron 是 DB2 的度量单元，用于给出对数据库服务器在执行同一查询的两种计划时所需的资源或成本的粗略估计。估计时计算的资源包括处理器和 I/O 的加权成本。

您可以使用 db2exfmt 来解释单独一条语句。例如，

清单 14. 为一条语句生成 Text Explain 输出

explain all for 　　　　SQL_statement db2exfmt -d 　　　　dbname -g tic -e 　　　　explaintableschema -n % -s % -w -1 -# 0 -o 　　　　outfile

如果为用 ";" 隔开的几条 "explain all' 语句构建一个文本文件，就可以一次解释多条语句：

清单 15. 为多条语句生成 Text Explain 输出

db2 -tf 　　　　file_with_statements db2exfmt -d 　　　　dbname -g tic -e 　　　　explaintableschema -n % -s % -w % -# 0 -o 　　　　outfile

最后，如果想解释一个包中包含的静态 SQL，那么您将需要使用 db2expln 工具：

db2expln -database dbname -schema schema_name -package package -output outfile.txt

DB2 v8 Documentation:

Concepts ==> Administration ==> Performance tuning ==> SQL explain facility

Tutorials ==> Tutorials (Visual Explain Tutorial)

SQL 语句调优

下面的这些技术可以用来从语句执行中挤出额外的性能：

使用 SELECT ... FOR UPDATE保护在随后的 UPDATE 语句中可能被更新的那些行。这样一来，选中的所有行上安置了一个更新（U）锁。

使用 SELECT ' FOR READ ONLY（或 FETCH ONLY）表明结果表是只读的。这意味着不能在随后放置的 UPDATE 或 DELETE 语句中引用游标。这可以帮助提高 FETCH 操作的性能，因为它允许 DB2 执行块操作（对于一个给定的 FETCH 请求返回多行给客户）。

用 SELECT ' OPTIMIZE FOR n ROWS优化返回时间。这样可以使优化器快速地返回 N 行，而不是像缺省行为那样，最小化整个回答集的代价。此外，如果使用 READ ONLY 子句，这将影响在每个块中返回的行数（一个块中的行数不会大于 n）。这不会限制可以取的行数，但是如果要取多于 n 行的记录，就可能降低性能。为了使该子句对数据缓冲区有一定的影响， n * row size 的值不能超出通信缓冲区的大小（由 DBM CFG RQRIOBLK 或 ASLHEAPSZ 定义）。

可以用 SELECT ' FETCH FIRST n ROWS来限制查询结果集的大小。

大规模的 DELETE/Purging 可以通过 altER TABLE ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE来实现。由于该操作没有日志记录，如果哪个地方出了错，就不得不将表删除。一种更安全的方法是使用 Import 实用程序（带上 Replace 选项）和一个空文件。

要减少锁等待或死锁出现的几率，可以频繁地使用 commit来释放锁。

确保让重复的语句使用参数标记。对于 OLTP，编译时间是比较可观的，因此用参数标记替换文字可以避免重复编译。当使用参数标记时，优化器假设值是均匀分布的，因此如果数据比较偏，则意味着所选择的访问计划不好。通常，正是 OLAP 这种类型的环境深受值的平均分布这一假设的毒害。

为了拥有更好的粒度，更好的性能和并发性，应在语句级指定隔离级别。DB2 支持 Uncommitted Read、Cursor Stability、Read Stability 和 Repeatable Read （UR、CS、RS 和 RR）。例如，SELECT * FROM STAFF WITH UR 将使用 Uncommitted Read （最小锁）执行 SELECT 语句。

DB2 v8 Documentation:

Reference ==> SQL ==> Queries and subqueries ==> select-statement

在有偏差数据的情况下使用参数标记时，指定选择性

如果数据不是均匀分布的，那么指定选择性就十分有用。 SELECTIVITY指任何一行满足谓词（即为真）的概率。采用具有高度选择性的谓词是可取的。这种谓词将为以后的操作符返回更少的行，从而减少了为满足查询所需的 CPU 和 I/O。

例如，对一个有 1,000,000 行的表执行一个选择性为 0.01 (1%) 的谓词操作，则意味着大约只有 10,000 行满足条件，而另外的 990,000 都不满足条件。

如果不是仅仅使用均匀分布的假设，而是人为地使用一个较低的选择性的值（例如 0.000001）来“保证”使用那一列上的索引，那么就可以影响优化器。如果预料到一个表要增长，并且希望确保能够坚持使用某些特定列上的索引，那么这一点就十分有用。如果想阻止 DB2 在某一特定列上使用索引，那么可以将 SELECTIVITY 设为 1。

有了这种技术，使用优化级别 5 （例如 DFT_QUERYOPT=5）就最具有可预测性。而且，首先必须设置注册表变量 DB2_SELECTIVITY=YES，然后，在使用 SELECTIVITY 子句之前重新启动实例。

您可以为下列谓词指定 SELECTIVITY 子句：

基本谓词，其中至少有一个表达式包含主机变量/参数标记（基本谓词包括像 =、<>、< 和 <=, 这样的简单的比较符，但是不包括像 IN、BETWEEN 和 IS NULL 这样的东西。）

其中的 MATCH 表达式、谓词表达式或换码表达式中包含主机变量/参数标记的 LIKE 谓词。

selectivity 的值必须是在 0 到 1 整个范围内的一个数值常量（numeric literal）值。如果没有指定 SELECTIVITY，那么就会使用一个缺省值。如果 SELECTIVITY 的值为 0.01，则意味着该谓词将过滤掉除表中所有行的 1% 之外的所有其他行。不过应该把提供 SELECTIVITY 看作是最后一招。

例如：

清单 16. 指定 Selectivity 的例子

　　SELECT c1, c2, c3, FROM T1, T2, T3 　　　　WHERE T1.x = T2.x AND 　　　　　　T2.y = T3.y AND 　　　　　　T1.x >= ? selectivity 0.000001 AND 　　　　　　T2.y < ? selectivity 0.5 AND 　　　　　　T3 = ? selectivity 1

DB2 v8 Documentation:

Reference ==> SQL ==> Language elements ==> Predicates ==> Search Conditions

持续维护

接下来的一些技术对于维护数据库的最佳性能很有用。当您使用一个分了区的数据库时，应记住命令的作用范围。例如，RUNSTATS 命令只收集调用该命令时所在数据库分区上的表的统计信息，而 REORG 则可以操作数据库分区组中的所有分区。请参阅每个命令的文档的鈥楽cope' 部分。

REORG 和 REORGCHK

REORG 将消除溢出的行，并从表和索引中删除的行那里收回空间，如果有很多的删除、更新或插入操作，这一命令就非常有用。该命令还可以用来将一个表放入到某个索引序列中（例如，在对群集索引的支持中就是如此）。REORG 命令可以在线执行和暂停。REORCHK 用于识别那些需要 REORG 的表和索引，也可用于收集数据库中所有表的统计信息。

收集更新后的统计信息，并标识是否需要重组表或索引：

REORGCHK UPDATE STATISTICS ON TABLE ALL

标识是否需要根据当前统计信息重组表或索引：

REORGCHK CURRENT STATISTICS ON TABLE ALL

标识为需要 REORG 的表将在 REORGCHK 输出的 reorg 列（F1 到 F8）中显示一个或多个星号。现在就可以真正地 REORG 被标识出的表。为了 REORG 一个表，不考虑顺序，只是简单地从伪删除的行收回空间，并消除溢出的行：

REORG TABLE schema.tablename

为了在一个表的所有索引上执行 REORG：

REORG INDEXES ALL FOR TABLE schema.tablename

为了根据特定的索引（ORDER BY 或一个群集索引）按物理序列对一个表排序：

REORG TABLE schema.tablename INDEX schema.indexname

DB2 v8 Documentation:

Reference ==> Commands ==> Command Line Processor (CLP) ==> REORG

Reference ==> Commands ==> Command Line Processor (CLP) ==> REORGCHK

RUNSTATS

REORG（重组）表和索引之后，重新收集表和索引的统计信息总是可取的，这样优化器就可以创建最合适的访问计划。您可能发现执行一次抽样的 RUNSTATS （对于大型数据库会有更好的性能）或者执行一次后台运行。

为了标识之前是否已经对表和索引执行过 RUNSTATS：

清单 17. 标识 Runstats 时间

SELECT char(tabname,40) FROM syscat.tables　 WHERE type = 鈥楾' AND stats_time is null SELECT char(indname,40) FROM syscat.indexes WHERE stats_time is null Or, to list runstat times (oldest first) SELECT char(tabname,40), stats_time FROM syscat.tables　 WHERE type = 鈥楾' ORDER by stats_time

对 System Catalog 表执行 RUNSTATS 也可以为这些表带来好处。

下面是一些有用的命令：

表 4. 一些有用的 Runstats 命令

命令	描述
RUNSTATS ON TABLE schema.table	收集一个特定表的统计信息
RUNSTATS ON TABLE schema.tableAND INDEXES ALL	收集一个特定表及其所有索引的统计信息
RUNSTATS ON TABLE schema.tableAND SAMPLED DETAILED INDEXES ALL	使用扩展的索引统计信息和 CPU 采样技术收集关于一个特定表的统计信息，这对于非常大型的索引（1+ 百万行）十分有用，因为 RUNSTATS 可用的时间是有限的。
RUNSTATS ON TABLE schema.tableWITH DISTRIBUTION	收集关于一个表（或者也可以是特定列）的附加统计信息，当数据不是均匀分布时，这个命令很有用。

使用一条 SELECT 语句创建一个脚本也十分方便，只需将 SELECT 语句的结果通过管道发送到一个文件：

清单 18. 生成一个 Runstats CLP 脚本

　　SELECT 'RUNSTATS ON TABLE　' || rtrim(tabschema) || '.' 　　　　　|| char(tabname,40) || 　　　　　' AND DETAILED INDEXES ALL;' 　　FROM syscat.tables 　　WHERE type = 'T' 　　ORDER BY tabschema, tabname;

DB2 v8 Documentation:

Reference ==> Commands ==> Command Line Processor (CLP) ==> RUNSTATS

REBIND

执行了 REORG 和 RUNSTATS 之后，您需要 REBIND 所有的数据库包，以便静态 SQL 可以利用最新的系统统计信息。使用 DB2RBIND 重新绑定所有的数据库包：

db2rbind dbname -l logfile.out ALL

可以用 REBIND 来重新绑定单独的包。

DB2 v8 Documentation:

Reference ==> Commands ==> System ==> db2rbind

Reference ==> Commands ==> Command Line Processor (CLP) ==> REBIND

数据库分区功能（DPF）性能

DB2 v8.1 ESE 的数据库分区功能（DPF）允许在一个服务器内或跨越一个群集的服务器给数据库分区。这为支持非常大型的数据库、复杂的工作负载和增加的管理任务的并行性提供了更多的可伸缩性。下面的小节包含帮助您获得 DPF 方面的最佳性能的建议。

何时分区？

在 64 位 DB2 出现以前，分区技术通常用于解决 32 位架构中关于共享内存的限制（大约每个数据库 2 GB）。利用内存的更好选择是使用一个大型的 SMP 64 位服务器，因为这种服务器可以避免分区的复杂性和开销。

然而，在某些情况下，分区也可以大大加快 Select、LOAD、BACKUP 和 RESTORE 的执行。每添加一个分区，就减少了每个分区上处理器要处理的数据量。通常，在分区数不多的小型数据库中，这种性能上的提高难于见到，因为散列行和发送数据的开销抵消了因处理更少数据而赚到的性能。

另一个分区的原因是克服对每个分区的一些 Database Manager 限制（例如，对于 4 K 的页面大小，每个分区上的表最大为 64GB）。

多少个分区？

这个问题难于回答，因为有些系统每个分区 1 个 CPU 的时候运行得最好，而其他一些系统每个分区需要 8 个或更多 CPU。这里的思想是让分配给每个分区的 CPU 都忙起来。对于一个给定的 SMP 机器，一开始最好是大约每个分区 4 个 CPU。如果发现 CPU 的利用率一直比较低（例如低于 40%），那么可能需要考虑增加更多的分区。

一般情况下，每台机器上的分区数越少越好，因为这样一来更容易本地旁路（local bypass）和并置（后面会解释）。

选择一个理想的分区键

通过选择一个适当的分区键，有助于确保平衡的数据分布和工作负载以及有效的表并置（table collocation）。

当选择一个分区键时，通常应记住下面几点：

总是亲自指定分区键，而不是使用缺省值。

分区键必须是主键或惟一索引的子集。

有很多独特值的列是比较好的选择。如果一个列只有有限的几种独特值，那么就只能生成少量的散列数，这会增加偏差数据和非平衡工作负载出现的机会。

如果分区键由太多的列组成（通常指 4 列或更多列），则仅仅是生成散列数这一项就可能导致性能下降。

使用经常要连接的一组相同的列作为分区键，以增加合并连接（collocated join）的数量。

使用 Integer 类型的列比使用字符类型的列更有效，后者又比使用小数类型的列更有效。

DB2 v8 Documentation:

Reference ==> SQL ==> Language elements ==> Data types ==> Partition-compatible data types

表并置

表并置允许本地处理查询（在相同的逻辑分区内），这样可以避免在参与的各分区间不必要的数据移动。为了帮助确保表并置，使用连接的列作为分区键，并将那些连接的表放入到共享相同分区组的表空间中。连接的表的分区键应该有相同的列数和相应的数据类型。

如果有些表不能按照跟它们通常连接的表相同的键来分区，而那些表的大小适中并且是只读的，那么采用复制的物化查询表或许是提高性能的一个有效的解决办法。这样就允许将整个表（或表的一部分）内容复制到数据库分区组中的每个分区上。然而，如果这个表要频繁更新，那么这样就可能降低性能，因为要增加资源的使用。

为了创建一个简单的复制的 MQT，使用下列语法：

CREATE TABLE replicated_table AS (SELECT * FROM source_table) DATA INITIALLY DEFERRED REFRESH IMMEDIATE REPLICATED

要了解关于 MQT 的更多信息，请参阅关于对表的讨论的适当小节。

如果正在使用 AIX，并且启用了 DB2 概要注册表变量 DB2_FORCE_FCM_BP=YES，那么，当使用多个逻辑分区（即在同一台机器上）时，在分区间传输的数据是通过共享内存处理的，这样就会非常的快。

DB2 v8 Documentation:

Release information ==> Version 8.1 ==> New features ==> Performance enhancements ==> Materialized query tables

Reference ==> SQL ==> Functions ==> Scalar ==> DBPARTITIONNUM

重新平衡数据

使用 REDISTRIBUTE DATABASE PARTITION GROUP 重新平衡各分区间的数据，并更新 hash 分区映射，使其更加平衡。如果已经添加了一个分区，或者发现当前分区之间存在不平衡的数据量，那么这样做就比较有用。

您可以使用 HASHEDVALUE 和 DBPARTITIONNUM SQL 函数来确定当前数据在 hash 分区或数据库分区之间的分布。应避免让太多的数据集中在一个或多个分区，或者让太少的数据分布在一个或多个分区。PARTITION 函数返回表中每一行的分区映射索引，而 DBPARTITIONNUM 函数则返回该行所在的分区号。例如，为了发现一个表的当前分布：

SELECT DBPARTITIONNUM( column), COUNT(*) FROM table GROUP BY DBPARTITIONNUM( column)

DB2 v8 Documentation:

Reference ==> Commands ==> Command Line Processor (CLP) ==> REDISTRIBUTE DATABASE PARTITION GROUP

利用率和瓶颈

下面指出处于 DB2 调优领域之外的一些潜在的问题，这些问题同样能大大降低数据库的性能。

CPU 利用率

如果一个系统中 CPU 的总利用率（用户 + 系统）大于 80%，则认为该系统是 CPU 限制的（CPU bound）。经验法则是，保持 CPU 利用率（大部分情况下）低于 80%，这样可以为处理突然增加的大量活动预留 CPU 的处理能力。如果系统是 CPU 限制的，那么就很难总结出问题所在。这可以是从无效率的访问计划到需要更多 CPU 资源的并发连接过多的任何问题。

在 Unix 中，用 vmstat （例如 "vmstat 3"）进行监视。在 Windows 中，用 Perfmon.exe 或 Task Manager 进行监视。忽略在遇到短的突发事件（1-3 秒）时 100% 运行的 CPU，而关注长期的平均 CPU 利用情况。

清单 19展示了来自 RedHat 8.0 Linux 的某个输出，其中重要的列用粗体强调。您需要观察的列是用户 CPU 利用率（us）和系统 CPU 利用率（sy）。id 列显示了空闲时间。每次都应该忽略掉第一行。这里我们看到的系统有相当高的用户 CPU 利用率，而系统利用率则一般。

清单 19. 示例 vmstat 输出

[db2inst1@65658161 db2inst1]$ vmstat 3 　 procs　　　　　　　　　　　memory　　swap　　　　　io　　 system　　　　　　　　cpu r　b　w　 swpd　 free　 buff　cache　si　so　　bi　　bo　 in　　　　　　cs　us　sy　id 0　0　0　　　0 704388　65460 165592　 0　 0　　 0　　21　 20　　 9　　　　　 2　 2　 6 0　1　1　　　0 642096　65660 206060　 0　 0　 515　2149　911　1047　　　　　18　 9　72 2　1　0　　　0 639712　65668 208292　 0　 0　 139　2287　862　 938　　　　　30　10　60 1　0　0　　　0 629772　65692 215496　 0　 0　　 0　 581　568　 422　　　　　94　 1　 4 1　0　0　　　0 625764　65696 218956　 0　 0　　 0　1809　612　 423　　　　　91　 1　 8 1　0　0　　　0 623752　65704 220752　 0　 0　　11　1741　712　 549　　　　　85　 8　 7 0　0　0　　　0 633548　65712 217768　 0　 0　　11　1264　728　 700　　　　　17　 4　79 1　0　0　　　0 633556　65712 217768　 0　 0　　 0　　87　621　 280　　　　　 5　 7　88 0　0　0　　　0 633556　65712 217768　 0　 0　　 0　　 0　519　 150　　　　　 0　 0 100 1　0　0　　　0 633556　65712 217768　 0　 0　　 0　　 0　523　 154　　　　　 0　 0 100

高的 CPU 利用率有时候要归因于对大型表的过度表扫描或索引扫描。通过分析 "rows read"值（在 SQL 快照中）很高的 SQL 语句，寻找建立索引的可能性。

还可以在进程级上进行监视，以更好地了解是什么正在消耗 CPU。在 UNIX 上用 ps （例如 "ps uax"）进行监视，在 Windows 则用 Perfmon.exe 或 Task Manager 进行监视。忽略掉突发的（1 到 3 秒）100% 利用率的情况，只关注长期的平均值。

例如，在 RedHat Linux 8.0 上，我们可以通过发出 "ps uax" 查看每个进行占用多少的 CPU：

清单 20. 示例 ps 输出

[db2inst1@65658161 tmp]$ ps uax user　　　 PID 　　　　%CPU %MEM　 VSZ　RSS TTY　　　STAT START　 TIME COMMAND db2inst1　9967 　　　　 0.0　2.5 123416 26620 ?　　　S　　Feb15　 0:00 db2agent (idle) db2inst1 10020 　　　　 2.1　7.4 435200 76952 ?　　　R　　Feb15　 2:12 db2agent (TEST1) db2inst1　3643 　　　　 0.1　3.9 249544 41220 ?　　　S　　13:17　 0:00 db2loggw (TEST1) db2inst1　3649 　　　　 0.0　4.0 249540 41320 ?　　　S　　13:17　 0:00 db2pclnr

磁盘利用率

如果一个系统中磁盘利用率一般超过 45%，则认为该系统是 I/O 限制的。如果存在磁盘瓶颈，那么应确保表空间的容器分布在所有可用磁盘上。如果磁盘利用率仍然很高，那么很可能就需要更多的磁盘。

不幸的是，取决于所使用的操作系统，iostat 有不同格式的输出。在 UNIX 中，用 iostat （例如 "iostat 3"）进行监视，而在 Windows 中则用 Perfmon.exe 进行监视。忽略掉突发（1-3 秒）的利用率 100% 的情况，只关注长期的平均利用率。

如果使用的是 Linux 操作系统，则使用 "iostat –d –x 3" 命令以便支持扩展的磁盘信息，并寻找服务时间大于 50 ms（svctm）的磁盘，以及利用率超过 45% 的磁盘。由于格式的关系，下面的输出中省略了某些数据列。

清单 21. 示例 iostat 输出

[db2inst1@65658161 tmp]$ iostat -d -x 3 Linux 2.4.18-14 (65658161.torolab.ibm.com)　　　02/18/2004 Device:　　 r/s　 w/s　rsec/s　wsec/s　　rkB/s　　wkB/s　await 　　　　 svctm　%util /dev/hda　 0.01　2.25　　0.19　 41.81　　 0.09　　20.91　 0.60 　　　　　1.88　 0.42 /dev/hda1　0.00　0.00　　0.00　　0.00　　 0.00　　 0.00 277.20 　　　　176.86　 0.00 /dev/hda2　0.00　0.00　　0.00　　0.00　　 0.00　　 0.00　 4.11 　　　　　4.11　 0.00 /dev/hda3　0.01　2.25　　0.19　 41.81　　 0.09　　20.91　 0.58 　　　　　0.66　 0.15 Device:　　 r/s　 w/s　rsec/s　wsec/s　　rkB/s　　wkB/s　await 　　　　 svctm　%util /dev/hda　 0.00 383.67　 0.00 5632.00　　 0.00　2816.00　 8.10 　　　　　1.35　51.97 /dev/hda1　0.00　0.00　　0.00　　0.00　　 0.00　　 0.00　 0.00 　　　　　0.00　 0.00 /dev/hda2　0.00　0.00　　0.00　　0.00　　 0.00　　 0.00　 0.00 　　　　　0.00　 0.00 /dev/hda3　0.00 383.67　 0.00 5632.00　　 0.00　2816.00　 8.10 　　　　　2.16　82.93

内存和调页空间的利用率

从 DB2 的角度来看，如果一个系统发生了换页，则称该系统是内存限制的（memory bound）的。一旦开始换页，性能通常就会急剧下降。

在 UNIX 中，使用 "lsps 鈥揳" 列出调页空间的特征，并且用 vmstat （例如 "vmstat 3"）进行监视。在 Windows 中，用 Perfmon.exe 或 Task Manager 进行监视。

例如，在 RedHat Linux 8.0 中，您需要注意交换（swap）信息。特别地，换入（si）和换出（so）列显示了从磁盘换入内存的空间大小和从内存换出到磁盘的空间大小，以 KB/sec 为单位（在 AIX 上是以 4K pages/sec 为单位的）。

清单 22. 示例 iostat 输出

[db2inst1@65658161 tmp]$ vmstat 3 　 procs　　　　　　　　　　　memory　　　　　　swap　　　　　io　　 system　　　　 cpu r　b　w　 swpd　 free　 buff　cache　　　　　si　so　　bi　　bo　 in　　cs　us　sy　id 0　0　0　　　0 675160　66088 175908　　　　　 0　 0　　 0　　21　 22　　 9　 2　 2　 6 0　0　0　　　0 675096　66088 175908　　　　　 0　 2　　 0　　37　624　 246　 5　 7　88 2　0　0　　　0 665376　66088 183408　　　　　 0　 0　　11　　88　666　 826　 6　 8　85 1　0　0　　　0 665376　66088 183408　　　　　 1　 0　　 0　　76　623　 452　 5　 8　88 2　0　0　　　0 654748　66096 191112　　　　　 0　 0　　79　　48　619　 847　 2　 4　94 3　0　0　　　0 652760　66096 191192　　　　　 0　 0　　15　　47　578　 791　 2　 2　96

网络利用率

虽然通常网络不是一个重大的瓶颈，但在这方面进行某些调优也可以提高性能。如果一个系统的 CPU 和 I/O 利用率都很低，则认为该系统是网络限制的（Network bound），并且在通过网络与 DB2 服务器进行通信时存在性能问题。在一个分了区的数据库中，如果分区策略产生了一些非合并连接，则可能导致最严重的性能下降。

在 UNIX 中可以用 netpmon （例如 "netpmon -O all -o netpmon.out"）进行监视，在 Windows 中可以用 Perfmon.exe 进行监视。