如何在 DB2 存储过程中使用优化配置文件-数据仓库-火龙果软件工程

如何在 DB2 存储过程中使用优化配置文件

火龙果软件发布于 2014-07-16

次浏览

简介

大多数主要的关系型数据库管理系统，包括 DB2 for Linux, UNIX, and Windows，依赖于一种基于成本的优化器设计。该优化器基于一些条件来评估成本，包括 CPU 并行性和速度、I/O 存储特征和通信带宽等外部条件，以及 DB2 注册表变量、DB2 优化级别、统计信息等内部条件。而且，在系统运行时期间，这些条件中许多都在不断变化，所以选择最佳执行计划的过程对任何数据库系统而言都是一个非常复杂的过程。DB2 优化配置文件是对优化器的重要补充。对于在实现了其他调节实践之后不满足性能需求的 SQL 语句，可以使用此功能修改默认执行计划。它对应用程序调试和 SQL 语句性能优化最有用。

存储过程是数据库系统的另一个有用的功能。使用存储过程，数据库可实现存在于高级编程语言中的功能，比如变量定义、条件声明、控制语句等。DB2 中的存储过程是使用 DB2 SQL 过程语言 (SQL PL) 编写的。SQL PL 是 SQL 持久存储模块语言标准的一个子集。这项标准将通过 SQL 访问数据的方便性与编程语言的流控制功能相结合。

存储过程中的 SQL 语句常常具有更复杂的逻辑并且非常难以调试或调节，在一些情况下，DB2 选择的访问计划并不是您想要的，尤其是在应用程序调试情形下。在使用所有最佳实践后，如果您仍然无法从存储过程获得想要的性能，可以使用优化配置文件来提供帮助。存储过程中的 SQL 语句可能与其他地方的 SQL 语句稍微不同，因为它们常常包含一些输入（输出）变量。当您希望对这些类型的语句使用优化配置文件时，您需要采取一些额外的步骤才能得到想要的结果。本文将通过一个示例，介绍如何使用优化配置文件修改存储过程中的 SQL 语句的执行计划。

优化配置文件简介

优化配置文件是一个 XML 文档，其中包含针对一个或多个数据操作语言 (DML) 语句的优化指南。优化配置文件可包含全局指南，它们适用于在配置文件生效时执行的所有 DML 语句，优化配置文件还可包含适用于一个包中各个 DML 语句的特定指南。优化配置文件可用于修改 SQL 语句的访问计划，但这并不意味着您可以任意为一个语句指定访问计划。

您应该特别关注以下两个问题。

优化类优先于优化配置文件。也就是说，优化器只有在指南符合当前优化类的规则时，才使用这些指南。例如，您不能在优化级别 0 上使用合并连接 (merge join) 或哈希连接 (Hash-Join)。

只有在优化指南是优化器评估的一项计划时，才会挑选它。如果出于某种原因，优化器没有评估优化配置文件中指定的计划，它将不会使用该计划。

优化配置文件的基本格式如清单 1 所示。

清单 1. 优化配置文件的示例

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
  <!-- Global optimization guidelines section. Optional but at most one. -->
  <OPTGUIDELINES>
    Here is for the global guidelines
  </OPTGUIDELINES>

<STMTPROFILE ID="profile id">
<STMTKEY>
Here is for the statement that we want to apply this statement level
optimization guidelines to
</STMTKEY>
<OPTGUIDELINES>
Here is for the optimization guidelines for the statement defined in the
<STMTKEY> element
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

OPTPROFILE

优化配置文件从 OPTPROFILE 元素开始。此元素包含一个名为 VERSION 的属性，它指定本文件要遵守的 XML 模式版本。一个优化配置文件必须包含且仅能包含一个 OPTPROFILE 元素。

全局优化指南

一个优化配置文件可拥有最多 1 个全局优化指南节。在优化配置文件生效时，它将应用于所执行的所有语句。全局优化指南在 OPTGUIDELINES 元素中定义。例如，您可以指定使用哪个 MQT、优化级别、查询的并发程度，等等。

语句配置文件节

