对访问计划的解释

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

这种信息可以帮助您：

• 理解为一个查询选择的执行计划。
• 辅助设计应用程序的程序。
• 确定应何时重新捆绑应用程序。
• 辅助数据库设计。

在获得一条 SQL 语句的解释数据之前，必须使用跟调用 explain 工具的授权 ID 相同的模式定义一组 explain 表。请查看 DB2 安装目录下的 'qllib/misc/explain.ddl' 或 'qllib\\misc\\explain.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 屏幕

 

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

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

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

delete from schema.explain_instance

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

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

图 3. Text Explain 屏幕

 

这里不像 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 命令

使用一条 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