db2 的优化

 reorg ,runstats ,reorgchk

 

1.runstats 收集统计信息，

2.reorgchk  
    针对系统表进行reorgchk
    reorgchk update statistics on table system
      
    针对用户表进行  reorgchk
    db2 reorgchk update statistics on table user

3. reorg 通过重构行来消除“碎片”数据并压缩信息，对表进行重组。 还有一个功能就
     是可以将表中的数据按照某个索引关键字的顺序排列，从而可以减少某些查询i/o数
     量。
     当数据库里某个表中的记录变化量很大时，需要在表上做REORG操作来优化数据库性
     能，值得注意的是，针对数据库对象的大量操作，如反复地删除表，存储过程，会引起
     系统表中数据的频繁改变，在这种情况下，也要考虑对系统表进行REORG操作。

  4.执行REORG可以考虑分为表上有索引和没有索引两种情况：
     a.如表名为DB2INST1.STAFF,索引名为DB2INST1.ISTAFF
      reorg table db2inst1.staff index db2inst1.istaff use tempspace1
    b.建议REORG时使用USE参数指定数据重排时使用的临时表空间，否则，REORG工作将会
       在表所在表空间中原地执行.如果表上有多个索引，INDEX参数值请使用最为重要的索
       引名.
    c.表上没有索引：
       reorg table db2inst1.staff use tempspace1
      reorg table sysibm.systables use tempspace1

runstate

 

现在，几乎所有重要数据库都使用某种方法来更新目录统计信息，以便为其优化器提供可能的最佳信息。可以将优化器视为一个勘探器，正在系统中的数据所代表的大山中进行定位。目录统计信息的更新将为优化器提供最新的地图，以便在整个地形中快速定位。
   DB2 优化器使用目录统计信息来确定最佳的访问路径，而更新这些目录统计信息所采用的主要方法就是运行 RUNSTATS 实用程序。当用户表中发生数据修改时，目录统计信息表不会自动被修改。必须在表和索引上执行 RUNSTATS 命令，用最新的信息更新目录表中的列。


   可以查询下列目录列，确定是否在表和索引上执行了 RUNSTATS：

   如果对于某个表，SYSCAT.TABLES 视图的 CARD 列显示的值为 -1，或者 STATS_TIME 列显示的值为 NULL，那么这表示没有对该表运行 RUNSTATS 实用程序。
如果对于某个索引，SYSCAT.INDEXES 视图的 NLEAF、NLEVELS 和 FULLKEYCARD 列显示的值为 -1，或者 STATS_TIME 列显示的值为 NULL，那么这表示没有对该索引运行 RUNSTATS 实用程序。

   在第一次将数据载入到表中之后，就无法避免地要对表进行更新、删除和插入等形式的修改。可以添加或删除索引。特定列中的数据分布可能随时间发生改变。目录中不会反映这些修改，除非在这些表和索引上执行 RUNSTATS。一段时间之后，随着表和数据发生更改，目录统计信息可能会过时。

   执行 RUNSTATS 是很重要的，但是频繁地运行可能会带来问题。对于较小的表，发出 RUNSTATS 命令将是一项普通任务。然而，随着表的增长，完成 RUNSTATS 命令将占用更多时间、CPU 和内存资源。最终，您必须考虑分配更多时间和资源来运行 RUNSTATS 与不执行该命令的潜在性能下降之间的平衡。通常只在关键查询的速度开始减慢时，管理员才会对 RUNSTATS 给予适当的注意。您可以通过制定高效、有效收集统计信息的策略，避免未经思考就调优查询和执行 RUNSTATS。

理论上，应该在下列情况下对表和索引执行 RUNSTATS：
1.将数据载入表中并创建了合适的索引之后。不过，最好是在创建索引之后再执行 LOAD
   命令，并在 LOAD 期间收集统计信息。
