简化 DB2 for i5/OS 索引建议

学习如何使用新的 Index Advice Condenser 简化 DB2 性能调优

理解和实现最佳 SQL 查询优化目标可以大大改善很多查询的响应时间。IBM® DB2® for i5/OS® on V5R4 提供了一种新方法,通过一些流行的动态 SQL 接口控制查询优化。本文展示如何用 CLI 实现新的性能调优增强。作为附加的优点,您将必须利用 iSeries Navigator 的性能监视器特性分析任何查询的优化目标。

Scott L. Forstie (forstie@us.ibm.com), 资深软件工程师, IBM

Scott Forstie 是位于明尼苏达州罗彻斯特城的 DB2 for i5/OS 开发小组的 SQL 开发主管。在从事 DB2 方面的工作之前,他还从事过 AS/400 和 S/390 系统上的 UNIX 启用方面的工作。



2007 年 7 月 19 日

DB2 for i5/OS on V5R4 经过改进,现包含一个 Index Advice Condenser。查询和 SQL 活动会留下一些索引建议。当考虑是否创建永久索引时,很多原始的索引建议可以看作是互补的或者可压缩的。本文解释新的 Index Advisor 压缩特性,并演示如何在 iSeries Navigator 图形界面中使用该特性,或直接用一个定制的用户查询来访问该特性。

DB2 for i5/OS Index Advisor

Index Advisor 是 DB2 for i5/OS V5R4 中引入的特性,该特性记录关于系统索引需要的统计信息。 每当执行一个查询或 SQL 语句时,Index Advisor 都会记录查询优化器建议使用的一些索引,留住提高性能的机会。如果对 DB2 for i5/OS 上的索引建议不熟悉,可以参考“OnDemand Index Advice for DB2 for i5/OS”。这方面的基础知识有助于理解这个新的 Index Advice Condenser。

每当建议一个索引时,就有一个新行被添加到系统索引建议表中,即模式 QSYS2 中的 SYSIXADV 表。这里是假设该索引之前还没有被建议。Index Advisor 检查以下列中的属性,以判断一个索引是否已经被优化器建议:

  • LEADING_COLUMN_KEYS
  • KEY_COLUMNS_ADVISED
  • TABLE_NAME
  • TABLE_SCHEMA
  • INDEX_TYPE
  • PARTITION_NAME
  • NLSS_TABLE_NAME
  • NLSS_TABLE_SCHEMA

表 3 给出了 SYSIXADV 表的完整布局。如果表中已经有匹配的建议,则更新已有的行。例如,“Number of Times Advised”条目加一,“Last Advised for Query Use”被更新为当前时间。

就原始格式而言,索引建议不一定是冗长的。虽然表中所有条目都是惟一的,但是有些条目仍然存在冗余。一个很好的例子就是,如果有相同的一些列被建议,建议的顺序有所不同,但这些键的顺序无关紧要,那么就会出现上述情况。在这种情况下,可以用这些列创建覆盖不同索引实例的一个索引。对被建议的键重新排序的时机可通过“Leading Keys Order Independent”列表明。这一列中列出的键是可以被重新排序,且仍然能够满足被建议的索引的领头键。

被建议索引的示例

我们来看一些索引建议的示例,以便为 condenser 的简化工作搭建背景。下面的例子假设所有建议都是基于相同表、相同分区、相同的索引类型和排序顺序的。

表 1. 被建议索引的示例 1
被建议的键领头键,顺序无关
C1, C2, C3C1, C2, C3
C1, C3, C2C1

在这个例子中,建议的第二行不易变通。 为了满足索引建议,一个永久的索引需要有顺序为 { C1, C3, C2 } 的键。但是,建议的第一行有很大的灵活性。领头键顺序无关这个细节表明,键之间可以按任意顺序排列,所以可以通过将 { C1, C2, C3 } 重新排序为 { C1, C3, C2 },将两行建议压缩成一个永久索引。

如果扩展表 2 中的例子,使之包括第三行建议,那么仍然可以得出结论,附加的一个永久索引 { C1, C3, C2, C4 } 应该足以帮助优化器永久地提高这些查询的性能。

表 2. 被建议索引的示例 2
被建议的键领头键,顺序无关
C1, C2, C3C1, C2, C3
C1, C3, C2C1
C1, C3, C2, C4C1, C2, C3, C4

