获得关于设计索引以及数据分区键、物化查询表、IBM DB2 Universal Database (DB2 UDB) 多维集群的专家帮助。Design Advisor 是 DB2 UDB 内置的，它能提供您所需的帮助。本文解释了如何使用 Design Advisor 为数据仓库作出最优的设计。

　　简介

　　本文是关于IBM DB2 Universal Database (DB2 UDB) V8.2 Design Advisor 特性的一个由两部分组成的系列中的第 2 部分。本文关注的是如何在数据仓库(DW)数据库方面有效地使用 Design Advisor。要获得关于 Design Advisor 的总体介绍和提示，请参阅 第 1 部分。在数据仓库领域面临的特殊设计挑战

　　Design Advisor 非常适合解决在数据仓库方面为最优数据访问而设计物理数据库时所面临的特殊挑战。在开发这个工具时，特别考虑了 DW 的一些方面，包括：

　　数据仓库经常使用以下面向 DW 的 DB2 特性：物化查询表(MQT)、多维集群表(MDC)和数据分区功能(DPF)hash 分区。所以，与简单地优化索引相比，这里的设计决策更加复杂。对这些特性的使用必须加以协调，才能将性能发挥到极至。

　　DW 常常很庞大，有时高达 terabyte 级。在这样的环境下，存储对于设计决策的意义就变得更加重大。实际上，当索引没有带来足够的益处时，能够发现这些可以删除的索引就是一个重要的设计目标。

　　DW 的庞大规模也使得更改已有的物理 DB 设计比较困难，因为这意味着要移动大量的数据(使用导出和导入)。因此，需要倚重一个能帮助评价设计方案的工具。

　　工作负载比较的即席(ad hoc)。这使得 DBA 在实际使用系统之前，难于对工作负载建模。因此，更需要 DBA 一边监控系统的使用情况，一边调整物理数据库的设计。

　　DW 常常有能作为 Design Advisor 的补充的 IBM 产品，即 DB2 Query Patroller 和 DB2 Cube Views。Query Patroller 适合为 Design Advisor 提供输入，而 Cube Views 中包括一个专门用于设计在 OLAP 模式中使用的 MQT 的工具。

　　DW 常常有一些表上的视图，用以为终端用户提供一个简化的、反规格化的数据视图。DW 的设计者可能对发现能通过将这些逻辑视图转换成 MQT 来获得性能好处的情况感兴趣。案例研究简介和讲解

　　下面的案例研究将演示 Design Advisor：您已经有一个数据仓库，现在正在升级到 DB2 UDB V8.2，而您想使用最新的 DB2 特性，尤其是 MDC 和 MQT。您的数据库已经在使用 DPF 功能和 Query Patroller 产品。您觉得 DW 中有一个表适合转换成 MDC。您愿意通过删除和重建表、索引等操作来更改物理 DB2 设计，以便实施 Design Advisor 的 建议。但是，为此将不得不停一次机。

　　对本案例研究的讲解分为以下步骤：

　　收集和描述提供给 Design Advisor 的工作负载：

　　从 Query Patroller 收集工作负载

　　使用 Design Advisor

　　决定何时以及如何运行 Design Advisor

　　后续步骤接下来的几个小节将一一讲解这些步骤，并附上 GUI。之后还有一个小节讲解该 Design Advisor 的命令行版本。

　　步骤 1：收集和描述提供给 Design Advisor 的工作负载从 Query Patroller 收集工作负载

　　在使用 Design Advisor 之前，需要确定如何获得代表性的工作负载样本，以作为它的输入。由于数据仓库工作负载的即席性质，这个任务有挑战性。幸运的是，在这个案例研究中，Query Patroller 简化了这一任务。您可以使用 Query Patroller 从过去的查询的历史记录中进行选择。您可以选择最适合自己需要的一个子集：

　　最大的 10% 的查询。

　　只来自访问特定一组表(例如，一个事实表和它的维表)的一个特定应用程序的查询。

　　包括来自每个终端用户工具(例如 Business Objects 或 SAS)的查询的样本。

　　包括访问每个表的查询的样本。

　　随机的样本。工作负载的选择应该与调优活动的目标相匹配。对于建议的解释，应该结合您提供给 Design Advisor 的工作负载。虽然可以包括整个一组查询，其数量可能介于 1 万到 100 万之间，但不推荐这样做。通常，10 到 100 个查询最能兼顾取得的结果与消耗的 DB2 资源之间的平衡。

　　在这个案例中，您可以使用在某个星期一早晨(此时有很多 Business Objects 活动)和某个晚上(此时有些较大的 SAS 任务正在运行)运行的查询作为样本。查询 Query Patroller 表以获得那些查询，并将它们导出到一个文件中(请参考 Query Patroller 文档获得详细的说明)。使用这个文件作为 Design Advisor 的输入。