2.创建了一个新的索引之后。
3.使用 REORG 实用程序重新组织表之后。
4.通过数据更新、删除和插入大量更新表及其索引之后。
5.更改了预取（prefetch）大小之后。
6.运行 REDISTRIBUTE DATABASE PARTITION GROUP 实用程序之后。

 

   您可以通过比较查询 RUNSTATS 之前和之后的 EXPLAIN 输出，来确定运行 RUNSTATS 对于访问计划的影响。
   完成每一条 RUNSTATS 语句之后，您都应该执行显式的 COMMIT WORK。COMMIT 将释放锁，并避免在收集多个表的统计信息时填写日志。
   在用 RUNSTATS 收集了统计信息之后，要使用 BIND 命令或 REBIND 命令重新绑定包含了静态 SQL 的包（并可以选择重新解释其语句）。 db2rbind 命令可用于重新绑定数据库中的所有包。使用 FLUSH PACKAGE 命令来删除包缓存器中当前所有缓存的动态 SQL 语句，并强制隐式地编译下一请求。

  
   注意：在 RUNSTATS 语法中，必须使用全限定的表名 schema.table-name 和全限定的索引名 schema.index-name。您可以在所有列上，或者仅仅在某些列或列组（除了 LONG 和 LOB 列）上执行 RUNSTATS。如果没有指定特定列的子句，系统则会使用默认的 ON ALL COLUMNS 子句。


   下列例子说明如何使用 RUNSTATS 收集统计信息：
   1：收集所有列上的统计信息     
     RUNSTATS ON TABLE db2admin.department ON ALL COLUMNS
    这等同于：RUNSTATS ON TABLE db2admin.department

   2.收集单个列上的目录统计信息
      RUNSTATS ON TABLE db2admin.department ON COLUMNS  
     (deptno,deptname)

   3.收集关键列上的目录统计信息
      RUNSTATS ON TABLE db2admin.department ON KEY COLUMNS
     展示如何收集关键列（key column）上的统计信息。短语“关键列（key
     column）”表示构成表上所定义索引的列。如果没有索引存在，这条命令不会收集任
      何列的统计信息。
   4.收集关键列上和一个非关键列上的目录统计信息
       RUNSTATS ON TABLE db2admin.department ON KEY COLUMNS AND
      COLUMNS (deptname )
   5.收集表和索引上的目录统计信息，不包含分布统计信息
       RUNSTATS ON TABLE db2admin.department AND INDEXES ALL
  
    6.收集表上的目录统计信息以及索引上的详细统计信息，不包含分布统计信息
      RUNSTATS ON TABLE db2admin.department AND DETAILED INDEXES ALL

   7.只收集 3 个指定索引上的目录统计信息（不含表统计信息）
      RUNSTATS ON TABLE db2admin.department FOR INDEXES     
     db2admin.INX1,  db2admin.INX2, db2admin.INX3

   8.只收集所有索引上的目录统计信息
      RUNSTATS ON TABLE db2admin.department FOR INDEXES ALL
  


使用 RUNSTATS WITH DISTRIBUTION
 
    当您已确定表中包含不是统一分布的数据时，可以运行包含 WITH DISTRIBUTION 子句的 RUNSTATS。目录统计信息表通常包含关于表中最高和最低值的信息，而优化器假定数据值是在两个端点值之间均匀分布的。然而，如果数据值彼此之间差异较大，或者群集在某些点上，或者是碰到许多重复的数据值，那么优化器就无法选择一个最佳的访问路径，除非收集了分布统计信息。使用 WITH DISTRIBUTION 子句还可以帮助查询处理没有参数标志符（parameter marker）或主机变量的谓词，因为优化器仍然不知道运行时的值是有许多行，还是只有少数行。

    下列例子说明了使用 RUNSTATS 来收集包含分布的统计信息的不同方法

    9.收集表和索引上的目录统计信息，包含分布统计信息
       RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND
      INDEXES ALL
    10.收集表上的目录统计信息以及索引上的详细统计信息，包含分布统计信息
        RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND
       DETAILED INDEXES ALL
    11.收集选定列中包含分布的目录统计信息
        RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION ON
       COLUMNS (deptno, deptname).

    12.只收集表上的目录统计信息，包含 deptno 和 deptname 上的基本列统计信息以
        及 mgrno 和 admrdept 上的分布统计信息
        RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno,
       deptname)   WITH DISTRIBUTION ON COLUMNS (mgrno, admrdept)
    13.收集构成索引的所有列以及两个非索引列中包含分布的目录统计信息
         RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION
        ON KEY COLUMNS AND COLUMNS (admrdept, location)

