DB2中15个pureXML性能的最佳实践

DB2 9中的pureXML支持提供了用于管理XML数据的高效且通用的功能。 对于许多XML应用程序而言，性能是重中之重。 DBA以及应用程序设计师可以尽其所能来确保良好的性能。 首先，对于平衡的CPU /内存/磁盘配置，表空间和缓冲池调整，锁定，日志记录，查询执行计划等，有所有传统的DB2性能准则。 所有这些主题在DB2以前的文章中都有介绍（请参阅参考资料 ），并且在DB2中管理XML数据时仍然是相关的。

幸运的是，DB2的自动功能（例如自动存储和自调整内存管理）解决了许多此类问题。 它们为许多应用程序提供了高水平的性能，并且几乎不需要人工干预。 但是，具有苛刻性能要求的XML应用程序可能会从其他性能考虑中受益。 本文重点介绍这种情况，提供了一些技巧和指南，以在DB2 9中实现XML相关应用程序的最佳性能。

这里有15个XML性能提示（不分先后顺序），我们将在本文中进行讨论和说明。 这15条技巧涵盖了多个领域，但是经验表明，存在性能问题的应用程序通常仅需要应用这些技巧中的一项或两项即可获得所需的性能。

• 提示1：明智地选择XML文档的粒度
• 技巧2：使用DMS和较大​​的页面可以更好地提高XML性能
• 技巧3：利用XML的存储选项：内联，压缩或单独的表空间
• 技巧4：如何配置DB2以快速批量插入XML数据
• 提示5：使用新的快照监视器元素来检查XML性能
• 技巧6：注意XML模式验证的开销
• 技巧7：在XPath表达式中，尽可能使用完全指定的路径
• 技巧8：定义精益XML索引，避免索引所有内容
• 技巧9：将文档过滤谓词放在XMLEXISTS中，而不是XMLQUERY中
• 技巧10：使用方括号[]避免XMLEXISTS中的布尔谓词
• 提示11：使用RUNSTATS收集XML数据和索引的统计信息
• 技巧12：如何使用SQL / XML发布视图将关系数据公开为XML
• 技巧13：如何使用XMLTABLE视图以关系格式公开XML数据
• 提示14：对于简短查询或OLTP应用程序，请使用带有参数标记SQL / XML语句
• 技巧15：在XML插入和检索过程中避免代码页转换

在讨论这些性能技巧时，我们假定您熟悉基本的DB2管理和性能实践以及DB2 pureXML支持的基础知识。 例如，您应该了解XML列，XML索引以及如何使用SQL / XML和XQuery查询XML数据。 先前在developerWorks上发表的文章中介绍了所有这些先决条件（请参阅参考资料 ）。

DB2 XML性能技巧

提示1：明智地选择XML文档的粒度

特别是在设计XML应用程序和XML文档结构时，您可以选择定义将哪些业务数据保存在单个XML文档中。 例如，在下面的部门表中，每个部门使用一个XML文档（中等粒度）。 如果部门是应用程序访问和处理数据的主要粒度，那么这是一个合理的选择。 或者，我们可以决定将多个或多个部门合并为一个XML文档，例如，所有属于一个单元的对象（粗粒度）。 但是，如果我们通常一次只处理一个部门，这将不是最佳选择。

表1.创建表dept（unitID char（8），deptdoc xml）

unitID	部门文件
WWPR	<dept deptID='PR27'> <employee id='901'> <name>Jim Qu</name> <phone>408 555 1212</phone> </employee> <employee id='902'> <name>Peter Pan</name> <office>216</office> </employee> </dept>
WWPR	<dept deptID='V15'> <employee id='673'> <name>Matt Foreman</name> <phone>416 891 7301</phone> <office>216</office> </employee> <description>This dept supports sales world wide</description> </dept>
使用	...
...	...

我们还可以决定为每位员工准备一个XML文档（细粒度），并为每位员工提供一个附加的“ dept”属性，以指示他或她所属的部门。 如果员工本身使用感兴趣的业务对象，而这些业务对象通常是与同一部门的其他员工独立访问和处理的，那么这将是一个很好的选择。 但是，如果应用程序通常一起处理一个部门中的所有员工，则每个部门一个XML文档会更好。

特别是，不建议在单个文档中批处理许多独立的业务对象。 DB2使用XML数据上的索引在每个文档级别进行过滤。 因此，XML文档的粒度越细，基于索引的访问的潜在利益就越高。 另外，如果您的应用程序使用DOM解析器来提取从DB2检索到的XML，那么小的文档将提供更好的性能。