Index Advisor 与压缩的索引建议的比较

压缩索引建议技术要求在服务器上安装以下 V5R4M0 5722-SS1 PTF:SI25391、SI25469 和 SI25470。

当压缩索引建议时,原始索引建议变成概要形式。原始建议中的一些字段是特定于建议实例的,在概要形式中没有价值。 为了保持压缩的建议的重要性,其它字段则很重要。 表 3 逐列解释了在索引建议压缩期间原始索引建议发生的变化。 跟 Index Advisor 下相比,压缩的索引建议允许进行与之相同的 iSeries Navigator 动作,只有一个例外。由于压缩的索引建议不是永久存在的,因此没有“Remove from List”动作。

表 3. Condenser 对列的更改
列名列标题压缩动作
Table_NAME建议索引时所基于的表保留,不作更改
Table_SCHEMA包含表的模式保留,不作更改
SYSTEM_TABLE_NAME建议索引时所基于的系统表的表名保留,不作更改
PARTITION_NAME索引的分区细节保留,不作更改
KEY_COLUMNS_ADVISED被建议索引的列名生成
LEADING_COLUMN_KEYS领头的、顺序无关的键,这些键在 Key_Columns_Advised 字段的开头部分,可以重新排列该字段且仍能满足被建议的索引使用后丢弃
INDEX_TYPE基数(默认)或编码向量索引(EVI)保留,不作更改
LAST_ADVISED该行的上一次更新时间使用最新建议时间戳
TIMES_ADVISED索引被建议的次数通过总计生成
ESTIMATED_CREATION_TIME估计创建索引所需的秒数使用最大值
REASON_ADVISED表明索引被建议的原因的原因码丢弃
LOGICAL_PAGE_SIZE建议用于索引的页面大小使用最大值
MOST_EXPENSIVE_QUERY查询的执行时间,单位为秒使用最大值
AVERAGE_QUERY_ESTIMATE查询的平均执行时间,单位为秒通过求平均值生成
Table_SIZE当索引被建议时表中的行数使用最新建议中的值
NLSS_TABLE_NAME用于索引的排序顺序表保留,不作更改
NLSS_TABLE_SCHEMA排序顺序表的库名保留,不作更改
MTI_USED因匹配的永久索引不存在,而使用与被建议定义相匹配的 MTI(被维护的临时索引,自主索引)的次数丢弃
MTI_CREATED这个特定的索引建议被用于创建 MTI 的次数丢弃
LAST_MTI_USED上一次因匹配的永久索引不存在而使用 MTI 的时间丢弃

下面的 SQL 语句用于生成 图 1a图 1b 中包含的原始索引建议。

清单 1. 用于生成索引建议的数据库和查询
 -- Create sample database in CONDENSE schema
 CALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');
 SET SCHEMA Condense;

 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';
 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');
 SELECT e.firstnme, d.deptnmae FROM department d, employee e
        ORDER BY e.job, e.sex, e.birthdate;

图 1b 只包含 图 1a 中放不下的索引建议属性。注意,‘Keys Advised’列下的键的顺序有所不同。

图 1a. 被建议索引输出
被建议的索引
图 1b. 被建议索引输出(续)
被建议的索引

在这个例子中,两个被建议索引的键顺序有足够的灵活性,允许将建议压缩成一个索引。图 2 显示了压缩的索引建议。除了为用户提供压缩列键顺序 { JOB, SEX, BIRTHDATE } 外,压缩的建议还包括帮助确定建议的重要性的上下文信息。通过“Times Advised for Query Use”和“Average of Query Estimates”等列可以看出永久索引能为这个环境带来多大的好处。而“Estimated Index Creation Time”列用于判断是否要将创建索引作为预定的活动。

图 2. 压缩的索引建议
压缩的建议

iSeries Navigator 压缩器界面

在 iSeries Navigator 中,有 Index Advisor 的地方就有压缩索引建议动作。图 3 显示了压缩器的图形化界面,该界面可以通过右键单击一个模式对象打开。也可以从一个表对象中进行访问。

图 3. iSeries Navigator 压缩器界面
压缩器界面

为了使用压缩器的 iSeries Navigator 界面,需要在客户机上安装最新的 V5R4M0 iSeries Access for Windows Service Pack

可编程压缩器接口