包含频率和分位数统计信息的 RUNSTATS
 
  在执行包含 WITH DISTRIBUTION 子句的 RUNSTATS 时，会根据 RUNSTATS 命令中给定的选项选择一组频率（frequency）和分位数（quantile）的统计信息。

   RUNSTATS 收集两种类型的数据分布统计信息：频率统计信息和分位数统计信息。

   频率统计信息的默认值由 num_freqvalues 数据库配置参数控制，该值提供了重复最多的列和数据值的信息。其默认值是 10，建议将这个值设置在 10 到 100 之间。如果将 num_freqvalues 设置为零，则不保留任何频率值的统计信息。

   分位数统计信息的默认值由 num_quantiles 数据库配置参数控制，该值提供了数据值对于其他值而言是如何分布的有关信息。 num_quantiles 数据库配置参数指定应将列数据值分成的组数。其默认值是 20，建议将该值设置在 20 到 50 之间。如果将这个参数设置为零或“1”，则不收集任何分位数统计信息。

    如果没有在 RUNSTATS 命令的列或表级别上指定 num_freqvalues 和 num_quantiles，那么 num_freqvalues 的值将从 num_freqvalues 数据库配置参数中获取，而 num_quantiles 的值将从 num_quantiles 数据库配置参数中获取。

     可以为单个列或一组列修改频率和分位数统计信息的精确度。提高分布统计信息的精确度将导致更大的 CPU 和内存消耗，并占用更多的目录空间。对于这些分布统计信息，只考虑对拥有选择谓词的最重要的查询而言最为重要的列。


  出现下列任何一种条件时，RUNSTATS 将不收集分布统计信息：

  1.当将 num_freqvalues 配置参数设置为零（0），以及将 num_quantiles 数据库配
    置参1.数设置为零（0）或 1 时。
  2.当每个数据值是惟一的时候。
  3.当该列是 LONG、LOB 或结构化列时。
  4.如果列中只有一个非空值。
  5.扩展的索引或声明的临时表。

    下列例子说明了使用 RUNSTATS 来收集目录统计信息和指定 num_freqvalues 和
     num_quantiles 的不同方法：


  14.收集包含分布统计信息的目录统计信息
      RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND
     INDEXES ALL
     将 num_freqvalues parameter 设置为 10， num_quantiles parameter 设
      置为 20，因为在命令行上没有指定 num_freqvalues 和 num_quantiles 参数。
 
  15.仅收集表上的目录统计信息，其中使用指定的 num_freqvalues 以及从数据库配置
      设置选择 num_quantiles 收集所有列上的分布统计信息
      RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION DEFAULT
     NUM_FREQVALUES 40


包含列组统计信息的 RUNSTATS
 
  列组（Column Group）统计信息将获得一组列的不同值组合的数目。通常，DB2 优化器可用的基本统计信息不检测数据相关性。列组的使用将给多个谓词的联合选择提供更准确的估计。列组统计信息假设数据是均匀分布的；但还无法获得列组上的分布统计信息。

  较于列组的基数，单个列的基数（cardinality）的乘积将获得更好的相关性估计。

  下列例子说明了如何使用 RUNSTATS 收集捕获了列组信息的目录统计信息：

   RUNSTATS ON TABLE db2admin.department ON COLUMNS
  ((deptno, deptname), deptname, mrgno, (admrdept, location)) 。

  本例中，总共有两个列组：(deptno, deptname) 和 (admrdept, location)。


包含 LIKE STATISTICS 的 RUNSTATS

   当在 RUNSTATS 中指定 LIKE STATISTICS 子句时，将收集附加的列统计信息。这些统计信息存储在 SYSIBM.SYSCOLUMNS 表里的 SUB_COUNT 和 SUB_DELIM_LENGTH 列中。它们仅针对字符串列进行收集，查询优化器用它们来提高“column LIKE '%abc'”和“column LIKE '%abc%'”类型谓词的选择性估计。

   下列例子说明了如何使用 RUNSTATS 收集捕获了 LIKE 统计信息的目录统计信息：
  
   示例 20：收集所有列上的目录统计信息并指定 VARCHAR 列上的 LIKE 统计信息   RUNSTATS ON TABLE db2admin.department ON ALL COLUMNS and
          COLUMNS (deptname LIKE STATISTICS)