一个优化配置文件可包含 0 个或多个语句配置文件节。在优化文件生效时，它将仅应用到准确匹配的 SQL 语句。语句优化指南在 STMTPROFILE 元素中定义。它包含一个 STMTKEY 元素和一个 OPTGUIDELINES 元素。

STMTKEY 元素定义语句优化指南将应用到的 SQL 语句。DB2 使用 STMTKEY 元素中定义的内容来匹配 SQL 语句。如果匹配成功，与此 STMTKEY 相关的优化指南将应用到此 SQL 语句。在 STMTKEY 元素内定义的语句必须与要影响到其访问计划的语句准确匹配，这种匹配是区分大小写的。它允许多余的空格和控制字符，比如换行字符。但是，它不允许使用通配符来匹配语句组。每个需要受到影响的语句只应有一个独立的 STMTPROFILE 节。如果有多于一个与执行语句匹配的 STMTPROFILE，我们将仅选择并应用第一个。

在 OPTGUIDELINES 元素内，您可以指定 DB2 对某个表的访问方法（表扫描或索引扫描），修改要使用的连接操作和连接方法的顺序，指定查询重写规则，等等。当执行的 SQL 语句与 STMTKEY 元素中的内容准确匹配时，相关 OPTGUIDELINES 元素中的所有优化指南将应用于此 SQL 语句的访问计划的创建。

修改存储过程中的 SQL 语句访问计划

在一个存储过程内使用的 SQL 语句可能具有特殊的形式。例如，它们常常包含一些输入（输出）变量。这种 SQL 语句可直接供 STMTKEY 元素使用。当 DB2 编译这些语句时，它将这些变量替换为内部形式，然后使用此内部形式作为最终版本。如果使用最初的 SQL 语句作为 STMTKEY，当 DB2 使用内部形式进行匹配时，不会成功。所以，您需要首先找到这些 SQL 语句的内部形式，然后使用它们作为 STMTKEY 来创建优化配置文件。以下各节将介绍所需的步骤。

测试环境

我们为本文中的测试和示例使用以下环境。

操作系统：AIX 6.1

DB2 for Linux, UNIX, and Windows Version 9.7

数据库：SAMPLE 数据库

本文中的所有示例都基于在 AIX 6.1 操作系统上运行的 DB2 V9.7。对于其他操作系统，实现应该相同。

准备测试数据库

如图 1 所示，SAMPLE 数据库是 DB2 提供的一个小型数据库。如果在安装期间未安装它，您可以找到当前实例的 sqllib/bin 目录，运行 db2sampl 命令来自动创建它。

图 1. 创建测试数据库

创建说明表 (Explain table)

您需要使用 DB2 Explain 工具查看优化指南是否已选择，SQL 语句的访问计划是否已修改。Explain 工具的输出将显示优化配置文件的名称和使用的有效指南。因此，您需要创建 Explain 工具所需的表。在默认情况下，DB2 不会创建这些表。

如清单 2 所示，从当前实例所有者的 sqllib/misc 目录，执行文件 EXPLAIN.DDL 来完成所有 Explain 表的创建工作。

清单 2. 创建 Explain 表的脚本

db2 connect sample
db2 -tvf EXPLAIN.DDL
db2 connect reset

运行前面的命令后，重新连接数据库并运行 db2 list tables。您可以在系统目录中看到所有具有 EXPLAIN 前缀的新创建的表，如图 2 所示。所有这些表由 Explain 工具用于存储信息。

图 2. 新创建的 Explain 表

创建 SYSTOOLS.OPT_PROFILE 表

如清单 3 所示，您定义的所有优化配置文件将存储在模式 SYSTOOLS 下的 OPT_PROFILE 表中。默认情况下，DB2 不会创建此表。如果您希望使用优化配置文件来修改 SQL 语句的访问计划，您需要自行创建它。

清单 3. 创建 SYSTOOLS.OPT_PROFILE 表的 SQL

CREATE TABLE SYSTOOLS.OPT_PROFILE (
      SCHEMA VARCHAR(128) NOT NULL,
      NAME VARCHAR(128) NOT NULL,
      PROFILE BLOB (2M) NOT NULL,
      PRIMARY KEY ( SCHEMA, NAME ) );

此表中包含以下 3 列。