与XML文档设计相关，一个常见的问题是何时使用属性与元素以及该选择如何影响性能。 与性能问题相比，这更是一个数据建模问题。 因此，这个问题与XML的前身SGML一样古老，并且一直在争论不休，没有得到普遍接受的共识。 但是，要坚持的一个重要事实是XML元素比属性更灵活，因为它们可以重复和嵌套。 例如，在部门文档中，我们使用元素“电话”，如果员工有多个号码，则允许我们多次出现“电话”。 如果我们以后需要将电话号码分成多个片段，也可以扩展它，即“ phone”元素可以包含国家代码，区号，分机号等子元素。如果“ phone”是employee元素的属性，那么每个员工只能存在一次，并且我们也不能添加子元素，这可能会阻止架构随时间的发展。 尽管您可以对所有数据建模而不使用属性，但是对于事先已知永远不会重复（每个元素）也没有任何子字段的数据项，它们可以是非常直观的选择。 属性会导致XML稍短一些，因为它们只有一个标签，而不是带有开始标签和结束标签的元素。 在DB2中，可以像在元素中一样容易地在查询，谓词和索引定义中使用属性。 由于属性的扩展性不及元素，所以DB2可以应用某些存储和访问优化。 应该将其视为额外的性能奖励，而不是将属性转换为元素的动力，尤其是在数据建模考虑因素实际上需要元素的情况下。

简而言之，根据预期的主要访问粒度选择您的XML文档粒度。 如有疑问，通常最好选择更细的粒度和更小的XML文档。

技巧2：使用DMS和较大​​的页面可以更好地提高XML性能

数据库托管表空间（DMS）提供的性能高于操作系统托管表空间（SMS）。 对于关系数据，这是正确的，对于XML读写访问，则更是如此。 在DB2 9中，缺省情况下，新创建的表空间是DMS。 还建议将DMS表空间与自动存储一起使用，以便DMS容器根据需要增长而无需手动干预。 如果XML文档太大而不能放在表空间的单个页面上，那么DB2会将文档拆分为多个区域，然后将其存储在多个页面上。 这对您的应用程序是透明的，并允许DB2处理XML文档，每个文档的绑定限制为2GB。

通常，每个文档的区域（拆分）数量越少，性能越好，尤其是对于插入和全文档检索而言。 如果文档不适合页面，则每个文档的拆分数取决于页面大小（4KB，8KB，16KB或32KB）。 表空间的页面大小越大，每个文档可能拆分的数量越少。 例如，假设给定的文档分为40个4KB页面。 然后，同一文档只能分别存储在20个8KB页面，10个16KB或5个32KB页面上。 如果XML文档大大小于所选页面大小，则不会浪费空间，因为可以在单个页面上存储多个小文档。

根据经验，为XML数据选择的页面大小应不小于平均预期文档大小的两倍，最大为32KB。 如果将单个页面大小用于关系和XML数据，或者用于数据和索引，则32KB页面大小对于XML数据可能是有益的，但对于关系数据和索引访问则有些不利。 在这种情况下，16KB或8KB页面可能是更好的选择，并且两者都适用。

技巧3：利用XML的存储选项：内联，压缩或单独的表空间

让我们考虑以下示例表，以讨论XML数据的存储选项。 该表包含关系数据和XML数据：

清单1.带有XML和关系数据的样本表

create table product(pid bigint, name varchar(20), brand varchar(35), 
                     category integer, price decimal, description XML);

使用此表定义，表的XML数据和关系数据默认情况下存储在同一表空间中。 这意味着它们使用相同的页面大小，并被缓冲在相同的缓冲池中。 在表空间内，关系数据存储在DAT对象中，而XML数据驻留在XDA对象中。 这是因为XML文档（如LOB）可能太大而无法容纳在表数据页的一行中。 此默认布局为大多数应用方案提供了良好的性能。

如果进行了性能分析，并且发现需要用于XML数据的大页面大小，但是需要用于关系数据或索引的小页面大小，则可以使用单独的表空间来实现此目的。 定义表时，可以将“长”数据定向到具有不同页面大小的单独表空间中。 长数据包括LOB和XML数据。
以下示例定义了两个缓冲池和两个表空间，每个都有4KB和32KB页。 （请注意，表空间始终需要页面大小匹配的缓冲池。）表"product"被分配给具有4KB页面的表空间"relData" 。 它的所有列都存储在该表空间中，XML列"description"除外，该列存储在表空间"xmldata" 32KB页面上。

清单2.单独的表空间和缓冲池中的XML和关系数据

create bufferpool bp4k pagesize 4k;
create bufferpool bp32k pagesize 32k;

create tablespace relData
pagesize 4K
managed by automatic storage
bufferpool bp4k;

create tablespace xmlData
pagesize 32K
managed by automatic storage
bufferpool bp32k;

create table product(pid bigint, name varchar(20), brand varchar(35), 
                     category integer, price decimal, description XML) 
     in relData
     long in xmlData;

DB2 9中的表空间缺省值不同于DB2 V8中的表空间缺省值。 除非明确指定，否则将将新表空间创建为具有大行ID的DMS。 这意味着在V8中，具有4KB页面的表空间可以增加到2TB，而不是64GB；而具有32KB页面的表空间可以增加到16TB，而不是512GB。 此外，每页255行的限制也被取消，在32KB页上最多允许2335行。 因此，每页行数限制本身不再是将小页面用于关系数据的原因。

