db2的访问计划、runstats、reorg、rebind

对于db2 SQL语句执行计划的自我理解

标签：访问计划、执行计划、runstats、rebind、reorg、reorgchk

（一）访问计划和执行计划

将一条 SQL 语句提交给 DB2 数据库引擎进行处理时，DB2 Optimizer 会对其加以分析，以生成所谓的访问计划（access plan）。访问计划包括将用于执行该语句的策略的详细信息（例如是否使用索引；若有排序方法，需要怎样的排序方法等）。

（1）               该 SQL 语句是在一个应用程序中编写的（静态sql，static sql），则访问计划生成于预编译时（若使用了延时绑定，则在绑定时生成），另外还会生成一个可执行形式的访问计划，它作为称为 “包（package，是一种数据库对象）” 的对象存储在系统目录中。

（2）               语句是通过 Command Line Processor 提交的，或者语句是应用程序中的一条动态 SQL 语句（也就是说，这是一条在应用程序运行时构造的 SQL 语句），则访问计划将在该语句发出时生成，而所生成的可执行形式则临时地存储在内存中（位于全局包缓冲区pck cache heap中），而不是系统目录。（若发出了一条 SQL 语句，而全局包缓冲区中已有其可执行形式的访问计划，则已有访问计划将被重用，不会再次调用 DB2 Optimizer。）

DB2 优化器（DB2 Optimizer）如何为这些包选择访问计划呢？它依赖于在创建包时的数据库统计信息。

（二）runstats

RUNSTATS 实用程序用于更新关于表和相关索引的物理特征的统计信息。这些特征包括记录的数量（基数）、页数、平均记录长度等。

对于静态 SQL 语句（包含在应用程序中），包所依赖的数据库统计信息是当时创建包时的统计信息。如果对统计信息进行了更新（以反映最近物理数据库特征），那么也应该更新已有的包（存在系统目录表中）。

如何让已有的包重新应用最近的数据库统计信息呢？

（三）rebind

REBIND 实用程序允许重新创建一个包，以便使用当前的数据库统计信息。这个命令非常简单：

REBIND PACKAGE package_name

在很多情况下，SQL 语句包含了主机变量、参数占位符和专用寄存器。这些变量的值只有到运行时才是已知的。通过 REBIND 命令中的 REOPT 子句，可以指定是否让 DB2 使用主机变量、参数占位符和专用寄存器的实际值来优化访问路径。有三种 REOPT 选项：

·             NONE —— SQL 语句中使用的主机变量、参数占位符和专用寄存器的实际值将不用于优化访问路径。相反，优化访问路径时使用的是这些变量的默认估计值。

·             ONCE —— 当第一次执行查询时，使用主机变量、参数占位符或专用寄存器的实际值优化给定 SQL 语句的访问路径。

·             ALWAYS —— 总是使用主机变量、参数占位符或专用寄存器的值编译和预优化给定的 SQL 语句的访问路径。

REBIND PACKAGE ACCTPKG REOPT ONCE

 

然而，如果要更改应用程序源，那么需要显式地删除、并重新创建已有的相关包。REBIND 实用程序不用于这个目的。这里我们向您提醒这一点，是因为 DBA 经常误解 REBIND 的用途。

对于动态 SQL 语句：

由于它们是在运行时预编译并存储在包缓存（pck cache heap）中的。如果更新了统计信息，那么可以刷新缓存，以便再次编译动态 SQL 语句，从而将更新后的统计信息考虑进去。（否则，若发出了一条 SQL 语句，而全局包缓冲区中已有其可执行形式的访问计划，则已有访问计划将被重用，即仍然使用过期的统计信息）。命令如下：

FLUSH PACKAGE CACHE DYNAMIC

 

以上所做的工作都是在已有的物理数据库特征上进行一些逻辑信息的收集和判断，以便更好地执行sql语句。但是，如果本身已有的物理数据库特征很糟糕，db2 optimizer的算法再优秀，runstats的统计信息再全面和及时，其sql语句的可执行的访问计划也不会好到哪里去。这就好像一座大楼的物理构造设计的不好，在优秀的装潢公司，也只能在这个物理设计的基础上进行修修补补，不可能把大楼推倒重来。

而实用工具reorg就是担当的这种任务。

（四）reorg和reorgchk

数据库中添加和删除的数据在物理上可能不是按连续的顺序放置的。在这种情况下，DB2 必须执行附加的读操作来访问数据。这通常意味着需要更多的磁盘 I/O 操作，我们都知道那些操作是开销很大的。在这种情况下，应该考虑在物理上将表重组，使相关的数据存放在相近的位置，以减少 I/O 操作。