除了 iSeries Navigator 界面以外,还可以用一条 SQL 语句以可编程方式访问索引建议压缩器。DB2 for i5/OS 在 QSYS2 模式中提供了一个新的视图 CondensedIndexAdvice。清单 2 显示了新的 CondensedIndexAdvice 视图返回的数据。由于 CondensedIndexAdvice 视图是用一个用户定义表函数(UDTF)实现的,因此该视图本身是只读的。任何修改该视图的尝试都会出现 SQL0150 错误。

清单 2. CondensedIndexAdvice 视图定义
QSYS2.CONDENSEDINDEXADVICE ( 	
TABLE_NAME              FOR COLUMN TABNAME      VARCHAR(258) CCSID 37 NOT NULL , 
TABLE_SCHEMA            FOR COLUMN TABSCHEMA    CHAR(10) CCSID 37 NOT NULL , 
SYSTEM_TABLE_NAME       FOR COLUMN SYS_TNAME    CHAR(10) CCSID 37 NOT NULL , 
PARTITION_NAME          FOR COLUMN TABPART      VARCHAR(128) CCSID 37 DEFAULT NULL , 
KEY_COLUMNS_ADVISED     FOR COLUMN KEYSADV      VARCHAR(16000) CCSID 37 DEFAULT NULL , 
INDEX_TYPE                                      CHAR(14) CCSID 37 DEFAULT NULL , 
LAST_ADVISED            FOR COLUMN LASTADV      TIMESTAMP DEFAULT NULL , 
TIMES_ADVISED           FOR COLUMN TIMESADV     BIGINT DEFAULT NULL , 
ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME      INTEGER DEFAULT NULL , 
LOGICAL_PAGE_SIZE       FOR COLUMN "PAGESIZE"   INTEGER DEFAULT NULL , 
MOST_EXPENSIVE_QUERY    FOR COLUMN QUERYCOST    INTEGER DEFAULT NULL , 
AVERAGE_QUERY_ESTIMATE  FOR COLUMN QUERYEST     INTEGER DEFAULT NULL , 
TABLE_SIZE                                      BIGINT DEFAULT NULL , 
NLSS_TABLE_NAME         FOR COLUMN NLSSNAME     CHAR(10) CCSID 37 DEFAULT NULL , 
NLSS_TABLE_SCHEMA       FOR COLUMN NLSSSCHEMA   CHAR(10) CCSID 37 DEFAULT NULL )

通过下面的 SELECT 语句可以看到,编写 SQL 语句来访问压缩的建议很简单。如前所述,压缩的索引建议分析可以在表级进行,也可以在模式级进行。

清单 3. CondensedIndexAdvice 查询示例
-- Condensed index advice for an entire schema, named CONDENSE
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;

-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schema
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
       WHERE table_name = 'EMPLOYEE'  AND table_schema = 'CONDENSE';

-- Condensed index advice for a range of schemas, where the average 
-- query estimate driving the index advice is greater than 10 seconds
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
   WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;

新的 CondensedIndexAdvice 视图使用的 Condense_Advice UDTF 还可以直接通过用户查询来访问。下面是这个表函数的定义,另外还有一个简单的例子,用于演示如何使用该 UDTF。

清单 4. Condense_Advice UDTF
CREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128), 
                                      TABLE_NAME VARCHAR(128) ) 
  RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000), 
                INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT, 
                ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
                MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
                TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
  LANGUAGE C 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  SCRATCHPAD 325064 
  DISALLOW PARALLEL 	
  FINAL CALL 
  CARDINALITY 1 
  EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)' 
  PARAMETER STYLE DB2SQL;   


-- Query the condenser UDTF directly, providing selection criteria and ordering 
-- the results of the 15 most important condensed entries
     SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
       WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
     ORDER BY average_query_estimate DESC 
     FETCH FIRST 15 ROWS ONLY ;

结束语

不管是使用压缩索引建议还是原始索引建议,都是改善索引策略的一个有效的办法。 在根据建议采取行动之前,需要考虑的重要的一点是查看已有的索引和它们的使用情况。由于任何索引都隐含着维护成本,因此任何性能调优活动的共同之处就是限制永久索引的数量。本文的主旨是,查看压缩的索引建议,以发现改善性能的机遇,并且在作出任何更改之前和之后,查看索引的使用情况。

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=241758
ArticleTitle=简化 DB2 for i5/OS 索引建议
publish-date=07192007