|
简介： 优化准则（Optimization guideline）是非常强大的工具，使您能够影响关键的执行计划决策，包括表访问方法、索引选择、连接方法和连接顺序。在 IBM® DB2® Version 9.7 for Linux®, UNIX®, and Windows® 发行版中，优化准则基础设施得到了增强，能够支持新的特定于 XML 的准则并将现有关系准则的范围扩展到包含 XML 操作符。本文将向 DB2 for Linux, UNIX, and Windows pureXML® 用户介绍优化准则基础设施，并带领逐步设置和使用您的 SQL/XML 和 XQuery 工作负载的优化准则。
简介 DB2 优化器是一款高度复杂的基于成本的优化器。然而，优化决策是各种属性的复杂函数，这些属性包括数据库环境、查询特征以及数据本身。在这些不同的独立因素的 相互作用下，优化器可能会选择不太优化的执行计划。由于不可能总是修改数据库设置或数据的内在特征，因此需要使用一种工具来支持用户影响计划的选择，从而 适合他们独特的设置特征。优化准则是一种强大的机制，使用户能够影响关键的执行计划属性，包括表访问方法、索引选择、连接方法和连接顺序，从而解决可能出 现的任何性能问题。最新的 DB2 V9.7 for Linux, UNIX, and Windows (DB2 9.7) 发行版对优化准则基础设施进行了增强，引入了特定于 XML 的准则并扩展了现有准则的范围以包含 XML 操作符。
本文旨在向 DB2 pureXML 用户介绍优化准则基础设施，并介绍针对 SQL/XML 和 XQuery 工作负载设置、使用和维护优化准则的所有步骤。同时提供了多种故障排除技巧和资源，使用户获得成功实现准则所需的所有工具和信息。
本文使用的数据库设置
Transaction Processing over XML (TPoX) 是一个基于金融应用程序场景（特别是有价证券交易）的应用程序级别的 XML 数据库基准。它使用一个真实的 XML 模式建模与有价证券交易有关的数据。它非常灵活，提供了各种 knob 和调优参数，可以修改数据分布、工作负载构成等等。本文使用一个经过轻微修改的 TPoX 基准来展示 XML 准则基础设施中可以用来影响优化器计划的各种选项。为了简单起见，本文的样例查询忽略了 TPoX XML 数据中的名称空间。
本文使用修改后的 TPoX 表，这些表使用清单 1 所示的 DDL 创建：
清单 1. 用于创建 TPoX 表的 DDL
create table security (security_id int, security_type varchar(10), SDOC XML);
create table custacc (CDOC XML);
create table order (security_id int, ODOC XML); |
这些表上创建了以下关系索引： | create index sec_type on security?security_type); |
这些表上创建了以下 XML 索引：
清单 2. 表上创建的 XML 索引
create index sec_industry on security(sdoc) generate key using
xmlpattern '/Security/SecurityInformation/StockInformation/Industry' as SQL varchar(30);
create index sec_symbol on security(sdoc) generate key using
xmlpattern '/Security/Symbol' as SQL varchar(30);
create index sec_name on security(sdoc) generate key using
xmlpattern '/Security/Name' as SQL varchar(100);
create index acc_currency on security(sdoc) generate key using
xmlpattern '/Customer/Accounts/Account/Currency' as SQL varchar(3);概要文件基础设施概述 优化概要文件是指包含优化准则的 XML 文档。优化准则分为两个类别： 全局准则：指定优化概要文件生效后需要对所有语句考虑的访问计划参数。 - 语句级别准则：指定只对特定语句应用的计划属性。
每个语句级别准则包含： 一个语句键：识别其访问计划必须受到影响的语句。 - 一个操作：指定理想的访问计划。
“XML 准则的使用场景” 一节包含了有关优化概要文件剖析的详细信息。
优化概要文件允许您在无需更改应用程序或数据库配置的情况下影响访问计划。应用程序和概要文件的分离实现了两者的独立开发。用户只需编写 XML 概要文件文档，将其插入到数据库，然后指示优化器使用这些概要文件。
一旦概要文件被启用，优化器将自动将优化准则匹配到对应的语句。概要文件中指定的准则 不需要包含计划中的所有操作符的详尽说明。可以定义那些只影响执行计划的某些属性的准则，比如查询中的某个表的索引说明，或者一对表的连接顺序。优化器根 据指定的准则实现概要文件中指定的部分计划，并通过在多个计划之间做出基于成本的决策来智能地完成计划的剩余部分。
设置和使用优化概要文件详解
必须通过以下步骤来使用优化概要文件： 以下小节将详细介绍每一个步骤。
创建 XML 概要文件
优化概要文件被编写为 XML 文件，该文件必须遵循 DB2 LUW Information Center 针对每个版本发布的 “Current Optimization Profile Schema (COPS)” （参见 参 考资料）。优化概要文件包含其访问计划需要受到影响的所有 SQL 查询。
清单 3. 样例优化概要文件
$C0 ')))
AS Q1), TPOX.SECURITY AS Q2, TPOX.ORDER AS Q3
WHERE (Q3.SECURITY_ID = Q2.SECURITY_ID) |
SECURITY 表的内部编号为 Q2；对于 ORDER，其编号为 Q3。初始查询中的 XMLEXISTS 谓词被转换为一个复杂表达式，可以通过编号 Q1 引用。准则将首先对 SECURITY 表应用 xmlexists 谓词，随后是与 ORDER 表的 hash 连接，如清单 6 所示：
清单 6. 根据名称或标识符引用表
SELECT *
FROM order, security
WHERE order.security_id = security.security_id
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
|
清单 6 表明准则中可能同时包含了表名和表标识符。表名通过 XML 属性 TABLE 指定，而表标识符通过 XML 属性 TABID 指定。 创建 OPT_PROFILE 表 优化概要文件必须被存储在 SYSTOOLS.OPT_PROFILE 表中。 可以使用两种方法创建该表： DB2 提供了一个由系统定义的存储过程，称为 SYSINSTALLOBJECTS，它可以创建或删除 OPT_PROFILE 表： | db2 "call sysinstallobjects('opt_profiles', 'c', '', '')" |
SYSINSTALLOBJECTS 过程将创建或删除用于各种工具的数据库对象。第一个参数表明用户对创建优化概要文件表感兴趣。第二个参数表明目标用户操作是创建 优化概要文件表。第三个和第四个参数指定模式名和对象名，这两个参数必须留空，这样就将使用默认的模式 SYSTOOLS 和表名 OPT_PROFILE。和所有系统过程一样，SYSINSTALLOBJECTS 过程位于 SYSPROC 模式中。 此外，可以显式地发出以下 DDL 语句来创建 OPT_PROFILE 表：
清单 7. 创建 OPT_PROFILE 表的 DDL 语句
create table systools.opt_profile (
schema varchar(12 not null,
name varchar(12 not null,
profile blob (2m) not null,
primary key (schema, name)
) |
schema 列用于指定优化概要文件的模式名。模式名必须是由数字和字母组成的字符串，并且必须遵守相应 DB2 LUW 发行版中的模式名的所有命名规则。 name 列包含优化概要文件的名称，还可能包含最多由 128 个字符组成的字母数字式字符串。 profile 列包含 XML 文档，其中包含优化概要文件。
加载概要文件
在启用优化概要文件之前，必须将其关联到一个惟一的模式限定名，并存储在 SYSTOOLS.OPT_PROFILE 表中。可以使用 INSERT 语句、IMPORT 实用工具或 LOAD 实用工具。清单 8 解释了如何将一个优化概要文件插入到 OPT_PROFILE 表中。此概要文件为 SECURITY 表包含了一个 XISCAN 准则。
清单 8. 插入一个优化概要文件
insert into systools.opt_profile values
('TPOX','PROFILE1',
CAST('
' as blob));
|
此外，用户还可以使用 IMPORT 实用工具导入已被保存为 XML 文件的优化概要文件。 下面的示例展示了如何将两个概要文件从 weekly_report_profile.xml 和 scientist_queries_profile.xml 文件导入到 SYSTOOLS.OPT_PROFILE 表。假设这两个概要文件位于当前目录中。一个带分隔符的输入文件，比如 “profiledata”（参见 清 单 9），包含概要文件模式、概要文件名和 XML 文件名。注意每个概要文件必须出现在单独的行中。
清单 9. 输入文件中的概要文件数据
"TPOX"," ROFILE1","weekly_report_profile.xml"
"TPOX","ROFILE2","scientist_queries_profile.xml" |
现在，可以使用 IMPORT 命令将概要文件导入到 OPT_PROFILE 表。
清单 10. 将概要文件导入到 OPT_PROFILE 表
import from profiledata of del
modified by lobsinfile
insert into systools.opt_profile |
启用概要文件 可以将任意数量的概要文件加载到 OPT_PROFILE 表。然而，在任何指定时间，最多只有一个概要文件是有效的。如果需要影响多个查询，可在一个优化概要文件中包含多个语句概要文件。然后在包级别使用 OPTPROFILE 绑定选项，或者在会话级别使用 CURRENT OPTIMIZATION PROFILE 专用寄存器，从而启用优化概要文件。CLI 应用程序可以使用客户机配置选项 CURRENTOPTIMIZATIONPROFILE 来对每个连接使用专用寄存器。 例如，用户可以发出以下命令： | db2 "set current optimization profile='TPOX.PROFILE1'" |
这使 PROFILE1 在模式 TPOX 中充当有效概要文件，直到当前会话结束。在任何时候，都可以使用 set 命令指定专用寄存器的值。 可以使用以下命令告诉优化器不要使用任何优化配置文件。优化器将通过基于成本的决策返回最佳计划。 | db2 "set current optimization profile=''" |
各种选项的优先顺序如下所示： 不管使用何种设置，OPTPROFILE 绑定选项适用于所有静态语句。 对于静态语句，CURRENT OPTIMIZATION PROFILE 专用寄存器的值根据以下顺序确定： 应用程序中的最后一个 SET CURRENT OPTIMIZATION PROFILE 语句 [最高优先级] - CURRENTOPTIMIZATIONPROFILE 客户机配置选项，如果已设置的话
- OPTPROFILE 绑定选项，如果已设置的话
发出查询 启用所需的概要文件后，用户就可以不加修改地运行他们的工作负载。对于匹配当前概要文件的查询，优化器将尽最大的努力来生成准则所要求的计 划。对于不与任何准则匹配的查询，将选择常规的基于成本的访问计划。 检验准则是否得到执行 DB2 Explain 工具对于判断某个概要文件是否成功匹配查询非常有用。在解释输出的 “Profile Information” 部分，指定了合适的优化概要文件名和语句概要文件名。
清单 11. 概要文件信息
OPT_PROF: (Optimization Profile Name)
TPOX.PROFILE1
STMTPROF: (Statement Profile Name)
Listing 3
|
db2exfmt 输出的 “Profile Information” 部分可以用来检验是否已将正确的优化概要文件和语句概要文件应用到当前的查询。 如果优化器无法应用准则，那么它将返回警告 437，其原因代码为 13： SQL0437W Performance of this complex query may be sub-optimal. Reason code: "13". SQLSTATE=01602 随后可以解释查询并检查 db2exfmt 输出来获得更多诊断信息，以便修复有关准则的任何问题。例如，如果没有找到引用的表名并因而未能应用准则的话，那么 db2exfmt 将生成以下输出：
清单 12. 更多诊断信息
Diagnostic Identifier: 1
Diagnostic Details: EXP0009W Invalid access request. The table
reference identified by the TABLE attribute could
not be found. Line number "1", character number
"60". |
如果没有出现任何警告信息或诊断消息，那么表明优化器已经成功应用了准则。可以查看 db2exfmt 输出，了解所使用的访问/连接方法。 XML 准则的使用场景 影响访问方法 / 强制实施 XML 索引访问 XISCAN (XML Index Scan) 准则 该准则指定一个单一的 XML 索引扫描，将用于访问给定的表。可以在一个优化准则中包含以下的 XISCAN 元素，表明应当使用索引 “SEC_INDUSTRY” 访问表 “SECURITY”： TABLE 或 TABID 属性指定了表名和表 id（即表的标识符，显示在被访问的 db2exfmt 输出的优化 SQL 中。这个属性是必需的）。 INDEX 属性是可选的，可用于指定一个索引名。如果指定了索引名的话，它将对特定的 XML 索引使用 XISCAN 操作符来请求一个访问计划。如果未指定索引名，优化器将选用成本最低的 XML 索引访问计划。 - FIRST 属性也是可选的。如果指定了的话，它将只接受 “TRUE” 值，表示给定表必须是连接查询中进行访问的第一个表。对于 FROM 子句中列出的所有表，最多有一个访问或计划可以带有 “FIRST” 属性。
清 单 13 中的示例展示了以完整优化概要文件为上下文的 XISCAN 准则。清 单 14 中的示例使用了不包含索引名的 XISCAN 准则，表示应当使用成本最低的可用 XML 索引访问表 “SECURITY”。
清单 13. 使用成本最低的 XML 索引的 XISCAN 准则
SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInformation
/StockInformation[Industry= "OfficeSupplies"]')
|
清单 14. 使用特定 XML 索引的 XISCAN 准则
SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInformation
/StockInformation[Industry= "OfficeSupplies"]')
|
XANDOR (XML Index Anding and Oring) 准则
该准则指定 XANDOR 操作符同时使用所有合适的 XML 索引访问给定表。如 XISCAN 准则所述，TABLE 或 TABID 属性以及 FIRST 属性具有相同的含义。 清 单 15 中的样例准则指定表的 “SECURITY” 应当通过所有合适的 XML 索引的 XANDOR 访问。如果元素 “Industry” 和 “Symbol” 分别具有一个 XML 索引，DB2 将使用 XISCAN 操作符为每个索引生成一个双向的 XANDOR。不会使用关系索引，因为 XANDOR 方法中不能使用关系索引。如果只有一个 XML 索引是可用的，那么 DB2 将无法应用这个准则，因而将生成成本最低的替代计划。
清单 15. 使用所有合适的 XML 索引的 XANDOR 准则
SELECT *
FROM security
WHERE security_type = 'Bond Fund'
AND XMLEXISTS('$SDOC/Security/SecurityInformation
/StockInformation[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
|
ACCESS 准则
ACCESS 准则（任何访问请求）允许优化器选择方法。当连接顺序（而不是访问方法）成为主要的考虑事项时，通常会使用这种准则。当目标表引用是一个派生表时，那么必 须使用 ACCESS 元素。 ACCESS 元素可以具有以下属性：TABLE、TABID、FIRST、TYPE、INDEX 和 ALLINDEXES。ACCESS 元素可以具有一个或多个名为 INDEX 的子元素。 对于前面讨论的 XISCAN 准则，TABLE 或 TABID 属性以及 FIRST 属性具有相同的含义。 TYPE 属性是可选的，并且只能使用 “XMLINDEX” 作为其值。它表示必须通过其中一种 XML 索引方法访问表，比如 XISCAN、XANDOR 或 IXAND（至少具有一个 XML index leg），或至少具有一个 XML index leg 的 RIDSCN（索引 ORing）。如果未指定该属性，优化器将生成一个基于成本的决策，为给定的表选择一种访问计划。 INDEX 属性是可选的，并且仅当 TYPE 被指定为 “XMLINDEX” 时才会指定一个索引名。如果指定了该属性，那么优化器可能将从以下计划中进行选择： 一个使用指定的 XML 索引的 XISCAN 计划。 一个具有所有合适的 XML 索引的 XANDOR 计划。 一个 IXAND 计划，指定的 XML 索引是 IXAND 的主索引。优化器可能会以一种基于成本的方式将更多索引加入到 IXAND 计划中。 - 一个基于成本的 XML 索引 ORing 计划。索引 ORing 计划的 db2exfmt 输出显示了一个 RIDSCN 操作符，其中以两个或更多个 XISCAN 操作符作为输入。
如果 ACCESS 元素的 TYPE 属性指定 “XMLINDEX” 的话，可选的 INDEX 元素可以提供两个或更多个 XML 索引名。如果指定了 INDEX 元素，优化器将从以下计划中选择： 一个具有所有合适的 XML 索引的 XANDOR 计划。 一个 IXAND 计划，将按照特定的顺序使用指定的 XML 索引，并且没有包含任何额外的索引。 - 一个基于成本的索引 ORing (RIDSCN) 计划。
在指定了索引属性和一个或多个索引元素后，索引属性将被忽略。 ALLINDEXES 属性是可选的，并且只能使用 “TRUE” 作为其值。只有当 TYPE 被指定为 “XMLINDEX” 时才有可能指定该属性。将该属性设置为 “TRUE” 表示必须使用所有合适的关系和 XML 索引来访问表，而不会考虑成本问题。优化器随后将从以下计划中进行选择： 一个 XANDOR 计划，其中所有合适的 XML 索引出现在 XANDOR 操作符下。 一个 IXAND 计划，其中所有合适的关系和 XML 索引出现在 IXAND 操作符下。 一个索引 ORing 计划。 - 一个 XISCAN 计划，前提是只对表定义了一个单一的索引并且其类型为 XML。
| 
免费注册
发表于 2010-12-24 09:14
mlcolumn("CUSTACC.CADOC"
/Customer