　　备选方案：Design Advisor 可以直接导入 Query Patroller 整个一组的查询。但是，如果有很多查询(例如超过 10,000)，那么这不是最好的方法。而上面描述的其中某种方法也许更值得推荐。而且，该工具的命令行版本(db2advis)有一个附加选项，通过该选项可以直接从 QP 获得包含开始时间戳与结束时间戳之间的查询的工作负载。收集和描述提供给 Design Advisor 的工作负载

　　有了工作负载文件，便可以启动 Design Advisor。如图 1 所示，Design Advisor 允许选择让它考虑哪些特性，例如索引、MQT、MDC 和 分区。这个例子包括所有选项。图 1. 选择性能特性

　　

 

　　提示：选择所有选项。这样可以确保 Design Advisor 充分利用各特性之间的协同作用，发现全局最优的解决方案。

　　备选方案：如果选择所有选项，可能难于理解为什么 Design Advisor 建议它所做的事情。有些用户可能更喜欢以牺牲最优解决方案为代价，采取逐步推进的方案。下面是逐步推进方案的一些例子：

　　一次只关注一个应用程序 —— 它的工作负载和它所访问的一部分模式。

　　一次重点优化对一个表的访问。也就是说，首先找出针对表 X 的查询，然后只实施与该表相关的建议。对于接下来的其他表，重复该过程。

　　单独关注某个特定的特性。例如，重点在于通过使用 MDC 和重新分区来重新设计 BigFactTable。留下 MQT 用于将来的迭代，可能是在初始的更改在生产中生效之后。

　　提示：考虑推迟刷新的 MQT。虽然这个例子使用立即(Immediate)刷新，但推迟刷新的 MQT 通常更适合 ETL 活动。

　　接下来，提供工作负载给 Design Advisor。在这个案例中，您有一个从 Query Patroller 获得的带工作负载数据的文件 。步骤 2：决定何时以及如何运行 Design Advisor

　　除了本系列 第 1 部分所讨论的几点之外，这一步无需特殊的考虑。步骤 3：检查建议

　　从图 2 可以看到，Design Advisor 生成了一组建议，这些建议将导致性能提升 60%。您也可以看一部分建议，即创建 3 个 MQT。图 2. 选择建议

　　

 

　　图 3 中显示了更多的建议。关于 CUSTOMER 表，建议是创建两个附加的索引，并保留已有的那个索引。关于 LINEITEM 表的一个建议是更改 hash 分区键。图 3. 设计建议

　　

 

　　提示：应该记得，工作负载没有包含任何 ETL 工作。在考虑这些建议时，要留意这些建议对 ETL 工作的性能的影响。

　　在 V8.2 中，Design Advisor 第一次提供详细的报告，让您可以更深入地了解建议。图 4 展示了哪些查询从这些建议中获益最多。在改进不同的 SQL 语句方面有很多的变化。图 4. Design Advisor 报告

　　

 

　　最后，从图 5 可以看到，Design Advisor 找出了对于这个工作负载来说，不需要达到最佳性能的一些对象。可以选择删除这些对象。但是，在删除之前，需要考虑提供给 Design Advisor 的工作负载是否忽略了可能从这些对象中获益的语句。图 5. 未使用的对象

　　

 

　　步骤 4 – 后续步骤

　　除了本系列 第 1 部分所讨论的几点之外，这一步无需特殊的考虑。这里重申一下，在接受了建议之后，似乎接下来的步骤自然就是作出更改。然而，此时可能还要做些其他事情：

　　保存建议，以便给其他人看。

　　以不同的输入重新运行 Design Advisor，看看能否获得更好的建议。

　　在实施建议之前，DBA 通常要做的事情是：

　　用符合惯例的名称替代生成的索引名称。

　　查看对象所要存储到的表空间。

　　添加关于对象的注释，表明创建它的原因。

　　DB2 V8.2 有一些补充性的增强，使得这些建议实施起来更加容易。这就是在线索引创建和 alter TABLE 支持。命令行 (db2advis) 讲解

　　现在我们将讲解同一个案例研究，不过这次使用的是命令行，而不是 GUI。

　　下面是这个案例研究中使用到的命令：

　　db2advis –d TPCD –i tpch_queries.in –m IMCP –k LOW -l 700 –c DB2ADVIS_TBSP -f

　　要点包括：

　　-m IMCP：规定 Design Advisor 应该考虑新的索引(I)、新的 MQT(M)、将标准的表转换成 MDC 表(C)，并且重新为已有的表分区(P)。默认情况是只考虑索引。

　　-k LOW：规定将工作负载压缩到 Low 级别。结果，Design Advisor 将分析您提供的更大一组的工作负载。默认情况是中等(medium)。

　　-l 700：规定任何新的索引、MQT 等都不能消耗多于 700 MB 的空间。默认情况是数据库总体规模的 20%。