列 SCHEMA 指优化配置文件的模式名称。

列 NAME 指优化配置文件的名称。

列 PROFILE 存储优化配置文件的内容。

SCHEMA.NAME 可用于惟一标识数据库中的一个优化配置文件。如清单 4 中所示，将之前的脚本保存到文件 SYSTOOLS.OPT_PROFILE.DDL 中并重新连接数据库。

清单 4. 创建 SYSTOOLS.OPT_PROFILE 表的脚本

db2 connect to sample
db2 -tvf SYSTOOLS.OPT_PROFILE.DDL
db2 connect reset

运行脚本后，您将完成此表的创建工作，如图 3 所示。

图 3. 创建 SYSTOOLS.OPT_PROFILE 表

创建存储过程

关于存储过程的详细语法，您可以参阅 IBM DB2 9.7 信息中心中的 “存储过程” 一节，请参阅参考资料一节。如清单 5 所示，您将创建一个名为 GET_EMP_NUM 的简单存储过程来完成所有示例。此存储过程使用样例数据库中的 DEPARTMENT 表和 EMPLOYEE 表。它有两个参数，一个名为 DEPT_NO（部门 ID）的输入参数和一个名为 EMP_NUM（此部门中的员工数）的输出参数。此存储过程的功能是基于用户输入的部门 ID，计算一个部门中的员工数。

清单 5. 定义存储过程的脚本

CONNECT TO SAMPLE%

CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL')%

CREATE PROCEDURE GET_EMP_NUM(
IN DEPT_NO CHAR(50),
OUT EMP_NUM INTEGER)

LANGUAGE SQL

BEGIN

SELECT COUNT(*) INTO EMP_NUM
FROM DEPARTMENT, EMPLOYEE
WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT
AND DEPARTMENT.DEPTNAME = DEPT_NO;

END%

CONNECT RESET%

调用 SYSPROC.SET_ROUTINE_OPTS 存储过程来设置预编译和绑定选项，然后再定义 GET_EMP_NUM 存储过程。您也可以修改 DB2_SQLROUTINE_PREPOPTS 注册表变量来实现相同功能。如果调用 SYSPROC.SET_ROUTINE_OPTS 存储过程，它将覆盖此注册表变量的值。在这里将参数 EXPLAIN ALL 的值传递给 SYSPROC.SET_ROUTINE_OPT 存储过程。它表明在创建存储过程期间，其中的所有 SQL 语句的访问计划将保存在 Explain 表中。

将之前的脚本保存到文件 create_procedure.ddl 中，如清单 6 所示，然后运行图 4 中所示的命令来完成存储过程的创建工作。请注意，您应该首先在这两个表上执行 RUNSTATS，然后再创建存储过程，以便 DB2 将使用最新的统计信息来生成更有效的访问计划。

清单 6. 创建存储过程的脚本

db2 connect to sample
db2 'runstats on table db2inst1.department and indexes all'
db2 'runstats on table db2inst1.employee and indexes all'
db2 connect to reset
db2 -td% -vf create_procedure.ddl

图 4. 创建 GET_EMP_NUM 存储过程

查看在存储过程中定义 SQL 语句的内部形式

对于在存储过程中定义的这些 SQL 语句，DB2 将输入（输出）变量替换为它的内部形式。您可使用清单 7 中所示的 SQL 语句查看这些语句的内部形式。

清单 7. 查看在存储过程中定义的 SQL 语句的内部形式的 SQL

SELECT PKGNAME,  S.TEXT
  FROM SYSCAT.STATEMENTS AS S,
       SYSCAT.ROUTINEDEP AS D,
       SYSCAT.ROUTINES   AS R
 WHERE PKGSCHEMA = BSCHEMA
   AND PKGNAME = BNAME
   AND BTYPE = 'K'
   AND R.SPECIFICNAME = D.SPECIFICNAME
   AND R.ROUTINESCHEMA = D.ROUTINESCHEMA
   AND R.ROUTINENAME = 'GET_EMP_NUM'
   AND R.ROUTINESCHEMA = 'DB2INST1'
 ORDER BY STMTNO;