REORG 是用于重组表和/或索引中的数据的一个实用程序。虽然数据是在物理上进行重排的，DB2 提供了在线或离线执行该过程的选项。默认情况下，离线 REORG 允许其他用户读这个表。可以通过指定 ALLOW NO ACCESS 选项来限制对表的访问。在线 REORG （也称就地 REORG）不支持对表的读或写访问。由于是在对数据页进行重排，并发的应用程序必须等到 REORG 完成当前页。您可以用适当的选项停止、暂停或恢复这个过程。

下面的例子很清楚地作了演示：

REORG TABLE panpan.test1 INDEX panpan.intest1 INPLACE ALLOW WRITE ACCESS REORG TABLE panpan.test1 INDEX panpan.intest1 INPLACE PAUSE

 

还可以重组索引。如果像下面一个例子中那样使用了 CLEANUP 子句，那么就会执行清除，而不是重组。

REORG INDEX db2user.idxemp FOR TABLE db2user.employee ALLOW WRITE ACCESS REORG INDEX db2user.idxemp FOR TABLE db2user.employee CLEANUP ONLY

 

REORGCHK 是另一种数据维护实用程序，它可以选择检索当前数据库统计信息或更新数据库统计信息。它还将使用 REORG 指示符生成关于统计信息的报告。通过使用统计信息公式，REORGCHK 将需要 REORG 的表或索引标上星号（*）。

我们来考虑一些例子。下面的命令生成关于运行时授权 ID 拥有的所有表的当前统计信息的一份报告：

REORGCHK CURRENT STATISTICS ON TABLE USER

 

下面的命令更新统计信息，并生成关于模式 smith 下创建的所有表的一份报告：

REORGCHK UPDATE STATISTICS ON SCHEMA smith

 

这里附上网上的一些帖子：

当数据库里某个表中的记录变化量很大时，需要在表上做REORG操作来优化数据库性能。

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

说明:

一个完整的REORG表的过程应该是由下面的步骤组成的：

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

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

1 、RUNSTATS

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

2、 REORGCHK

REORGCHK命令的语法如下：

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

              |  .-UPDATE--.               |

              '--+-CURRENT-+---STATISTICS--'

 

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

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

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

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

                 '-table-name-'

REORGCHK是根据统计公式计算表是否需要重整。

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

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

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

1) 针对系统表进行REORGCHK

db2 reorgchk update statistics on table system

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

 

2) 针对用户表进行REORGCHK

db2 reorgchk update statistics on table user

 

下面是执行的部分结果

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

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参数值请使用最为重要的索引名。因为数据在物理上进行cluster存储，只能够以一个索引为参考。

 

2) 如果表上没有索引

如表名为DB2INST1.STAFF, SYSIBM.SYSTABLES

db2 reorg table db2inst1.staff use tempspace1

db2 reorg table sysibm.systables use tempspace1

 

4 RUNSTATS

RUNSTATS命令的语法如下：

>>-RUNSTATS ON TABLE--table-name-------------------------------->

 

>-----+-+--------------------------------------------------------------------+-+>

     | '-WITH DISTRIBUTION--+--------------------------------------------+--' |

     |                      '-AND--+----------+--+-INDEXES ALL--------+--'    |

     |                             '-DETAILED-'  '-INDEX--index-name--'       |

     '-+--------------------------------------------------+-------------------'

       '--+-AND-+---+----------+--+-INDEXES ALL--------+--'

          '-FOR-'   '-DETAILED-'  '-INDEX--index-name--'

 

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

     |           .-CHANGE----.  |

     '-SHRLEVEL--+-REFERENCE-+--'

 

如果表名为DB2INST1.STAFF，表上有索引，则可以用下面的例子完成RUNSTATS命令：

db2 runstats on table db2inst1.staff with distribution and detailed indexes all

 

5 (可选) 上面命令完成后可以重复第二步，检查REORG的结果，如果需要，可以再次执行REORG和RUNSTATS命令。

 

6 BIND或REBIND

RUNSTATS命令运行后，应对数据库中的PACKAGE进行重新联编，简单地，可以使用db2rbind命令来完成。

db2rbind命令的语法如下：

>>-db2rbind--database--/l logfile----+------+------------------->

                                    '-all--'

 

                                      .-conservative--.

>-----+-------------------------+--/r--+-any-----------+-------><

     '-/u userid--/p password--'

 

 

例如，如果数据库名为SAMPLE，执行：

db2rbind sample -l db2rbind.out