DB2日常维护之优化

 一个完整的日常维护规范可以帮助 DBA 理顺每天需要的操作，以便更好的监控和维护数据库，保证数据库的正常、安全、高效运行，防止一些错误重复发生。

    由于DB2使用CBO作为数据库的优化器，数据库对象的状态信息对数据库使用合理的 ACCESS PLAN至关重要。DB2 优化器使用目录统计信息来确定任何给定查询的最佳访问方案。如果有关表或索引的统计信息已过时或者不完整，则会导致优化器选择不是最佳的方案，并且会降低 执行查询的速度。当数据库里某个表中的记录变化量很大时，需要在表上做REORG操作来优化数据库性能

    一、完整的REORG表的过程

    值得注意的是，针对数据库对象的大量操作，如反复地删除表，存储过程，会引起系统表中数据的频繁改变，在这种情况下，也要考虑对系统表进行REORG操作。一个完整的REORG表的过程应该是由下面的步骤组成的：

    RUNSTATS ->   REORGCHK ->   REORG ->   RUNSTATS -> BIND或REBIND

    注： 执行下面命令前要先连接数据库

    1 RUNSTATS

    由于在第二步中REORGCHK时可以对指定的表进行RUNSTATS操作(在REORGCHK时指定UPDATE STATISTICS)，所以第一步事实上是可以省略的。

    2 REORGCHK

    在对表数据进行许多更改之后，逻辑上连续的数据可能会位于不连续的物理数据页上，在许多插入操作创建了溢出记录时尤其如此。按这种方式组织数据时，数据库管理器必须执行其他读操作才能访问顺序数据。另外，在删除大量行后，也需要执行其他的读操作。

    表重组操作会整理数据碎片来减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。还可以指定根据特定索引来重新排序数据，以便查询时通过最少次数据读取操作就可以访问数据。

    下列任何因素都可能指示用户应该重组表：

    1）自上次重组表之后，对该表进行了大量的插入、更新和删除活动 。

    2）对于使用具有高集群率的索引的查询，其性能发生了明显变化 。

    3）在执行 RUNSTATS 命令以刷新统计信息后，性能没有得到改善。

    4）REORGCHK 命令指示需要重组表（注意：在某些情况下，REORGCHK 总是建议重组表，即使在执行了重组后也是如此）。例如，如果使用 32KB 页大小，并且平均记录长度为 15 字节且每页最多包含 253 条记录，则每页具有 32700- (15 x 253)=28905 个未使用字节。这意味着大约 88% 的页面是可用空间。用户应分析 REORGCHK 的建议并针对执行重组所需的成本平衡利益。

    5）db.tb_reorg_req（需要重组）运行状况指示器处于 ATTENTION 状态。此运行状况指示器的集合详细信息描述通过重组可获得好处的表和索引的列表。

    REORGCHK 命令返回有关数据组织的统计信息，并且可以建议您是否需要重组特定表。然而，定期或在特定时间对目录统计信息表运行特定查询可以提供性能历史记录，该记录使用户可以发现可能具有更广性能隐含的趋势。

    DB2 V9.1 引入了自动重组功能，可以对表和索引进行自动重组。自动重组通过使用 REORGCHK 公式来确定何时需要对表进行重组。它会定期评估已经更新了统计信息的表，以便了解是否需要重组。

    REORGCHK命令的语法如下：

    .                           -UPDATE STATISTICS--.

    >>-REORGCHK--+--------------------+----------------------------->

    '-CURRENT STATISTICS-'

    .-ON TABLE USER-----------------.

    >--+-------------------------------+---------------------------><

    '-ON--+-SCHEMA--schema-name---+-'

    | .-USER-------. |

    '-TABLE--+-SYSTEM-----+-'

    +-ALL--------+

    '- table-name-'

    下面我们来看一下各个选项的含义：

    UPDATE STATISTICS：更新表的统计数据，根据该统计数据判断是否需要重组表。

    CURRENT STATISTICS：根据当前表统计数据判断是否需要重组表。

    TABLE table_name：对单个表进行分析。

    TABLE ALL：对数据库所有的表进行分析。

    TABLE SYSTEM：对系统表进行分析。

    TABLE USER：对当前用户模式下的所有表进行分析。

    如果数据库中数据量比较大，在生产系统上要考虑REORGCHK的执行时间可能较长，需安排在非交易时间执行。

    可以分为对系统表和用户表两部分分别进行REORGCHK：

    1) 针对系统表进行REORGCHK

    db2 reorgchk update statistics on table system

    使用UPDATE STATISTICS参数指定数据库首先执行RUNSTATS命令。

    2) 针对用户表进行REORGCHK

    db2 reorgchk update statistics on table user

    REORGCHK是根据统计公式计算表是否需要重整。对于每个表有3个统计公式，对索引有3个统计公式(版本8开始有5个公式)，如果公式计算结果该表需重整，在输出的REORG字段中相应值为*，否则为-。

    reorgchk 所使用的度量的考虑因素包括：（当查看 reorgchk 工具的输出时，找到用于表的 F1、F2 和 F3 这几列，以及用于索引的 F4、F5、F6、F7 和 F8 这几列。如果这些列中的任何一列有星号 (*)，则说明当前的表和/或索引超出了阈值。）

    F1: 属于溢出记录的行所占的百分比。当这个百分比大于 5% 时，在输出的 F1 列中将有一个星号 (*)。

    F2: 数据页中使用了的空间所占的百分比。当这个百分比小于 70% 时，在输出的 F2 列上将有一个星号 (*)。

    F3: 其中含有包含某些记录的数据的页所占的百分比。当这个百分比小于 80% 时，在输出的 F3 列上将有一个星号 (*)。

    F4: 群集率，即表中与索引具有相同顺序的行所占的百分比。当这个百分比小于 80% 时，那么在输出的F4 列上将有一个星号 (*)。

    F5: 在每个索引页上用于索引键的空间所占的百分比。当这个百分比小于 50% 时，在输出的 F5 列上将有一个星号 (*)。

    F6: 可以存储在每个索引级的键的数目。当这个数字小于 100 时，在输出的 F6 列上将有一个星号 (*)。

    F7: 在一个页中被标记为 deleted 的记录 ID（键）所占的百分比。当这个百分比大于 20% 时，在输出的 F7 列上将有一个星号 (*)。

    F8: 索引中空叶子页所占的百分比。当这个百分比大于 20% 时，在输出的 F8 列上将有一个星号 (*)。

    下面是执行的部分结果

    db2 reorgchk update statistics on table user

    执行 RUNSTATS ....

    表统计信息：

    F1: 100 * OVERFLOW / CARD < 5

    F2: 100 * (Effective Space Utilization of Data Pages) > 70

    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE F1 F2 F3 REORG

    ----------------------------------------------------------------------------------------

    DB2INST1 STAFF                  -       -     -     -            -      -   -   - -*-

    ...

    索引统计信息：

    F4: CLUSTERRATIO 或正常化的 CLUSTERFACTOR > 80

    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50

    F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100

    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20

    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

    SCHEMA   NAME                 CARD LEAF ELEAF LVLS ISIZE NDEL   KEYS F4 F5 F6 F7 F8 REORG

    -------------------------------------------------------------------------------------------------

    表：DB2INST1.STAFF

    DB2INST1   ISTAFF             -     -     -     -     -     -       -    -    -   -   -   - -----

    ...

    从上面的例子来看，对于表DB2INST1.STAFF，根据统计公式F2计算结果，有必要对表进行REORG。

    3、对需要重组的表进行重组(REORG TABLE)

    DB2 V9 可以对表和索引进行自动重组。要进行高效率的数据访问和获得最佳工作负载性能，具有组织良好的表数据是很关键的。在对表数据进行许多更改之后，逻辑上连续 的数据可能会位于不连续的物理数据页上，在许多插入操作创建了溢出记录时尤其如此。按这种方式组织数据时，数据库管理器必须执行其他读操作才能访问顺序数 据。另外，在删除大量行后，也需要执行其他的读操作。表重组操作会整理数据碎片来减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速 度并最终提高查询性能。还可以指定根据特定索引来重新排序数据，以便查询通过最少次数据读取操作就可以访问数据。既可重组系统目录表，也可以重组数据库 表。由 RUNSTATS 收集的统计信息与其他信息一起来显示表中的数据分布情况。特别是，通过分析这些统计信息可以知道何时需要执行哪种类型的重组。自动重组通过使用 REORGCHK 公式来确定何时需要对表进行重组。它会定期评估已经更新了统计信息的表，以便了解是否需要重组。如果需要重组，则它会在内部调度对表进行传统重组。这将要 求执行应用程序功能而不对正在重组的表进行写访问。可以使用 auto_reorg、auto_tbl_maint 和 auto_maint 数据库配置参数来启用或禁用自动重组功能部件。在分区数据库环境中，确定执行自动重组和启动自动重组是在目录分区上完成的。只需要在目录分区上启用数据库 配置参数，就将在目标表所在的所有数据库分区上运行重组。如果用户不太确定何时以及如何重组表和索引，则可以将自动重组作为整个数据库维护方案的一部分。

    如果是 DB2 V8.2.2 以前的版本，是没有自动重组功能的。如果你的数据库是 DB2 V8.2.2 以前的版本，或者想在 DB2 V8.2.2 以后的版本里手工运行重组表，可以使用命令完成。手工重组可以使用 REORG 命令来完成。

    以脱机方式重组表是整理表碎片的最快方法。重组可减少表所需的空间量并提高数据访问和查询性能。

    必须具有 SYSADM、SYSCTRL、SYSMAINT 或 DBADM 权限，或者必须具有对表的 CONTROL 权限才能重组表。必须具有数据库连接才能重组表。标识需要重组的表之后，可以对这些表运行 REORG 实用程序，并且可以选择对在这些表上定义的任何索引运行该实用程序。

    REORG TABLE命令的语法如下：

    >>-REORG TABLE--table-name----+--------------------+------------>

    '-INDEX--index-name--'

    >-----+-----------------------+--------------------------------><

    '-USE--tablespace-name--'

    执行REORG可以考虑分为表上有索引和没有索引两种情况：

    1) 如果表上有索引

    如表名为DB2INST1.STAFF，索引名为DB2INST1.ISTAFF，命令如下：

    db2 reorg table db2inst1.staff index db2inst1.istaff use tempspace1

    建议REORG时使用USE参数指定数据重排时使用的临时表空间，否则，REORG工作将会在表所在表空间中原地执行。

    如果表上有多个索引，INDEX参数值请使用最为重要的索引名。

    2) 如果表上没有索引

    如表名为DB2INST1.STAFF, SYSIBM.SYSTABLES

    db2 reorg table db2inst1.staff use tempspace1

    db2 reorg table sysibm.systables use tempspace1

    使用 REORG TABLE 命令重组 RHETTE.TEST 表

    C:> db2 reorg table rhette.test

    DB20000I   REORG 命令成功完成。

    要使用临时表空间 TEMPSPACE1 重组表 RHETTE.TEST，可以在 DB2CLP 窗口中使用带 USE TEMPSPACE1 选项的 REORG TABLE 命令。

    使用临时表空间 TEMPSPACE1 重组 RHETTE.TEST 表

    C:> db2 reorg table rhette.test   use TEMPSPACE1

    DB20000I   REORG 命令成功完成。

    下面我们在示例表 TEST 上创建一个索引 A1，构建在 ID 列上。此时我们想要重组表并根据索引 A1 对行进行重新排序，可以使用带 INDEX 选项的 REORG TABLE 命令。

    根据索引 A1 重组 RHETTE.TEST 表

    C:> db2 create index   a1 on rhette.test(id)

    DB20000I   SQL 命令成功完成。

    C:> db2 reorg table rhette.test   index a1

    DB20000I   REORG 命令成功完成。

    如果想使用 SQL 调用语句重组表，请使用 ADMIN_CMD 过程发出 REORG TABLE 命令。

    使用 SQL 调用语句重组 RHETTE.TEST 表

    C:> db2 call sysproc.admin_cmd ( 'reorg table rhette.test index a1' )

    返回状态 = 0

    要使用 DB2 管理 API 重组表，请使用 db2REORG API 。 在重组表之后，应收集有关表的统计信息，以便优化器具有最准确的数据来评估查询访问方案。另外，记住在分区数据库环境中，如果想在所有节点运行命令，需要使用 db2_all 命令。

    通过删除和插入操作对表进行更新后，索引的性能会降低，其表现方式如下：

    1）叶子页分段。

    叶子页被分段之后，由于必须读取更多的叶子页才能访存表页，因此 I/O 操作成本会增加。

    2）物理索引页的顺序不再与这些页上的键顺序相匹配（此称为不良集群索引）。

    叶子页出现不良集群情况后，顺序预取操作的效率将降低，因此会导致更多的 I/O 等待。

    3）形成的索引大于其最有效的级别数。

    在此情况下应重组索引。

    如果在创建索引时设置了 MINPCTUSED 参数，则在删除某个键且可用空间小于指定的百分比时，数据库服务器会自动合并索引叶子页。此过程称为联机索引整理碎片。但是，要复原索引集群和可用空间以及降低叶级别，请使用下列其中一种方法：

    1）删除并重新创建索引。

    2）使用 REORG INDEXES 命令联机重组索引。

    因为此方法允许用户在重建表索引期间对表进行读写操作，所以在生产环境中可能需要选择此方法。

    3）使用允许脱机重组表及其索引的选项运行 REORG TABLE 命令。

    在使用 ALLOW WRITE ACCESS 选项运行 REORG INDEXES 命令时，如果同时允许对指定的表进行读写访问，则会重建该表的所有索引。进行重组时，对基础表所作的任何将会影响到索引的更改都将记录在 DB2 日志中。另外，如果有任何内部内存缓冲区空间可供使用，则还将这些更改放在这样的内存空间中。重组将处理所记录的更改以便在重建索引时与当前写活动保持同 步更新。内部内存缓冲区空间是根据需要从实用程序堆中分配的指定内存区域，它用来存储对正在创建或重组的索引所作的更改。使用内存缓冲区空间使索引重组操 作能够通过这样的方式来处理更改，即先直接从内存读取，然后读取日志（如有必要），但读取日志的时间要晚得多。在重组操作完成后，将释放所分配的内存。重 组完成后，重建的索引可能不是最佳集群的索引。如果为索引指定 PCTFREE，则在重组期间，每页上均会保留相应百分比的空间。

    对于分区表，支持对各个索引进行联机索引重组和清除。要对各个索引进行重组，指定索引名：REORG INDEX index_name for TABLE table_name

    对于空间索引或多维集群（MDC）表，不支持采用 ALLOW WRITE 方式的联机索引重组。

    注： REORG INDEXES 命令的 CLEANUP ONLY 选项不能完全重组索引。 CLEANUP ONLY ALL 选项将除去那些标记为“删除”且被认为要落实的键。此外，它还将释放所有标记为“删除”且被认为要落实的键所在的页。在释放页后，相邻的叶子页将会合并， 前提是这样做可以在合并页上至少留出 PCTFREE 可用空间。 PCTFREE 是指在创建索引时为其定义的可用空间百分比。 CLEANUP ONLY PAGES 选项仅删除那些标记为“删除”且被认为要落实的所有键所在的页。