db2数据库日常维护-reorgtable

转）DB2 日常维护REORG TABLE 命令优化数据库性能2009-04-24 16:18一个完整的日常维护规范可以帮助 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-.-+-+- 70F3: 100 * (Required Pages / Total Pages) 80SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG-DB2INST1 STAFF - - - - - - - - -*-.索引统计信息：F4: CLUSTERRATIO 或正常化的 CLUSTERFACTOR 80F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / (NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) 50F6: (100 - PCTFREE) * (INDEXPAGESIZE - 96) / (ISIZE + 12) * (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) -REORG TABLE-table-name-+-+-INDEX-index-name-+-+- db2 reorg table rhette.testDB20000I REORG 命令成功完成。要使用临时表空间 TEMPSPACE1 重组表 RHETTE.TEST，可以在 DB2CLP 窗口中使用带 USE TEMPSPACE1 选项的 REORG TABLE 命令。使用临时表空间 TEMPSPACE1 重组 RHETTE.TEST 表C: db2 reorg table rhette.test use TEMPSPACE1DB20000I 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 a1DB20000I 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 方式的联机索引重组。shuqian注： REORG INDEXES 命令的 CLEANUP ONLY 选项不能完全重组索引。 CLEANUP ONLY ALL 选项将除去那些标记为“删除”且被认为要落实的键。此外，它还将释放所有标记为“删除”且被认为要落实的键所在的页。在释放页后，相邻的叶子页将会合并， 前提是这样做可以在合并页上至少留出 PCTFREE 可用空间。PCTFREE 是指在创建索引时为其定义的可用空间百分比。 CLEANUP ONLY PAGES 选项仅删除那些标记为“删除”且被认为要落实的所有键所在的页。使用 CLEANUP ONLY 选项对分区表的索引进行重组时，支持任何访问级别。如果未指定 CLEANUP ONLY 选项，则缺省访问级别 ALLOW NO ACCESS 是唯一支持的访问级别。REORG INDEXES 具有下列要求：1）对索引和表具有 SYSADM、SYSMAINT、SYSCTRL 或 DBADM 权限，或者具有 CONTROL 特权。2）用于存储索引的表空间的可用空间数量等于索引的当前大小在发出 CREATE TABLE 语句时，考虑在大型表空间中重组索引。3）其他日志空间REORG INDEXES 需要记录其活动。因此，重组可能会失败，尤其是在系统繁忙和记录其他并发活动时。注： 如果具有 ALLOW NO ACCESS 选项的 REORG INDEXES ALL 命令运行失败，则会标记索引无效并且此项操作不可撤销。但是，如果具有 ALLOW READ ACCESS 选项的 REORG 命令或具有 ALLOW WRITE ACCESS 选项的 REORG 命令运行失败，则可以复原原来的索引对象。另外还需要注意，在 DB2 V9 开放平台上对新引入的表分区功能来说，不能联机重组分区表。4.对表和索引进行 runstatsDB2 优化器使用目录统计信息来确定任何给定查询的最佳访问方案。如果有关表或索引的统计信息已过时或者不完整，则会导致优化器选择不是最佳的方案，并且会降低 执行查询的速度。但是，决定要为给定的工作负载收集哪些统计信息是很复杂的事情，并且使这些统计信息保持最新是一项很花费时间的任务。以往，建议对一个频繁进行大量更新、插入或者删除操作的表进行 RUNSTATS，建议在重组表之后运行 RUNSTATS 。我们都是通过手工执行 RUNSTATS 命令，或者通过 DB2 任务中心调度执行 RUNSTATS 命令来收集表的统计信息以改善数据库性能。现在 DB2 V9 又新增了自动收集统计信息功能， 自动收集统计信息通过收集最新的表统计信息来改善数据库性能。 DB2 首先确定工作负载需要哪些统计信息以及需要更新哪些统计信息，然后，在后台自动调用 RUNSTATS 实用程序以确保收集并维护正确的统计信息。然后，DB2 优化器根据准确的统计信息来选择访问方案。从 IBM DB2 版本 9.1 开始，在创建新的数据库时，缺省情况下会启用自动收集统计信息（RUNSTATS）功能。这表示 DB2 数据库管理器将确定是否需要更新数据库统计信息。然后，RUNSTATS 实用程序会根据需要在后台自动运行，以确保最新的数据库统计信息可用。用户在创建数据库之后，可以通过将数据库配置参数 AUTO_RUNSTATS 设置为 OFF 来禁用自动收集统计信息。需要注意的是，将数据库从版本 8 迁移到版本 9.1 时，不会自动启用此功能。要在已迁移的数据库中使用此功能，必须手工进行启用。通过使用自动统计信息收集功能，可以让 DB2 确定是否需要更新数据库统计信息。在启用了自动统计信息收集功能的情况下，DB2 将自动在后台运行 RUNSTATS 实用程序以确保最新的数据库统计信息可用.RUNSTATS命令的语法如下：-RUNSTATS ON TABLE-table-name-+-+-+-+| -WITH DISTRIBUTION-+-+- | -AND-+-+-+-INDEXES ALL-+- | -DETAILED- -INDEX-index-name- |-+-+-+-AND-+-+-+-+-INDEXES ALL-+-FOR- -DETAILED- -INDEX-index-name-+-+- db2 runstats on table rhette.test and index allDB20000I RUNSTATS 命令成功完成。收集索引统计信息以允许优化器评估是否应该使用索引来解析查询。如果想收集索引统计信息，必须连接至包含表和索引的数据库并具有下列其中一个权限级别：sysadm 、sysctrl 、sysmaint 、dbadm 、对表的 CONTROL 特权 。带 SAMPLED DETAILED 选项执行 RUNSTATS 命令需要 2MB 统计信息堆。将附加的 488 个 4K 页分配给为此附加内存需求设置的 stat_heap_sz 数据库配置参数。如果该堆看起来太小，则 RUNSTATS 在尝试收集统计信息之前会返回一条错误。现在我们在 DB2CLP 窗口中，连上示例数据库 SAMPLE，在示例数据库中存在表 RHETTE.PROJECT，其有两个索引 PK_PROJE CT 和 XPROJ2，我们要创建索引 PK_PROJE CT 和 XPROJ2 的详细统计信息，可以发出带 AND DETAILED INDEXES ALL 选项的 RUNSTATS 命令。创建索引 PK_PROJECT 和 XPROJ2 的详细统计信息C: db2 RUNSTATS ON TABLE ject AND DETAILEDINDEXES ALL DB20000I RUNSTATS 命令成功完成。命令成功完成。如果我们想创建两个索引的详细统计信息，但是不想耗费太多的资源和时间，可以使用采样的方式而不是对每个索引条目执行详细计算。创建索引 PK_PROJECT 和 XPROJ2 的详细统计信息，使用采样方式C: db2 RUNSTATS ON TABLE ject AND SAMPLED DETAILEDINDEXES ALL DB20000I RUNSTATS 命令成功完成。命令成功完成。如果要创建索引上的详细采样统计信息以及表的分布统计信息，以便索引和表统计信息一致，可以使用带 WITH DISTRIBUTION 选项的 RUNSTATS 命令。收集表 ject 的详细统计信息C: db2 RUNSTATS ON TABLE jectWITH DISTRIBUTION ON KEY COLUMNS AND SAMPLEDDETAILED INDEXES ALL DB20000I RUNSTATS 命令成功完成。命令成功完成。如果想全面收集表和索引的信息，而不是使用抽样的方式收集表 ject 的详细统计信息C: db2 RUNSTATS ON TABLE ject WITH DISTRIBUTIONAND DETAILED INDEXES ALL DB20000I RUNSTATS 命令成功完成。5 (可选) 上面命令完成后可以重复第二步，检查 REORG的结果，如果需要，可以再次执行 REORG和 RUNSTATS命令。6 BIND或 REBINDRUNSTATS命令运行后，应对数据库中的 PACKAGE进行重新联编，简单地，可以使用 db2rbind命令来完成。db2rbind命令的语法如下：-db2rbind-database-/l logfile-+-+-all-.-conservative-.-+-+-/r-+-any-+-/u userid-/p password-例如，如果数据库名为 SAMPLE，执行：db2rbind sample -l db2rbind.out二、DB2 V8.2 如何配置 AUTO_RUNSTATS1.配置参数首先确保 DBM参数 HEALTH_MON是 ON,然后使用如下命令打开 AUTO_RUNSTATS 的开关。db2 update db cfg using AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON2.设置需要自动 RUNSTATS的表的 profile，该 profile会纪录于系统表sysibm.systables的 STATISTICS_PROFILE列例如:db2 runstats on table huangdk.hasales and indexes all tablesample system(20) set profile only 说明：a.RUNSTATS 的语法见命令指南/in . c/core/r0001980.htmb.tablesample 是采样 runstats的选项，例子里采用 system的方法，采样 20%3.AUTO_RUNSTATS触发条件配置完后，需要重起一下实例，然后系统会在第一次连接请求时，去触发AUTO_RUNSTATS，如果表需要 RUNSTATS，就开始 RUNSTATS。以后，系统每隔 3小时触发 AUTO_RUNSTATS用户可以使用以下语句检查：db2 select STATS_TIME,SUBSTR(STATISTICS_PROFILE,1,200) from sysibm.systables其中，STATS_TIME 列指示最后一次 RUNSTATS的时间。另外，db2diag.log 里有这样的信息：2004-10-27-16.08.39.007000+480 I30117H327 LEVEL: EventPID : 2596 TID : 3404 PROC : db2fmp.exeINSTANCE: DB2 NODE : 000FUNCTION: DB2 UDB, Automatic Table Maintenance, db2HmonEvalStats, probe:100START : Automatic Runstats: evaluation has started on database TBCSAMPL指示自动 RUNSTATS已经运行。三、优化语句的批量获取1、查找数据库管理表空间（DMS）SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D ;2、DMS 下的 creator（用户、模式）SELECT DISTINCT creatorFROM sysibm.systablesWHERE tbspace IN (SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D );DB2ADMIN3、creator 下的表数量SELECT creator,count(1)FROM sysibm.systablesWHERE Type = T AND tbspace IN (SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D)GROUP BY creator ;-DB2ADMIN 564、构造优化脚本-=(1).reorgchk update statistics on table SCHEMA.TABLE ;select reorgchk update statistics on table DB2ADMIN.|rtrim(name)|; from sysibm.systables where creator = DB2ADMIN AND Type = T AND tbspace IN (SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D)-=(2).reorg table SCHEMA.TABLE ;select reorg table DB2ADMIN.|rtrim(name)|; from sysibm.systables where creator = DB2ADMIN AND Type = T AND tbspace IN (SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D)-=(3).reorg indexes all for table SCHEMA.TABLE ;select reorg indexes all for table DB2ADMIN.|rtrim(name)|; from sysibm.systables where creator = DB2ADMIN AND Type = T AND tbspace IN (SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D-=(4).runstats on table SCHEMA.TABLE and indexes all; select runstats on table DB2ADMIN.|rtrim(name)| and indexes all;from sysibm.systableswhere creator = DB2ADMIN AND Type = T ANDtbspace IN (SELECT TBSpace FROM syscat.tablespaces WHERE TBSpaceType = D) ;-=UPDATE STATISTICS在指定的表或索引视图中，对一个或多个统计组（集合）有关键值分发的信息进行更新。若要基于列生成统计，请参见 CREATE STATISTICS。 语法UPDATE STATISTICS table | view index| ( statistics_name ,.n ) WITH FULLSCAN | SAMPLE number PERCENT | ROWS | RESAMPLE , ALL | COLUMNS | INDEX , NORECOMPUTE 参数table | view要更新统计的表或索引视图的名称。表名和视图名必须符合标识符的规则。有关更多信息，请参见使用标识符。由于索引名在每个数据库中不唯一，所以必须指定 table 或 view。可选择指定数据库、表或视图所有者。只有在 Microsoft SQL Server 2000 企业版中才支持索引视图。index要更新统计的索引。索引名必须符合标识符的规则。如果未指定 index，则更新指定表或索引视图中的所有索引的分发统计。若要查看索引名和描述的列表，请带表名或视图名执行 sp_helpindex。statistics_name要更新的统计组（集合）的名称。统计名称必须符合标识符规则。有关生成统计组的更多信息，请参见 CREATE STATISTICS。n是表示可以指定多个 statistic_name 组的占位符。FULLSCAN指定应读取 table 或 view 中的所有行以收集统计。FULLSCAN 提供与 SAMPLE 100 PERCENT 相同的行为。FULLSCAN 不能与 SAMPLE 选项一起使用。SAMPLE number PERCENT | ROWS 当为较大的表或视图收集统计时，指定要采样的表或索引视图的百分比或行数。 number 只允许使用整数，无论它是 PERCENT 还是 ROWS。若要对较大的表或视图使用默认采样行为，请将 SAMPLE number 和 PERCENT 或 ROWS 一起使用。Microsoft SQL Server 将确保值的采样数不低于某一数目，以保证统计有用。如果 PERCENT、ROWS 或 number 选项导致要采样的行数过小，SQL Server 则自动根据表或视图中的现有行数改正采样。说明 默认行为是在目标表或索引视图上进行采样扫描。SQL Server 自动计算所需的样本大小。RESAMPLE指定使用从所有现有统计（包括索引）继承的采样速率来收集统计。如果采样速率导致要采样的行过少，SQL Server 则自动根据表或视图中的现有行数改正采样。ALL | COLUMNS | INDEX指定 UPDATE STATISTICS 语句是否影响列统计、索引统计或所有现有统计。如果未指定选项，则 UPDATE STATISTICS 语句影响所有的统计。每个 UPDATE STATISTICS 语句只能指定一种类型（ALL、COLUMNS 或 INDEX）。 NORECOMPUTE指定过期统计不自动重新计算。统计过期与否取决于在索引列上进行的 INSERT、UPDATE 和 DELETE 操作的数量。指定该选项时，将导致 SQL Server 禁用自动统计重建功能。若要还原自动统计重新计算，请重新执行 UPDATE STATISTICS（不要 NORECOMPUTE 选项），或者执行 sp_autostats。重要 禁用自动统计重新计算会导致 SQL Server 查询优化器对于涉及指定表的查询选择非最佳的策略。注释SQL Server 保留每个索引中关于键值分发的统计，并且使用这些统计来决定查询处理中使用哪个（或哪些）索引。用户可以通过使用 CREATE STATISTICS 语句生成基于非索引列的统计。查询优化依赖于分发步骤的准确性： 如果索引中的键值有显著变化，请对此索引重新运行 UPDATE STATISTICS。 如果索引列中添加、更改或删除大量数据（即如果键值分发更改） ，或者用 TRUNCATE TABLE 语句将表截断然后重新填充，请使用 UPDATE STATISTICS。 若要查看统计最近一次更新的时间，请使用 STATS_DATE 函数。只有当能够在计算列上创建索引时，才可以在包含这