DB2性能优化 – 如何通过db2优化工具提升SQL查询效率

用户通常报告一下性能问题：

l  事务或查询的响应时间比预期长

l  事务吞吐量不足以完成必需的工作负载

l  事务吞吐量减少

DB2要提高性能的方法，简单的可从以下四个方面下手：

SQL

Bufferpool

Lock

SORTHEAP

那么如何能获得最佳性能的SQL呢？ 下面我们了解一下DB2 提供的几种相关工具：

Ø  DB2 Visual Explain

DB2 Visual Explain 能够获得可视化的查询计划。有了查询计划，我们就可以有针对的对查询进行优化。根据查询计划找出代价最高的扫描 ( 表扫描，索引扫描等 ) 和操作 (Join，Filter，Fetch 等 )，继而通过改写查询或者创建索引消除代价较高的扫描或操作来优化查询。

Ø  db2exfmt

db2exfmt 命令能够将 Explain 表中存储的存取计划信息以文本的形式进行格式化输出。db2exfmt 命令将各项信息更为直观的显示，使用起来更加方便。

Ø  db2expln

db2expln 命令和前面说过的 Visual Explain 功能相似。通过该命令可以获得文本形式的查询计划。db2expln 是命令行下的解释工具。

Ø  db2advis

db2advis 是 DB2 提供的另外一种非常有用的命令。通过该命令 DB2 可以根据优化器的配置以及机器性能给出提高查询性能的建议。

就目前来说，我们用的最多的是db2advis, 因为此工具给的建议更直观，这种建议主要集中于如何创建索引，这些索引可以降低多少查询代价，需要创建哪些表或者 Materialized Query Table(MQT) 等。因此以下我们主要来分析如何用db2advis提高SQL语句查询性能。

db2advis命令如下所示：

db2advis -d <db_name> -a <user>/<password> -i <sql.file> -o <output>

Example: db2advis -d test_db -a user/password -i D:\temp\sql_2.txt > D:\temp\sql_2_result_db2advis.txt

db2数据库中通常出现消耗时间成本很高的sql语句，耗时长的sql语句会长时间占用各种资源，如CPU, Memory, 事务日志等，增加其他sql语句的等待时间，导致整个数据库性能变差。因此我们会时刻监控性能差的sql。

以下的例子是我在南基仓库碰到一个性能很差的语句。

我们这边首先收到告警：

[BOMC]告警、级别：2,IP地址：172.16.5.48,告警时间：2017-02-08 07:04:42，告警内容： 172.16.5.48*BASSDB_LE_DBS-执行超过1个小时且长时间占用大量事务日志应用：进程号1573执行时长89;

当我登陆上去查看时sql已经跑完，于是通过进程号1573查询对应的历史记录查到以下sql语句：

select op_time, channel_city_name, channel_region_name, promo_name, promo_id , cond_name , cond_id, user_id, product_no , valid_date , channel_name , channel_type1 , channel_type2 , channel_type3 , op_id , op_name 

from (select * from (  select  rownumber() over(order by channel_city_id asc) as row_,temp_.*

from (select  *  from bass2.stat_act_repeat_order a where 1=1  and a.op_time='2017-01-17' order by channel_city_id asc ) as temp_ )

as temp2_ where row_  between 0+1 and 15) a    

由于语句较长，我将这个语句封装到test2.sql中来执行优化，优化之前要确定语句之间没有断句，并且不能有双引号““，如有的话将其替换成单引号‘’。

以下是详细的优化过程：

bash-3.2$ db2advis -d bassdb -i test2.sql                    ----我们把待优化的sql语句写在test.sql2里，这种方法适合较长的sql

Using user id as default schema name. Use -n option to specify schema

    CALL SYSPROC.GET_DBSIZE_INFO failed, sqlcode = -443. Getting database size from the catalog tables.

execution started at timestamp 2017-02-08-09.52.12.667113

found [1] SQL statements from the input file

Recommending indexes...

  total disk space needed for initial set [  15.060] MB          ----需要创建的索引总大小

total disk space constrained to         [2227893.025] MB

Trying variations of the solution set.

  1  indexes in current solution

 [3428.0000] timerons  (without recommendations)           ----未优化前所需花费时间成本为3428

 [ 96.0000] timerons  (with current solution)                  ----预计优化后所需花费时间成本为96

 [97.20%] improvement                                    ----可提升查询效率为97.20%，提升的效果明显

Db2advis建议创建索引（如何创建显示在以下“LIST OF RECOMMENDED INDEXES”），成本预计从3428降低到96，查询效率提升97.20%，

--

--

-- LIST OF RECOMMENDED INDEXES 

-- ===========================

-- index[1],   15.060MB                                  ----所需添加的索引所占的空间是15.06MB。

   CREATE INDEX "DB2INST1"."IDX1702101953330" ON "BASS2   "."STAT_ACT_REPEAT_ORDER"

   ("OP_TIME" ASC, "CHANNEL_CITY_ID" ASC, "OP_NAME" ASC,

   "OP_ID" ASC, "CHANNEL_TYPE3" ASC, "CHANNEL_TYPE2"

   ASC, "CHANNEL_TYPE1" ASC, "CHANNEL_NAME" ASC, "VALID_DATE"

   ASC, "PRODUCT_NO" ASC, "USER_ID" ASC, "COND_ID" ASC,

   "COND_NAME" ASC, "PROMO_ID" ASC, "PROMO_NAME" ASC,

   "CHANNEL_REGION_NAME" ASC, "CHANNEL_CITY_NAME" ASC)

   ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;