包含统计信息配置文件的 RUNSTATS

   现在，可以在 DB2 V8.2 中为 RUNSTATS 建立一个统计信息的配置文件。统计信息配置文件是指一组选项，它预先定义了特定表上将要收集的统计信息。

   当将命令参数“SET PROFILE”添加到 RUNSTATS 命令时，将在表描述符和系统目录中注册或存储统计信息配置文件。若要更新该统计信息配置文件，则可以使用命令参数“UPDATE PROFILE”。

   没有删除配置文件的选项。

   下列例子展示了如何使用这项功能：

   21.只注册一个统计信息配置文件，不收集目录统计信息
       RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET
      PROFILE ONLY
      RUNSTATS 中的子句“SET PROFILE ONLY”不收集统计信息
   22.注册一个统计信息配置文件，并执行所存储统计信息配置文件的 RUNSTATS 命令选
       项来收集目录统计信息
       RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET
      PROFILE
   23.仅修改现有的统计信息配置文件，不收集任何目录统计信息
       RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND
      INDEXES ALL UPDATE PROFILE ONLY
   24。修改现有的统计信息配置文件，并执行已更新的统计信息配置文件的 RUNSTATS 命
       令选项来收集目录统计信息 
      RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND
     INDEXES ALL UPDATE PROFILE
   25.根据前面已注册的统计信息配置文件来查询 RUNSTATS 选项
       SELECT STATISTICS_PROFILE FROM SYSIBM.SYSTABLES WHERE
       NAME = 'DEPARTMENT' AND CREATOR = 'DB2ADMIN'


带有抽样的 RUNSTATS

   随着数据库快速不断地增长，通过访问所有数据收集统计信息的能力可能会受到固定的批量窗口、内存和 CPU 约束的阻碍。

   目前，要运行表上的 RUNSTATS，就要执行全表扫描。通过数据抽样，只需扫描数据的一个子集即可。

   如果一个查询试图预计总的趋势和模式，且某一误差域（margin of error）内的近似答案足以监测这些趋势和模式，那么数据抽样或许是比全表扫描更好的选择。

   在 DB2 V8.1 中，引入了 SAMPLED DETAILED 子句，允许通过抽样计算详细的索引统计信息。该子句的使用将减少为获得详细索引统计信息而执行的后台计算量和所需的时间，但在大多数情况下，都会使数据足够的精确。

   以下是一些关于该子句的使用的例子：
27. 收集两个索引上的详细目录统计信息，但对每个索引条目使用抽样来代替执行详细的计
     算
     RUNSTATS ON TABLE db2admin.department AND SAMPLED DETAILED
    INDEXES ALL
28.收集索引上的详细抽样统计信息和表的分布统计信息
     RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION ON KEY
    COLUMNS AND SAMPLED DETAILED INDEXES ALL

带有行级贝努里（Bernoulli）抽样的 RUNSTATS
     行级贝努里（Bernoulli）抽样利用 sargable（search + argument-able 谓词是一个可以由数据管理器来评估的谓词）谓词获得百分之 P 的表行样本，在样本中包含每一行的概率是 P/100，而不包含它的概率则是 1 - P/100。

      例如，对于 10% 贝努里（Bernoulli）抽样，将会选择 10%（10/100）的行，而拒绝 90%（1-10/100）的行。在贝努里（Bernoulli）抽样中，每一页将会引发一次 I/O，因为要扫描该表。将生成一个随机数来确定是否选择一行（类似于以 P/100 的概率扔钱币）。即使每一页都发生一次 I/O，我们仍然节省了处理数据所需的 CPU 时间。

      在行级贝努里（Bernoulli）抽样中，需要读取每一个数据页。然而，它仍然可以带来极大的性能提高，因为 RUNSTATS 是 CPU 密集的。如果索引是可用的，那么就会改进抽样。如果数据是群集的，它还可以提供更准确的统计信息（所获得的样本更好地代表了整个表数据）。

      