DB2 9.5还允许您“内联”和压缩存储XML数据。 如果您的某些或所有XML文档足够小以适合DAT对象在基表页面上的相应行，则可以将它们内联到关系行中。 这提供了对XML数据的更直接访问，并避免了对XDA对象的重定向访问。 如果XML列中的某些文档仍然太大而无法内联，则照常将它们“轮廓化”存储在XDA对象中。 内联可以显着减小区域索引的大小，因为内联文档不需要任何区域索引条目。 它们始终由单个内联区域组成。 也可以使用常规的DB2行压缩来压缩内联的文档，如清单3所示：

清单3.内联和压缩的XML存储

create table product(pid bigint, name varchar(20), brand varchar(35), 
                     category integer, price decimal, 
                     description XML inline length 3000) compress yes;

在此示例中，XML列定义为选项"inline length 3000" 。 这意味着任何可以以3000字节或更少的字节存储的文档都将内联。 与内联相关的是在DB2中进行XML解析之后的文档大小，而不是文件系统中文本XML文档的大小。 内联长度必须小于页面大小减去表中其他列的大小。 内联XML数据始终与表的关系列位于同一表空间中，并且不能存储在不同的页面大小或单独的表空间中。

由于示例表是使用选项"compress yes"定义的，因此将"compress yes"关系数据和内联的XML文档。 将内联XML数据压缩70％到85％并不少见。 以下语句可用于检查"product"表的压缩率：

清单4.用于检查压缩率的管理功能

select tabname,pages_saved_percent,bytes_saved_percent 
from table(sysproc.admin_get_tab_compress_info('MYSCHEMA','PRODUCT','ESTIMATE')) as t

如果您的系统是I / O绑定而不是CPU绑定，则压缩XML数据可以极大地提高性能。 但是请注意，内联会大大增加数据页上的行大小。 反过来，这减少了每页的行数。 现在，仅访问表的关系列的查询需要读取的页面要比不进行内联的要大得多。 对于这些查询，这可能导致更多的I / O并降低性能。 如果您的查询通常总是触摸XML列，那么这不会影响您。

简而言之，在为XML数据考虑单独的表空间时，请使用常识。 较少的缓冲池和表空间以及较少的不同页面大小将导致更简单的物理数据库设计，从而更易于管理，维护和调优。 除非您知道它确实提供了有价值的性能优势，否则请避免引入多个页面大小。 使用内联和压缩来减少存储消耗并提高I / O性能。

技巧4：如何配置DB2以快速批量插入XML数据

DB2 9支持将XML数据从文件系统移至DB2表的两个选项：插入和导入。 从性能和调整的角度来看，插入和导入具有相似的特征，因为导入实用程序实际上执行了一系列插入。

从DB2 9.5开始，您还可以使用DB2 LOAD实用程序将XML数据移动到表中。 对于XML和关系数据，LOAD实用程序的主要优点是相同的，例如不记录数据，并且自动使用并行性来提高性能。 DB2根据CPU和表空间容器的数量确定默认的并行度。 您还可以使用LOAD命令的语法中的参数CPU_PARALLELISM和DISK_PARALLELISM设置CPU和I / O并行性。

无论您的应用程序可能通过并发插入线程执行批量插入，还是使用导入或加载，都适用以下性能准则：

• 作为关键的前提条件，请确保使用具有大页面大小的DMS表空间（请参阅技巧2 ）。
• 即使没有在目标表上定义任何索引，DB2的pureXML存储机制也透明地维护所谓的区域和路径索引，以进行有效的XML存储访问。 因此，提供足够的缓冲池空间以支持索引读取。
• 如果您在表上需要多个用户定义的XML索引，通常最好在批量插入之前定义它们，而不是在之后创建它们。 在插入期间，每个XML文档将只处理一次 ，以为所有 XML索引生成索引条目。 但是，如果发出多个“创建索引”语句，则XML列中的所有文档将被遍历多次。
• 如果您需要将大量的小型XML文件从文件系统移至DB2表中，则可以将其置于禁用了文件系统缓存的专用文件系统中，从而提高性能。 由于每个文件只能读取一次，因此不需要缓存。 在AIX上，发现使用-o cio选项挂载此文件系统是有益的。

考虑以下插入和导入操作准则：

• “ ALTER TABLE <表名> APPEND ON”启用表的追加模式。 新数据将附加到表的末尾，而不是在现有页面上搜索可用空间。 这样可以提高批量插入的运行时性能。
• 增大日志缓冲区大小（LOGBUFSZ）和日志文件大小（LOGFILSIZ）有助于提高性能。 这对于XML插入尤为重要，因为每行的数据量往往比关系数据大得多。 建议使用用于日志的快速I / O设备。
• 如果您使用“ ALTER TABLE <tablename> ACTIVATE NOT LOGGED INITIALIALLY”（NLI），则可以避免进行日志记录。 但是，请注意，如果语句失败，该表将被标记为不可访问，必须将其删除。 这通常禁止在生产系统中使用NLI进行增量批量插入，但是对于空表的初始填充可能很有用。 请注意，NLI会阻止并发插入/导入目标表，并且并行性可能会产生比NLI更高的性能。
• 如果使用import，则COMMITCOUNT参数的值较小会损害性能。 每提交100行或更多行将比提交每行更好。 您还可以省略COMMITCOUNT参数，并让DB2适当地提交。
• 为了更好地利用多个CPU和磁盘，可以同时运行多个导入命令。 确保每个导入都以其自己的数据库连接运行，并使用“ ALLOW WRITE ACCESS”子句避免表锁定。 您不需要单独的输入文件（DEL文件）即可运行并发导入。 每次导入都可以读取同一输入文件的不同部分，因为import命令允许您指定“ SKIPCOUNT m ROWCOUNT n ”来从输入文件读取m +1到m + n行。