您应该将 R.ROUTINESCHEMA 的值替换为您使用的存储过程的模式名（在本例中为 DB2INST1），并将 R.ROUTINENAME 的值替换为存储过程的名称（在本例中为 GET_EMP_NUM）。在这之后，将脚本保存到文件 get_routine_sqls.sql 中，连接数据库来执行该脚本，如清单 8 所示。

清单 8. 查看存储过程中定义的 SQL 语句的内部形式的脚本

db2 connect to sample
db2 -tvf get_routine_sqls.sql
db2 connect reset

上述 SQL 语句的输出包含两列。一列是与存储过程相关的包名称，另一列是 SQL 语句的内部形式。运行该脚本之后，输出如图 5 所示。

图 5. 查询存储过程中定义的 SQL 语句的内部形式

您可以看到，输出参数 EMP_NUM 已替换为 :HV00009 :HI00009，输入参数 DEPT_NO 已替换为 :HV00008 :HI00008。它与之前在存储过程中定义的 SQL 语句有很大不同。因为这个新形式是 DB2 在编译时和执行时将执行的形式，所以这是您将在 STMTKEY 元素中使用的形式。此外，该结果的第一列是与此存储过程相关的包名称（在本例中，它为 P1513856）。您需要在稍后的查询中使用此信息检索此存储过程的访问计划。

查看默认访问计划

要确认该优化配置文件将修改 DB2 的默认访问计划，您需要首先获得 SQL 语句当前的访问计划。使用 db2exfmt 工具，–n 选项与包名称相关。关于此工具的每个选项的详细信息，您可以参阅 IBM DB2 9.7 信息中心中的相关部分，请参阅参考资料一节。如清单 9 和图 6 所示，您将原始访问计划保存在文件 orig_plan.out 中。

清单 9. 查看 SQL 语句的原始访问计划的脚本

db2exfmt -d sample -e db2inst1 -g -l -n 'P1513856' -s db2inst1 
-o orig_plan.out -w -1 -# 0 -v %

图 6. 原始访问计划

从以前的访问计划，您可以看到 DB2 默认选择了一个嵌套的循环连接来执行此语句。挑选了 DEPARTMENT 作为外部表（使用表扫描方法），挑选 EMPLOYEE 作为内部表（使用索引扫描方法，XEMP2 是表 EMPLOYEE 的一个索引）。在以下示例中，您将使用优化配置文件修改这个连接序列（让 EMPLOYEE 成为外部表，让 DEPARTMENT 成为内部表）。这样做的目的不是实现更高的性能，只是为了表明优化配置文件已应用于 SQL 语句。

创建优化配置文件

如清单 10 所示，使用存储过程中定义的 SQL 语句的内部形式创建优化配置文件。

清单 10. 优化配置文件的脚本

<?xml version='1.0' encoding='UTF-8'?>
<OPTPROFILE VERSION='9.1.0.0'>
  <STMTPROFILE ID='example profile'>
    <STMTKEY>
       <![CDATA[SELECT COUNT(*) INTO :HV00009 :HI00009
FROM DEPARTMENT, EMPLOYEE
WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT AND DEPARTMENT.DEPTNAME =
        :HV00008 :HI00008]]>
    </STMTKEY>
    <OPTGUIDELINES>
        <NLJOIN>
          <ACCESS TABLE='EMPLOYEE'/>
          <ACCESS TABLE='DEPARTMENT'/>
        </NLJOIN>
    </OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

在上一个脚本中，一定要特别注意以下几点。

STMTPROFILE 元素的 ID 属性：ID 属性的值是此优化配置文件中这个语句级优化指南的惟一标识符。如果将此优化指南应用于某条 SQL 语句，您可以从语句的访问计划中看到此标识符。

STMTKEY 元素：您必须使用 SQL 语句的内部形式作为 STMTKEY，否则它无法成功匹配。通常，您始终使用 <![CDATA[]]> 包装语句。

NLJOIN 元素：此元素指定连接的顺序。这里，您使用 EMPLOYEE 作为外部表，使用 DEPARTMENT 作为内部表。

将优化配置文件插入数据库中

