DB2使用db2advis工具调优SQL

转自 http://www.dataguru.cn/thread-227495-1-1.html

在之前的博文中说了如何去查看SQL的访问计划，当我们发现当前计划需要调整或者想看看有无优化空间时，我们可以使用db2advis工具，该工具是针对用户提供的工作负载（这里的工作负载就是一组SQL语句的组合）而给出的优化建议，优化建议包括创建索引，MQT，MDC以及表重分布等，优化建议以DDL的形式提供，用户直接运行即可。另外还会给出该工作负载未使用的索引及MQT等建议删除的信息。
目前个人感觉索引的建议比较有用，另外也不必删除索引吧，因为并不是只有当前的这个工作负载。

 

常见的三种用法

1.指定单条SQL语句

$ db2advis -d cphapp -s "select * from test" -o result.out

说明：

• -d 指定数据库名称
• -s 指定单条SQL语句用于评估并给出索引建议，需要注意的是SQL语句需要由双引号包围
• -o 指定建议输出至文件。

 
2.针对DB2快照中捕获的一组SQL

$ db2 reset monitor for database cphapp
$ db2advis -d cphapp -g -p -o result.out

说明：首先重置数据库监视器，然后让应用程序运行足够长的时间，以使DB2快照可以捕获到足够的动态SQL语句，然后使用db2advis工具估计工作负载并给出建议。

• -g 指示从动态SQL快照获得SQL语句
• -p 指出将捕获的SQL语句存储在ADVISE_WORKLOAD表中（此表在运行EXPLAIN表脚本时创建）

 
3.用户提供工作负载文件，db2advis根据此文件给出建议

$ db2advis -d cphapp -i input.sql -o result.out

说明：-i指定用于分析的工作负载文件。
工作负载文件说明：
定义SQL语句的执行频率：“–#SET FREQUENCY 100”
定义注释：“–comment“

 

部分参数的描述

• -m：指定返回的建议类型，包括IMCP–I（索引）M（MQT物化视图）C（MDC多维聚簇表）P（repartition），默认是I即索引。
• -q schema-name：即限定工作负载中未指定模式的查询对象的模式名。
• -l disk-limit：限定所建议的索引或物化视图所占的磁盘空间，单位是M。-1表示较大size；默认是数据库总大小的20%
• -delim char：指定工作负载文件中的语句结束符，默认为“；”
• -b tablespace-name：指定建议创建的MQT所在表空间

 
现在遇到的问题是不管使用单条SQL方式还是定义工作负载文件方式，db2advis不会给出创建索引的建议
此问题已经解决，原来是创建索引所分配的空间不足导致不给出任何建议，同样的SQL语句，首先使用

$ db2advis -d cphapp -i stmt.db2 -m I

没有给出任何建议，但是在建议的开头有两行：
total disk space needed for initial set [0.000]MB
total disk space constrained to [967.930]MB

当我使用另一个参数时：

$ db2advis -d cphapp -i stmt.db2 -l -1 -m I

给出了我预期的建议，建议创建一个索引，同样在建议的开头有两行：
total disk space needed for initial set [1453.513]MB
total disk space unconstrained

总结：使用了选项-l -1将不限制创建索引所占用的空间，默认是总空间的20%，超过的话将不给出创建索引的建议。