收集目录统计信息的可供选择的方法
 
     对于数据库中的所有表，收集统计信息的一个可供选择的方法就是发出一条 REORGCHK 命令，如下所示
     32.使用 REORGCHK 收集所有表的目录统计信息
         REORGCHK UPDATE STATISTICS ON TABLE ALL
        reorgchk 命令的 update statistics 选项的作用类似于调用 RUNSTATS 例
         程来更新数据库中所有表的目录统计信息，但是所有列上的统计信息只能通过默认
         的 RUNSTATS 选项来收集。通过使用该命令，还可以收集单个表上或同一模式下
         一组表的统计信息。
    33.使用 REORGCHK 收集一个表的目录统计信息
         REORGCHK UPDATE STATISTICS ON TABLE db2admin.department
    34. 使用 REORGCHK 收集一个模式的目录统计信息
         REORGCHK UPDATE STATISTICS ON SCHEMA systools


包含授权和用户访问的 RUNSTATS

    为了可以对一个表或索引收集统计信息，必须能够连接到包含该表和索引的数据库，并具有下列授权级别之一：

      1.sysadm
     2.sysctrl
     3.sysmaint
     4.dbadm
     5.表上的 CONTROL 权限
      当对一个表运行 RUNSTATS 时，有两种用户访问选项：允许读访问和允许写访问。

      在 RUNSTATS 命令中的参数 ALLOW READ ACCESS 指定计算统计信息时，其他用户
      可以只读地访问该表。

      在 RUNSTATS 命令中的参数 ALLOW WRITE ACCESS 指定计算统计信息时，其他用
      户可以读取或写入该表。如果该表在任何时刻都必须是可用的，那么应该使用该子
      句。



分区数据库中的 RUNSTATS
     当在一个分区数据库中发出 RUNSTATS 命令，并且一个表分区位于发出 RUNSTATS 的数据库分区中时，那么 RUNSTATS 将在该数据库分区上执行。如果表分区不在该数据库分区上，那么将请求发送给数据库分区组中持有该表分区的第一个数据库分区。然后，在该数据库分区上执行 RUNSTATS 命令。

     RUNSTATS 不是在分区的数据库中并行运行的，但对一个分区确定信息，然后对所有分区推断出合适的估计值。有一个隐式的假设：每个表中的行是均匀分布在每个多分区数据库分区组中的所有分区上的。

     下列 IBM 技术札记（technote）谈到了加载一个表时运行 RUNSTATS 可能碰到的问题。其解决方案就是确保在加载表之前，为该表定义一个分区键（partitioning key）。

  
    加载表之后，Runstats 将多分区实例上 syscat.tables 中的列 CARD 更新为零行。

    如果加载某一给定表之后，其中一个分区的行数为零，并且将从该分区执行 runstats，那么该命令将更新 syscat. 表中的列 CARD，显示该表的行数为零。这只对多分区的实例有影响。

    runstats 的行为使其将使用运行它的分区上的数据来推测该表中的行数。例如，如果加载之后所有数据都在一个分区上，并从另一分区执行 runstats，那么该命令将更新 syscat.tables 来表示该表的行数为零。然而，如果是从保存所有数据的分区运行 runstats，那么它将更新 syscat.tables 来展示该表包含（（该分区上的行数）x（分区数目））行。

    为了避免 runstats 的行为默认行为，需要在加载表之前，为该表定义分区键（partitioning key）。分区键需要位于高度惟一的一列或一组列上，从而确保数据均匀分布在该表所定义的所有分区上。”



   
调整 STAT_HEAP_SZ 数据库配置参数

    stat_heap_sz 或统计信息堆大小的数据库配置参数指定了使用 RUNSTATS 命令收集统计信息中所用堆的最大尺寸。它是在启动 RUNSTATS 实用程序时分配的，然后当它完成时释放。stat_heap_sz 是代理私有内存的一部分。因此，在收集分布统计信息时，最好增大 stat_heap_sz 参数，以便能将更多的列放入这个堆中。处理较宽的表也需要更多的内存。当执行包含 SAMPLED DETAILED 选项的 RUNSTATS 时，必须额外分配 2 MB 内存，以确保 RUNSTATS 能够成功运行。