有关其他插入性能准则，请参阅文章“在DB2通用数据库中提高INSERT性能的技巧” [请参见参考资料 ]。

简而言之，传统的插入和日志记录性能调整非常适合XML插入和导入。 如果将ALLOW WRITE ACCESS子句添加到每个导入命令，则可以运行并行导入会话。 在DB2 9.5中，使用load而不是import。

提示5：使用新的快照监视器元素来检查XML性能

无论您是研究不同页面大小的收益还是XML性能的其他方面的收益，您都可能希望像对待关系数据一样使用DB2快照监视器。 您将发现DB2 9为XML数据提供了新的缓冲池快照监视元素，这些元素与关系数据和索引的现有计数器相匹配。 由于关系数据和索引存储在表空间内的单独存储对象中，因此它们具有单独的读写计数器。 DB2 9中的pureXML存储引入了一个新的XML数据存储对象XDA，它也有自己的缓冲池计数器。

下面的示例是快照监视器输出的摘录。 您将看到三个不同存储对象的各种快照监视元素：数据，索引和XDA。 这使您可以与关系数据分开监视和分析XML的缓冲和I / O活动。 与XML索引有关的任何活动都包含在现有索引计数器中。 新XDA计数器的解释与它们对应的关系计数器相同。 例如，XDA物理读取与XDA逻辑读取的比率低表明XML数据的缓冲池命中率高，这是理想的。 有关缓冲池快照监视器元素的更多详细信息，请参阅DB2文档。

清单5.监视数据，索引和XDA存储对象的输出

Buffer pool data logical reads             = 221759
Buffer pool data physical reads            = 48580
Buffer pool temporary data logical reads   = 10730
Buffer pool temporary data physical reads  = 0
Buffer pool data writes                    = 6
Asynchronous pool data page reads          = 0
Asynchronous pool data page writes         = 6

Buffer pool index logical reads            = 8340915
Buffer pool index physical reads           = 54517
Buffer pool temporary index logical reads  = 0
Buffer pool temporary index physical reads = 0
Buffer pool index writes                   = 0
Asynchronous pool index page reads         = 0
Asynchronous pool index page writes        = 0

Buffer pool xda logical reads              = 2533633
Buffer pool xda physical reads             = 189056
Buffer pool temporary xda logical reads    = 374243
Buffer pool temporary xda physical reads   = 0
Buffer pool xda writes  
                   = 0
Asynchronous pool xda page reads           = 97728
Asynchronous pool xda page writes          = 0
Asynchronous data read requests            = 0
Asynchronous index read requests           = 0
Asynchronous xda read requests             = 83528

简而言之，快照监视器输出中的新XDA计数器反映了XML活动。 它们对于了解XML数据的缓冲池，I / O和临时空间的使用很有用。

技巧6：注意XML模式验证的开销

XML模式可以定义一组XML文档中允许的结构，元素和属性，它们的数据类型，值范围等。 DB2允许您（可选）根据XML模式验证XML文档。 如果选择验证文档，则通常在插入时进行验证。 这有两个目的。 首先，验证确保插入到数据库中的数据符合架构定义，即，防止“垃圾数据”进入表。 其次，模式验证将模式的类型注释添加到每个XML元素和属性，并且这些类型将保留在DB2的XML存储中。 例如，如果一个XML模式定义了我们的部门表（在技巧1中显示 ）中的员工ID是整数，并且针对该模式对文档进行了验证，那么DB2会在每个文档中记住员工ID的类型为xs：整数。 在雇员ID上执行字符串比较的任何尝试都将失败，并在查询运行时出现类型错误。

XML模式验证是XML解析期间的可选活动。 性能研究表明，如果启用模式验证，则XML解析通常会占用更多CPU资源[link]。 根据您的XML文档的结构和大小，尤其是所使用的XML模式的大小和复杂性，此开销可能会发生巨大变化。 例如，由于使用中等复杂模式的模式验证，您可能会发现CPU消耗增加了50％。 除非您的XML插入文件受I / O约束，否则CPU使用量的增加通常会导致插入文件吞吐量的降低。

确定您的应用程序是否需要更严格的类型检查以确保XML查询和XML模式符合性。 例如，如果使用的应用程序服务器在XML文档存储在数据库中之前先接收，验证和处理XML文档，则可能不需要在DB2中再次验证这些文档。 到那时，您已经知道它们是有效的。 或者，也许数据库从受信任的应用程序接收XML文档，甚至可能是您控制的XML文档，并且您知道XML数据始终有效。 在这种情况下，请避免进行模式验证，以提高插入性能。 但是，如果您的DB2数据库从不受信任的来源接收XML数据，并且您需要确保DB2级别的模式遵从性，那么您需要为此花费一些额外的CPU周期。