　　-c DB2ADVIS_TBSP：规定使用表空间 DB2ADVIS_TBSP 作为生成 MQT 建议的临时工作空间。如果您想要 MQT 建议，并且正在运行一个 DPF(多分区)示例，那么这个选项是必需的。否则，这个参数是可选的。

　　另一个有用的选项(没有给出)是 -o output_file。该选项保存脚本，以便在一个文件中创建建议的对象。请参阅 参考资料小节，以获取关于命令选项的更全面信息的链接。

　　当该命令执行时，它描述正在进行的工作，下面显示了其中的一部分。至此，Design Advisor 已经生成了有关除 MDC 之外的所有对象的建议。

　　Cost of workload with all recommendations included [1306186] timerons 27 indexes in current solution 3 partitionings in current solution 8 MQTs in current solution

　　建议集由 27 个索引(新索引或已有的索引)、3 个分区(即与 DPF 相关的更改，例如新的分区键或表空间)，以及 8 个 MQT(新的或已有的)。

　　接下来，Design Advisor 分析 MDC，并在完成时显示以下信息。

　　3 clustering dimensions in current solution [12305400] timerons (without any recommendations) [1042873] timerons (with current solution) [91.53%] improvement

　　“3 clustering dimensions”意味着 Design Advisor 建议 3 个 MDC 维。这 3 个维可以同时在一个表上，也可以在不同的表上，例如，3 个维都在表 A 上，或者其中 1 个维在表 A 上，另外两个维在表 B 上。性能统计信息指的是所有建议的性能，而不仅仅是 MDC 建议的性能。“timerons (without any recommendations)”这一项指的是现有设计能取得的性能，而“with current solution”指的是实施这些建议后估计能取得的性能。

　　接着，Design Advisor 以 DDL 格式显示建议，并且该 DDL 已经被注释掉。这些建议以如下顺序出现：

　　包括 MDC 或分区建议的基本表。

　　MQT 建议(首先是新的 MQT，然后是要保留的已有的 MQT，最后是未使用的 MQT)。

　　新的集群索引(如果有的话)。

　　索引建议(新的，保留的，然后是未使用的)。

　　关于更改一个表的建议如下所示：

　　– create TABLE “TPCD”.”LINEITEM” (“L_ORDERKEY BIGINT NOT NULL, — “L_PART” INTEGER NOT NULL, — “L_SUPPKEY” INTEGER NOT NULL, — “L_LINENUMBER” INTEGER NOT NULL, — “L_SHIPINSTRUCT” char(25) NOT NULL, (11 other columns omitted from this example) — MDC409022109290000 GENERATED ALWAYS AS ( ((INT(L_SHIPDATE))/7) ) — —- PARTITIONING KEY (“L_PARTKEY”) USING HASHING — —- IN “TPCDLADT” — ORGANIZE BY ( — MDC409022109290000, — L_SHIPINSTRUCT ) — PARTITIONING KEY (L_ORDERKEY) USING HASHING — IN TPCDLDAT –; — COMMIT WORK ;

　　注意，这里建议了一个新的分区键(L_ORDERKEY)，用以替代当前的分区键(L_PARTKEY)，后者被注释掉了。对于这个表的 MDC 建议(ORGANIZE BY 子句)包括两个维：一个生成的列(INT(L_SHIPDATE/7))和一个已有的列(L_SHIPINSTRUCT)。

　　输出中接下来的是关于 MQT 的建议，如下所示。

　　– LIST OF RECOMMENDED MQTs — ======================== — MQT MQT40902204140000 can be created as a refresh immediate MQT — mqt[1], 0.009MB create SUMMARY TABLE “ADVDEMO2″.” MQT40902204140000″ AS (select Q6.CO AS “CO”, Q6.C1 AS “C1”, …additional details omitted here…) DATA INITIALLY DEFERRED REFRESH IMMEDIATE PARTITIONING KEY (C8) USING HASHING IN TPCDLDAT ; COMMIT WORK; REFRESH TABLE “ADVDEMO2″.” MQT40902204140000″; COMMIT WORK; RUNSTATS ON TABLE “ADVDEMO2″.” MQT40902204140000″; COMMIT WORK; — MQT MQT409022041530000 can be created as a refresh immediate MQT (… DDL to create this table follows…)

　MQT 建议包括： 估计的大小、使用的表空间、分区键(如果适用的话)、刷新类型(立即或延迟)以及这个表是否是一个基本表的复制品(由 REPLICATE 关键字表明)，但在本案例中不是的。

　　最后，Design Advisor 以下面显示的信息结束。

　　8604 solutions were evaluated by the advisor DB2 Workload Performance Advisor tool is finished.结束语

　　Design Advisor 非常适合为数据仓库优化数据库设计，因为除了关于索引的建议之外，它还可以提供关于 MQT、MDC 和 DPF 分区键的建议。由于数据仓库中的查询工作负载具有即席的特点，所以该工具分析这种工作负载以及提供建议的方式是非常可贵的。