减小 RUNSTATS 对系统性能影响的策略

  1.一次仅在少数表和索引上运行 RUNSTATS，在整组表中循环运行。
  2.仅指定将收集其数据分布统计信息的那些列。仅指定那些谓词中所使用的列。
  3.对于不同的表，在不同的分区上实现多个并发的 RUNSTATS。
  4.仅在那些影响当前工作负载的关键表上执行 RUNSTATS。避免在不需要它的表上运行
     RUNSTATS。
  5.根据表中数据发生改变的速度，调整 RUNSTATS 的频率。
  6.根据 RUNSTATS 在该表上完成运行的速度，调整 RUNSTATS 的频率和细节。
  7.仅在系统活动量少的时候，安排执行 RUNSTATS。
  8.调整（Throttle）RUNSTATS，以便最大程度地减少它对系统的需求。


   仅在系统活动量少的时候安排执行 RUNSTATS，这是最大程度地减少系统影响的一个好方法。然而，对于一个 24 x 7 的系统，系统中可能没有可用的窗口或活动量少的时候。处理该情形的一种方法就是使用 RUNSTATS 的 throttling 选项。

   throttling 选项将根据当前的数据库活动级别，来限制实用程序所占有的资源数量。UDB 中，在调整时，util_impact_lim 与 UTIL_IMPACT_PRIORITY 参数的交互确定了 RUNSTATS 的行为。UTIL_IMPACT_PRIORITY 关键字被用于诸如 RUNSTATS 的实用程序命令的子句中，而 util_impact_lim 则是一个实例配置参数。

    util_impact_lim 参数是指允许所有已调整实用程序对于实例的工作负载产生影响的百分比。如果 util_impact_lim 是 100（默认值），则不用调整诸如 RUNSTATS 之类的实用程序调用。例如，如果将 util_impact_lim 设置为 10，那么已调整的 RUNSTATS 调用就被限定在消耗 10% 以下的工作负载。

    UTIL_IMPACT_PRIORITY 关键字可充当一个开关，它指定 RUNSTATS 是否订阅调整策略。

  throttle ---控制油、气流的阀门
 
  UTIL_IMPACT_PRIORITY 关键字的使用
 
  RUNSTATS ON TABLE db2admin.department AND INDEXES ALL 
  UTIL_IMPACT_PRIORITY 10

 
   其中，10 是调整 RUNSTATS 的优先权或级别。
  
   当 util_impact_lim 不是 100，而且用 UTIL_IMPACT_PRIORITY 调用 RUNSTATS 时，将会对该值进行调整。如果没有为 UTIL_IMPACT_PRIORITY 指定优先权，且 util_impact_lim 不是 100，那么 RUNSTATS 将使用默认的优先权 50，并将据此进行调整。如果在 RUNSTATS 命令中省略 UTIL_IMPACT_PRIORITY 关键字，那么可以不加调整地运行 RUNSTATS。如果指定了优先权，但是将 util_impact_limit 设置为 100，也可以不加调整地运行 RUNSTATS。如果将优先权设置为零，也能不加调整地运行 RUNSTATS。

   当定义了 RUNSTATS 调整（throttling），并且该调整可操作时，RUNSTATS 实用程序预计将花费更长时间，但是对系统产生的影响会少一些。



DB2 自动统计信息收集

   DB2 自动统计信息收集是在 DB2 UDB Version 8.2 中引入的。自动统计信息收集是完全自动表维护解决方案的一部分。其目标是允许 DB2 确定工作负载需要哪些统计信息，并定期在后台自动运行 RUNSTATS 实用程序，以便按需更新统计信息。

   为了设置 SAMPLE 数据库自动进行统计信息收集，需要为自动维护开关设置数据库配置参数，如下所示：

db2 update db cfg for SAMPLE using AUTO_MAINT ON
db2 update db cfg for SAMPLE using AUTO_TBL_MAINT ON
db2 update db cfg for SAMPLE using AUTO_RUNSTATS ON