简而言之，对于高性能插入，如果确实不需要，请避免在DB2中执行模式验证。

技巧7：在XPath表达式中，尽可能使用完全指定的路径

假设我们有一个带有XML列的表

create table customer(info XML);

管理具有以下结构的“ customerinfo”文档：

清单6.示例XML文档

<customerinfo Cid="1004">
    <name>Matt Foreman</name>
    <addr country="Canada">
          <street>1596 Baseline</street>
          <city>Toronto</city>
          <state>Ontario</state>
          <pcode>M3Z-5H9</pcode>
    </addr>
    <phone type="work">905-555-4789</phone>
    <phone type="home">416-555-3376</phone>
</customerinfo>

如果要检索客户的电话号码或他们居住的城市，则无论使用XQuery还是SQL / XML，都有多种可能的路径表达式来获取该数据。 /customerinfo/phone和//phone都会为您提供电话号码。 同样， /customerinfo/addr/city以及/customerinfo/*/city返回城市。 为了获得最佳性能，与使用*或//首选使用完全指定的路径，因为它使DB2可以直接导航到所需的元素，而跳过文档的无关部分。

换句话说，如果您知道所需元素在文档中的位置，则有助于以完全指定的路径的形式提供该信息。 如果您要求//phone而不是/customerinfo/phone ，则要求在文档中的任何位置输入phone元素。 这要求DB2向下导航到文档的"addr"子树中以在文档的任何级别上查找phone元素。 这是可以避免的开销。

请注意，*和//也可能导致意外或意外的查询结果。 例如，如果某些“ customerinfo”文档还包含“辅助”信息，例如以下信息。 //phone路径将返回客户电话and助理电话号码，而不会区分它们。 从查询结果中，您甚至不会知道并错误地将助手的电话处理为客户电话号码。

清单7.文档中多个级别的phone和name元素

<customerinfo Cid="1004">
    <name>Matt Foreman</name>
    <addr country="Canada">
          <street>1596 Baseline</street>
          <city>Toronto</city>
          <state>Ontario</state>
          <pcode>M3Z-5H9</pcode>
    </addr>
    <phone type="work">905-555-4789</phone>
    <phone type="home">416-555-3376</phone>
    <assistant>
          <name>Peter Smith</name>
          <phone type="home">416-555-3426</phone>
     </assistant>
</customerinfo>

简而言之，请避免在路径表达式中使用*和//，并尽可能使用完全指定的路径。

技巧8：定义精益XML索引，避免索引所有内容

假设我们的查询经常按客户名称搜索“ customerinfo”文档。 客户名称元素上的索引可以大大提高此类查询的性能。 让我们看下面的例子：

清单8.支持按客户名称搜索的索引

create table customer(info XML);

create index custname1 on customer(info) 
generate key using xmlpattern '/customerinfo/name' as sql varchar(20);

create index custname2 on customer(info) 
generate key using xmlpattern '//name' as sql varchar(20);

select * from customer
where xmlexists('$i/customerinfo[name = "Matt Foreman"]' passing info as $i);

上面定义的两个索引都有资格评估客户名称上的XMLEXISTS谓词。 但是，索引custname2可以比索引custname1因为它不仅包含客户名称的索引条目，还包含助理名称的索引条目。 这是因为XML模式//name与文档中任何地方的name元素匹配。 但是，如果我们从不按助理姓名搜索，则不需要对它们进行索引。

对于读取操作，索引custname1较小，因此可能具有更好的性能。 对于插入，更新和删除操作，索引custname1仅对客户名称产生索引维护开销，而索引custname2需要对客户and助理名称进行索引维护。 如果您需要最大的插入/更新/删除性能，并且不需要基于助手名称的索引访问，那么您当然不想支付该额外费用。

还请考虑以下“索引所有内容”的heavyIndex 。 它包含每个文本节点的索引条目，即XML列中每个XML文档中的每个叶子元素值。 这样的索引在插入/更新/删除操作期间维护成本非常高，消耗大量存储空间，通常不建议这样做。 唯一的例外是写活动少和不可预测的查询工作量的应用程序，因此更难定义更具体的索引。

create index heavyIndex on customer(info) 
generate key using xmlpattern '//text()' as sql varchar(20);

简而言之，在定义XML索引时应尽可能精确，如果可以，请避免使用*和//。

技巧9：将文档过滤谓词放在XMLEXISTS中，而不是XMLQUERY中

让我们考虑下表和数据：

create table customer(info XML);

表2.客户表中的三行数据

<customerinfo> <name>Matt Foreman</name> <phone>905-555-4789</phone> </customerinfo>
<customerinfo> <name>Peter Jones</name> <phone>905-123-9065</phone> </customerinfo>
<customerinfo> <name>Mary Poppins</name> <phone>905-890-0763</phone> </customerinfo>

给定此表，假定您要返回电话号码为“ 905-555-4789”的客户的姓名。 您可能会想写以下查询

select xmlquery('$i/customerinfo[phone = "905-555-4789"]/name' passing info as "i") 
from customer;