--

--

-- RECOMMENDED EXISTING INDEXES

-- ============================

--

--

-- UNUSED EXISTING INDEXES

-- ============================

-- ===========================

--

14 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.         

为避免全表扫描，db2advis建议添加一个索引，但是我们可以看到，添加的索引的关键字比较多，这样会占用不必要的空间，因为db2advis一般给出的建议也不能完全采纳，所以需要我们DBA来进一步分析怎样创建索引才能用最低成本实现最高效率。

以下为test2.sql中的sql语句

select op_time, channel_city_name, channel_region_name, promo_name, promo_id , cond_name , cond_id, user_id, product_no , valid_date , channel_name , channel_type1 , channel_type2 , channel_type3 , op_id , op_name 

from (select * from (  select  rownumber() over(order by channel_city_id asc) as row_,temp_.*

from (select  *  from bass2.stat_act_repeat_order a where 1=1  and a.op_time='2017-01-17' order by channel_city_id asc ) as temp_ )

as temp2_ where row_  between 0+1 and 15) a     

从以上语句大概分析了一下，主要搜索的关键字可能会集中在channel_city_id 和 op_time，所以我们建索引只包含这两个关键字段。经过一系列变更管控流程后，我们把索引添加上，再跑一次db2advis, 看一下结果如何：

bash-3.2$ db2advis -d bassdb -i test2.sql       

Using user id as default schema name. Use -n option to specify schema

    CALL SYSPROC.GET_DBSIZE_INFO failed, sqlcode = -443. Getting database size from the catalog tables.

  execution started at timestamp 2017-02-08-14.58.45.473590

found [1] SQL statements from the input file

Recommending indexes...

total disk space needed for initial set [   0.000] MB

total disk space constrained to         [2232773.544] MB

Trying variations of the solution set.

  0  indexes in current solution

 [ 76.0000] timerons  (without recommendations)    ----目前需消费的时间成本已经由之前的3428降低到76

 [ 76.0000] timerons  (with current solution) 

 [0.00%] improvement                            ----没有可以提升的

--

--

-- LIST OF RECOMMENDED INDEXES

-- ===========================

--  no indexes are recommended for this workload.

--

--

-- RECOMMENDED EXISTING INDEXES

-- ============================

-- RUNSTATS ON TABLE "BASS2   "."STAT_ACT_REPEAT_ORDER" FOR SAMPLED DETAILED INDEX "DB2INST1"."IDX1702110408560" ;

-- COMMIT WORK ;

--

--

-- UNUSED EXISTING INDEXES

-- ============================

-- ===========================

--

3 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.      

等一下，上面那个例子我们是不是漏了什么？在加了索引后没有更新统计数据！！！

现在跑一下runstats统计更新后的索引，我们再来看一下现在的db2advis 结果

bash-3.2$ db2advis -d bassdb -i test2.sql       

Using user id as default schema name. Use -n option to specify schema

    CALL SYSPROC.GET_DBSIZE_INFO failed, sqlcode = -443. Getting database size from the catalog tables.

  execution started at timestamp 2017-02-08-14.58.45.473590

found [1] SQL statements from the input file

Recommending indexes...

total disk space needed for initial set [   0.000] MB

total disk space constrained to         [2234220.950] MB

Trying variations of the solution set.

  0  indexes in current solution

 [ 31.0000] timerons  (without recommendations)  ----目前需消费的时间成本再由之前的76降低到31

 [ 31.0000] timerons  (with current solution) 

 [0.00%] improvement  ----没有可以提升的

--

--

-- LIST OF RECOMMENDED INDEXES

-- ===========================

--  no indexes are recommended for this workload.

--

--

-- RECOMMENDED EXISTING INDEXES

-- ============================

-- RUNSTATS ON TABLE "BASS2   "."STAT_ACT_REPEAT_ORDER" FOR SAMPLED DETAILED INDEX "DB2INST1"."IDX1702110408560" ;                         ----这里提示要更新索引的统计数据，其实刚才我们                       

已经执行过了，所以说db2advis有一些建议可

以自己再斟酌一下。

-- COMMIT WORK ;

--

--

-- UNUSED EXISTING INDEXES

-- ============================

-- ===========================

--

3 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.      

从以上的结果可以看出，我们选择的索引关键字是正确的，已经没有可以再提升的空间，并且在添加索引之后记得再次收集统计数据，才能获得更准确的评估值。

总结：

1.       db2advis提供的建议需根据实际情况再做修改，力求以最低的成本实现最高的查询性能；

2.       在执行db2advis之前确保所有涉及的表已经收集了统计数据，能提高提供的数据的准确率；

3.       添加了新的索引后，索引也需要收集统计数据，虽然不会对数据库的实际优化后的性能产生影响，但是会影响DBA对优化后的性能评估。