定义优化配置文件之后，将它保存到文件 test_profile.prof 中。然后将此数据加载到表 SYSTOOLS.OPT_PROFILE 中，接着再使用导入命令完成此操作。
首先，使用名称 profile_file.load 定义导入的数据源文件。如清单 11 所示，此文件指定优化配置文件的模式名称为 Test，优化配置文件的名称为 OPTPROF，优化配置文件的详细内容包含在 test_profile.prof 文件中。

清单 11. 导入的数据源文件

"TEST","OPTPROF","test_profile.prof"

然后运行导入命令（如清单 12 和图 7 所示）将数据加载到数据库中。

清单 12. 导入命令的脚本

db2 "IMPORT FROM profile_file.load OF DEL MODIFIED BY LOBSINFILE 
INSERT_UPDATE INTO SYSTOOLS.OPT_PROFILE"

图 7. 导入命令

修改存储过程的定义文件以使用此优化配置文件

如果您希望在存储过程中使用优化配置文件，除了设置前面的注册表变量来启用此功能，您还需要调用 SYSPROC.SET_ROUTINE_OPTS 系统存储过程来设置模式名称，然后设置将在预编译和绑定时使用的优化配置文件的名称，如清单 13 所示。您需要在 SYSPROC.SET_ROUTINE_OPTS 参数中指定 OPTPROFILE 选项，它的值为 TEST.OPTPROF。所以您以前定义的优化配置文件将在为此存储过程中的 SQL 语句创建访问计划时使用。当然，您可以设置 DB2_SQLROUTINE_PREPOPTS 注册表变量获得相同的结果。

清单 13. 修改的存储过程

CONNECT TO SAMPLE%

CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL OPTPROFILE TEST.OPTPROF')%

CREATE PROCEDURE GET_EMP_NUM(
IN DEPT_NO CHAR(50),
OUT EMP_NUM INTEGER)

LANGUAGE SQL

BEGIN

SELECT COUNT(*) INTO EMP_NUM
FROM DEPARTMENT, EMPLOYEE
WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT
AND DEPARTMENT.DEPTNAME = DEPT_NO;

END%

CONNECT RESET%

如清单 14 所示，丢弃已创建的存储过程，然后重新创建它。

清单 14. 重新创建存储过程

db2 connect to sample
db2 drop procedure GET_EMP_NUM
db2 -td% -vf create_procedure.ddl

查看新访问计划以检查已使用了优化配置文件

要检查优化配置文件是否已成功使用，您需要对比原始访问计划与新访问计划。

首先，因为存储过程已重新创建，DB2 将为它分配一个新包。运行清单 15 和图 8 中所示的命令，获得此存储过程的相关的包名称。

清单 15. 获得存储过程的包名称

db2 connect to sample
db2 -tvf get_routine_sqls.sql

图 8. 重新创建的存储过程的新包名称

然后使用前面的包名称查看存储过程的新访问计划，将结果保存在文件 curr_plan.out 中，如清单 16 所示。

清单 16. 获得新访问计划

db2exfmt -d sample -e db2inst1 -g -l -n 'P2270199' -s db2inst1 
-o curr_plan.out -w -1 -# 0 -v %

从新访问计划，您可以看到有一个附加的 Profile Information 部分，如图 9 所示。它包含模式名称，用于生成此 SQL 语句的访问计划的优化配置文件（在本例中为 TEST.OPTPROF），以及与该语句匹配的语句级优化指南的 ID。这个 ID 在定义优化配置文件时指定。

图 9. stored procedure_1 的新访问计划

与此同时，从新访问计划中，您可以看到连接的顺序已改变，如图 10 所示。现在，外部表为 EMPLOYEE 表，内部表为 DEPARTMENT 表。所以您可以证明，优化配置文件已用来成功生成了存储过程中的 SQL 语句的访问计划。

图 10. stored procedure_2 的新访问计划

结束语

存储过程是 DB2 的一项非常有用的功能。一个存储过程中定义的 SQL 语句通常非常复杂，并且难以调试和调节。在一些情形下，DB2 选择的访问计划可能不是您想要使用的，尤其是当调试性能问题时。通过使用优化配置文件，您可以影响 SQL 语句的访问计划，而无需更改应用程序和数据库设置。它是一个调试应用程序和优化 SQL 的非常有效的工具。

次浏览