但是，由于多种原因，此查询不是您想要的：

1. 它返回以下结果集，该结果集的行数与表中的行数相同。 这是因为SQL语句没有where子句，因此无法消除任何行。

   <name>马特领班人</ name>

   已选择3条记录

2. 对于表中与谓词不匹配的每一行，将返回包含空XML序列的行。 这是因为XMLQUERY函数中的XQuery表达式一次应用于一行（文档），并且从不删除结果集中的一行，而只修改其值 。 该XQuery产生的值（如果谓词为true则为客户的name元素），否则为空序列。 这些空行在语义上是正确的（根据SQL / XML标准），并且如果以这种方式编写查询，则必须返回这些空行。
3. 该查询的性能不会很好。 首先，可能无法使用/customerinfo/phone上存在的索引，因为不允许此查询消除任何行。 其次，返回许多空行会使此查询不必要地变慢。

要解决性能问题并获得所需的输出，应仅在select子句中使用XMLQUERY函数来提取客户名称，并将应消除行的搜索条件移动到where子句中的XMLEXISTS谓词中。 这将允许索引使用，行过滤，并避免了空结果行的开销。 通过以下方式编写查询：

select xmlquery('$i/customerinfo/name' passing info as "i") 
from customer
where xmlexists('$i/customerinfo[phone = "905-555-4789"]' passing info as "i")

<name>马特领班人</ name>

已选择1条记录

简而言之，XMLQUERY函数中的谓词仅应用于每个XML值内，因此它们绝不会消除任何行。 文档过滤和行过滤谓词应进入XMLEXISTS函数。

技巧10：使用方括号[]避免XMLEXISTS中的布尔谓词

一个常见的错误是在XMLEXISTS函数中编写没有方括号的上一个查询：

select xmlquery('$i/customerinfo/name' passing info as "i") 
from customer
where xmlexists('$i/customerinfo/phone = "905-555-4789"' passing info as "i")

这将产生以下结果：

<name>马特领班人</ name>
<name>彼得·琼斯</ name>
<name> Mary Poppins </ name>

已选择3条记录

编写XMLEXISTS谓词中的表达式，以便XMLEXISTS始终求值为true。 因此，没有行被消除。 这是因为，对于给定的行，仅当内部的XQuery表达式返回空序列时，XMLEXISTS谓词的评估结果才为false。 但是，没有方括号，XQuery表达式是一个布尔表达式，它总是返回一个布尔值，而不是空序列。 请注意，XMLEXISTS会真正检查一个值的存在 ，并且如果存在一个值，则即使该值碰巧是布尔值“ false”，也将评估为true。 根据SQL / XML标准，这是正确的行为，尽管这可能不是您想要表达的。

再次造成的影响是phone上的索引无法使用，因为不会删除任何行，并且您收到的行比实际需要的多得多。 另外，请注意在使用两个或多个谓词时不要犯同样的错误，如以下查询所示：

清单9. XMLEXISTS中两个谓词的错误使用

select xmlquery('$i/customerinfo/name' passing info as "i") 
from customer
where xmlexists('$i/customerinfo[phone = "905-555-4789"] and 
		 $i/customerinfo[name = "Matt Foreman"]' 
      passing info as "i")

该查询使用方括号，所以怎么了？ XQuery表达式仍然是布尔表达式，因为它的形式为“ exp1和exp2 ”。 这是编写此查询以过滤行并允许使用索引的正确方法：

清单10.更正查询以过滤行并允许使用索引

select xmlquery('$i/customerinfo/name' passing info as "i") 
from customer
where xmlexists('$i/customerinfo[phone = "905-555-4789" and name = "Matt Foreman"]' 
      passing info as "i")

简而言之，不要在XMLEXISTS中使用布尔谓词。 将谓词放在方括号中，包括任何“与”和“或”。

提示11：使用RUNSTATS收集XML数据和索引的统计信息

RUNSTATS实用程序已扩展为收集有关XML数据和XML索引的统计信息。 DB2的基于成本的优化器使用这些统计信息来生成XQuery和SQL / XML查询的有效执行计划。 因此，请像对待关系数据一样继续使用RUNSTATS。 如果表包含关系和XML数据，并且您只想刷新关系统计信息，则可以使用新子句“ EXCLUDING XML COLUMNS”执行RUNSTATS。 如果没有此子句，则默认的首选行为是始终收集有关关系和XML数据的统计信息。

对于关系数据和XML数据，可以启用采样以减少执行runstats的时间。 在大型数据集上，来自10％（甚至更少）数据的统计数据通常仍非常代表总人口。 无论选择哪种采样百分比，runstats都可以对行（ Bernoulli采样 ）或页面（ 系统采样 ）进行采样 。 行级采样读取所有数据页，但仅考虑每一页上一定百分比的行，因此仅考虑相应XDA页的子集。 页面级采样显着减少了I / O，因为它仅读取一部分数据页。 因此，如果您的表不仅包含XML，还包含大量的关系数据，则页面采样可以显着提高性能。 但是，如果关系数据值高度聚类，那么行级采样可能会产生更准确的统计信息。

这里有些例子。 第一个runstats命令收集表客户及其所有索引的最全面，最详细的统计信息，而无需进行抽样。 如果执行时间允许，这是理想的选择。 seconds命令收集相同的统计信息，但只收集10％的页面。 在许多情况下，这将为优化器提供与第一个命令几乎相同的统计信息，但返回结果的速度要快得多。 The third command samples 15% of all rows, does not collect distribution statistics, and also applies sampling to indexes which the first and second commands didn't.

Listing 11. Use RUNSTATS to collect statistics

runstats on table myschema.customer 
with distribution on all columns and detailed indexes all;

runstats on table myschema.customer 
with distribution on all columns and detailed indexes all tablesample system (10);

runstats on table myschema.customer 
on all columns and sample detailed indexes all tablesample bernoulli (15);

In a nutshell, the DB2 does generate better execution plans if XML statistics are available. Use runstats as you normally would, or use runstats with sampling to reduce its execution time.

Tip 12: How to use SQL/XML publishing views to expose relational data as XML

SQL/XML publishing functions allow you to convert relational data into XML format. It can be beneficial to hide the SQL/XML publishing functions in a view definition, so that applications or other queries can simply select the constructed XML documents from the view instead of dealing with the publishing functions themselves.

Listing 12. SQL/XML publishing functions hidden in a view

create table unit( unitID varchar(8), name varchar(20), manager varchar(20));

create view UnitView(unitID, name, unitdoc) as
   select unitID, name, 
       XMLDOCUMENT(
          XMLELEMENT(NAME "Unit",
          XMLELEMENT(NAME "ID", u.unitID),
          XMLELEMENT(NAME "UnitName", u.name),
          XMLELEMENT(NAME "Mgr", u.manager)
             )   )
   from unit u;

Note that we included some of the relational columns in the view definition. This does not create any physical redundancy because it is just a view, not a materialized view. Exposing the relational columns helps to query this view efficiently. Let's say we need to fetch an XML document for one particular unit. All of the following three queries can do that, but the third one tends to perform better than the first two.

In the first two queries, the filtering predicate is expressed on the constructed XML. But, XML predicates can not be applied to the underlying relational column or its indexes. Thus, these queries require the view to construct XML for all units and then pick out the one for unit "WWPR". This is not optimal.

May perform sub-optimally:

Listing 13. Queries that perform sub-optimally

select unitdoc
from UnitView
where xmlexists('$i/Unit[ID = "WWPR"]' passing unitdoc as "i");

for $u in db2-fn:xmlcolumn('UNITVIEW.UNITDOC')/Unit
where $u/ID = "WWPR"
return $u;

The third query uses a relational predicate to ensure that only the XML document for "WWPR" is constructed, resulting in a shorter runtime, especially on a large data set. This query will perform well:

Listing 14. Query that performs well

select unitdoc
from UnitView
where UnitID = "WWPR";

In a nutshell, include relational columns in a SQL/XML publishing view, and when querying the view express any predicates on those columns rather than on the constructed XML.

Tip 13: How to use XMLTABLE views to expose XML data in relational format

Just like it can be useful to create a view to expose relational data in XML format, you may want to use a view to expose XML data in relational format. Similar caution needs to be applied as in Tip 12 , but in the reverse way. Let's look at the following example where the SQL/XML function XMLTABLE is used to return values from XML documents in tabular format:

Listing 15. Values returned from XML documents in tabular format

create table customer(info XML);

create view myview(CustomerID, Name, Zip, Info) as 
SELECT T.*, info 
FROM customer, XMLTABLE ('$c/customerinfo' passing info as "c" 
   COLUMNS 
   "CID"     INTEGER      PATH './@Cid',
   "Name"    VARCHAR(30)  PATH './name',
   "Zip"     CHAR(12)     PATH './addr/pcode' ) as T;

Note that we included the XML column info in the view definition to help query this view efficiently. Let's say you want to retrieve a tabular list of customer IDs and names for a given ZIP code. Both of the following queries can do that, but the second one tends to perform better than the first. In the first query, the filtering predicate is expressed on the CHAR column "Zip" generated by the XMLTABLE function. But, relational predicates cannot be applied to the underlying XML column or its indexes. Thus, this query requires the view to generate rows for all customers and then picks out the one for zip code "95141". This is not optimal. The second query uses an XML predicate to ensure that only the rows for "95141" get generated, resulting in a shorter runtime, especially on a large data set.

Listing 16. Query with an XML predicate

-- may perform suboptimal:

select CustomerID, Name 
from myview
where Zip = "95141";


-- will perform well:

select CustomerID, Name
from myView
where xmlexists('$i/customerinfo[addr/pcode = "95141"]' passing info as "i");

If the base table on which the view is defined contains not just an XML column but also relational columns with indexes, you should include those relational columns in the view definition. If the queries against the view contain highly restrictive predicates on the relational columns, DB2 uses the relational indexes to filter qualifying rows to a small number, and then applies XMLTABLE and any remaining predicates to this interim result before returning the final result set.

In a nutshell, be careful with XMLTABLE views which expose XML data in relational form. When possible, include additional columns in the view definition so that filtering predicates can be expressed on those columns instead of the XMLTABLE columns.

Tip 14: For short queries or OLTP applications, use SQL/XML statements with parameter markers

Very short database queries often execute so fast that the time to compile and optimize them is a substantial portion of their total response time. Thus, it's useful to compile ("prepare") them just once and only pass predicate literal values for each execution. While DB2 9 XQuery does not support external parameters, the SQL/XML functions XMLQUERY, XMLTABLE and XMLEXISTS do. They allow you to pass SQL parameter markers as a variable into the embedded XQuery expressions. This is recommended for applications with short and repetitive queries.

Listing 17. Hardcoded predicate literal values

for $c in db2-fn:xmlcolumn('CUSTOMER.INFO')/customer
where $c/phone = "905-555-4789"
return $c;

select info 
from customer
where xmlexists('$i/customerinfo[phone = "905-555-4789"]' 
                passing info as "i")

Listing 18. With parameter marker

select info 
from customer
where xmlexists('$i/customerinfo[phone = $p]' 
                passing info as "i", cast(? as varchar(12)) as "p")

In a nutshell, short queries and OLTP transactions are faster as prepared statements with parameter markers. For XML, this requires SQL/XML to pass SQL-style parameters to XQuery expressions.

Tip 15: Avoid code page conversion during XML insert and retrieval

XML is different from other types of data in DB2 because it can be internally and externally encoded. Internally encoded means that the encoding of your XML data can be derived from the data itself. Externally encoded means that the encoding is derived from external information. The data type of the application variables which you use to exchange XML data with DB2 determines how the encoding is derived. If your application uses character type variables for XML, then it is externally encoded, ie in the application code page. If you use binary application data types, then the XML data is considered internally encoded. Internally encoded means that the encoding is determined by either a Unicode Byte-Order mark (BOM) or an encoding declaration in the XML document itself, such as

<？xml版本=“ 1.0”编码=“ UTF-8”吗？>

From a performance point of view, the goal is to avoid code page conversions as much as possible since they consume extra CPU cycles. Internally encoded XML data is preferred over externally encoded data because it can prevent unnecessary code page conversion. This means that in your application you should prefer binary data types over character types. For example, in CLI when you use SQLBindParameter() to bind parameter markers to input data buffers, you should use SQL_C_BINARY data buffers rather than SQL_C_CHAR, SQL_C_DBCHAR, or SQL_C_WCHAR. When inserting XML data from Java applications, reading in the XML data as a binary stream (setBinaryStream) is better than as a string (setString). Similarly, if your Java application receives XML from DB2 and writes it to a file, code page conversion may occur if the XML is written as non-binary data.

When you retrieve XML data from DB2 into your application, it is serialized. Serialization is the inverse operation of XML parsing. It is the process of converting DB2's internal XML format (a parsed, tree-like representation) into the textual XML format that your application can understand. In most cases it is best to let DB2 perform implicit serialization. This means your SQL/XML statements simply select XML-type values as in the following example, and DB2 performs the serialization into your application variables as efficiently as possible:

Listing 19. Query with implicit serialization

create table customer(info XML);

select info from customer where...;

select xmlquery('$i/customerinfo/name' passing info as "i") 
from customer
where...;

If your application deals with very large XML documents, it can be beneficial to use LOB locators for data retrieval. This requires explicit serialization to a LOB type, preferably BLOB, because explicit serialization into a character type such as CLOB can introduce encoding issues and unnecessary code page conversion. Explicit serialization uses the XMLSERIALIZE function:

select XMLSERIALIZE(info as BLOB(1M)) from customer where...;

In a nutshell, use binary data types in your application for exchanging XML with DB2 as this avoids unnecessary code page conversion. Be aware of encoding issues and when in doubt, follow the detailed guidelines in the DB2 9 documentation

摘要

To achieve maximum XML performance in DB2, a good start is to use DB2's autonomic features such as automatic storage and self-tuning memory management. This provides decent, out-of-the-box performance for many applications. It also frees up valuable DBA time for more dedicated performance tuning, when needed. All the conventional DB2 performance wisdom still applies to XML and is covered in a variety of developerWorks articles which are listed below.

On top of that, the 15 tips in this article can help you with common XML-specific performance aspects. If you need to improve the performance of your XML application, you don't need to apply all 15 tips but only the 1 or 2 that really matter in your situation. For example, reducing unnecessary code page conversion is not goign to help if your system is heavily I/O bound due to an unfortunate table space configuration. Similarly, using SQL/XML parameter markers may not help with the query performance if you actually need to execute runstats to enable better query execution plans. In short, the tips in this paper can help you to avoid performance issues, but fixing observed performance problems first requires identification of the root cause and bottlenecks. The standard diagnostic tools in DB2 such as visual explain, db2exfmt, and the snapshot monitor can be used for XML performance investigations just like for relational data.

致谢

Thanks to Cindy Saracco, Irina Kogan, Henrik Loeser, Nikolaj Richers and Marcus Roy for their reviews and help with this article.

---

翻译自: https://www.ibm.com/developerworks/data/library/techarticle/dm-0610nicola/index.html