数据库性能分析与SQL优化

1.1、CPU瓶颈

在AIX系统中，可以使用vmstat监控CPU使用情况。

以1秒为时间间隔，连续收集3次性能数据，命令如下：vmstat 1 3

[db2ibits@h064074:/home/db2ibits]$vmstat 1 3

System configuration: lcpu=16 mem=32768MB ent=4.00

kthr       memory           page              cpu         

---------  ---------------------  -----------------------  -----------------------------------

 r  b   avm   fre      pi  po  fr   sr  us  sy  id  wa pc    ec

 1  0  4514677 808880  0   0   0   0   0  0  99  0  0.02  0.6

 1  0  4514677 808880  0   0   0   0   0  0  99  0  0.02  0.5

 1  0  4514677 808880  0   0   0   0   0  0  99  0  0.02  0.5

在上面的显示中，说明该系统有16颗逻辑CPU，内存为32G，授权CPU为4颗(LPAR管理程序会保证该LPAR有最少4颗CPU的运算能力)。

r：在运行队列中等待的进程数。如果该列高(一般数值在10以下)，则可以认为有很多的进程在请求执行，但无法得到CPU，可能存在CPU瓶颈。

b：等待队列，在等待IO的进程数。

最后6列分别代表的意义如下：

Us:用户进程使用的CPU时间(%)

Sy:系统进程使用的CPU时间(%)

Id:CPU空闲时间(%)

Wa:等待IO所消耗的CPU时间(%)。

Pc:当前LPAR所用到的实际CPU运算量

Ec:值等于pc/ent，当前LPAR的运算量所占百分比。

对于AIX系统，如果r数值超过10，说明CPU资源紧张；如果pc列高于ent，说名cpu资源紧张；如果us+sy保持在90%左右也说明cpu资源紧张。

如果CPU使用率较高，则可以通过如下的方法进一步定位并解决:

第一步：

使用ps命令获得进程的CPU使用情况，

ps -elf|sort +5 -rn|more

第6列为C列，用来表示当前进程所消耗的CPU，如果C列很高，说明该进程消耗大量的CPU资源，如果发现该进程是db2sysc，那说明db2进程消耗过多的CPU。

第二步：找到那些占用高CPU时间的DB2代理线程

第三步：查看代理线程对应的应用程序快照

db2pd -edus interval=10 top=5

在10秒内，消耗CPU前5的EDU

只关注EDU Name为db2agent(dbname)的EDU ID，EDU ID为11139，根据该EDU ID在db2pd –agents的结果中去筛选，找到AppHandl，命令为db2pd -agents|grep -i 11139，如下：

找到AppHandl后，就可以通过获取该应用程序的快照，查看该应用程序的执行细节，命令为：db2 get snapshot for application agentid 1309 > 1309_appSnap.txt

分析1309_appSnap.txt（详见附件），能看到正在执行语句：select * from db2obits.wf_task。

有时候edu堆栈信息很有用，如果想获取edu的堆栈信息，方式如下：

db2pd -edus interval=10 top=5 stacks

在10秒内，消耗CPU前5的EDU，并输出这些EDU的stack

或查看某个EDUID的堆栈：db2pd -stack eduid(db2pd -stack 11139)

或db2pd -stack 11139 dumpdir=/home/db2ixxxx/huangh/stack timeout=6 输出6秒的堆栈信息到指定目录。

1.2、IO瓶颈

在vmstat输出结果中，

Wa表示等待IO所消耗的CPU时间(%)。表示CPU因等待IO资源而被迫处于空闲状态，这时候的CPU并没有处于运算状态，而是被白白浪费了，等待IO应该越小越好。数值高表明存在IO瓶颈。

b：等待队列，在等待IO的进程数。数值高表明存在IO瓶颈。

也可以使用iostat 1 3来收集磁盘IO信息，参数1代表采样的时间间隔，单位为秒；参数3代表采样次数。tm_act指的是该驱动器占用率的百分比。

vmstat中wait列超过20%或iostat中某个磁盘的tm_act维持在80%左右，则说明IO存在瓶颈。

解决方法一：

1)、就找到引起IO瓶颈的表(一般是热大表)，即表扫描次数很多的大表

方法如下：使用表函数

例如要查找表空间TRX_DATA_DMS_16K中表扫描次数最多的表

select tabname,table_scans from table(mon_get_table(null,null,null)) where tabname in (SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACE='TRX_DATA_DMS_16K') order by table_scans desc

找到热表之后，假如为AP_CLONE_CUSTOM_TR表，继续通过如下查询查到引用该表的SQL语句：

select stmt_text from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as T where stmt_text like '%AP_CL%' order by total_act_time desc

然后通过查看这些SQL语句的访问计划，确定具体哪个语句引起了表扫描，最后根据实际情况看是否需要通过创建或调整索引、更新统计信息或者SQL语句编写方式等措施消除表扫描。

解决方法二：查看正在执行的SQL语句

select stmt_text,rows_read,rows_returned from sysibmadm.mon_current_sql order by elapsed_time_sec desc

案例一：根据表名找到使用的物理卷

有时候执行一条SQL，通过TOPAS会发现某个PV的使用率一直为99%，那么该怎么确定这种现象就跟执行该SQL有关呢。这就需要根据表名找到表使用的物理卷(PV)，如果这个PV正是繁忙程度一直为99%的PV，那可以肯定是由于该SQL引起的磁盘瓶颈。

下面详细描述根据表名找到表使用的物理卷的步骤：

(1)、假设表名为T1，根据表名T1，通过如下SQL查找该表使用的表空间

db2 "select tbspaceid,tbspace, index_tbspace,long_tbspace from syscat.tables where tabname='T1'"

得到

TBSPACEID  TBSPACE              INDEX_SPACE LONG_TBSPACE

---------- -------------------- ----------- ---------------------------------------------------

3          IBMDB2SAMPLEREL      -           -

可知使用的数据库表空间为IBMDB2SAMPLEREL，数据表空间ID为3。

(2)、根据TBSPACEID=3，通过如下命令查找该表空间使用的容器

db2 list tablespace containers for 3

得到

            Tablespace Containers for Tablespace 3

 Container ID = 0

 Name      = home/db2ibits/db2ibits/NODE0000/SAMPLE/T0000003/C0000000.LRG

 Type       = File

可知文件系统为/home/db2ibits

(3)、根据文件系统/home/db2ibits，通过如下命令查找对应的逻辑卷

df –g

得到

Filesystem        GB blocks    Free   %Used    Iused    %Iused   Mounted on

/dev/bitsdevlv02     25.00     16.91   33%     1201     1%      /home/db2ibits

可知逻辑卷为bitsdevlv02

(4)、根据逻辑卷bitsdevlv02，通过如下命令查找对应的PV

lslv -l bitsdevlv02

得到

PV                COPIES        IN BAND       DISTRIBUTION

hdisk3            200:000:000   8%            160:016:000:024:000

如果第(2)步得到的是一个裸设备：

db2 list tablespace containers for 3

得到

            Tablespace Containers for Tablespace 3

 Container ID = 0

 Name      = dev/rhqbbprelv06

 Type       = Disk

该裸设备对应的逻辑卷为hqbbprelv06，然后通过如下命令查找对应的PV

lslv -l hqbbprelv06

得到

PV                COPIES        IN BAND       DISTRIBUTION

hdisk6            400:000:000   39%            000:156:159:085:000

由此，就可以把表跟PV对应上了。

1.3、内存瓶颈

虚拟内存=物理内存+交换空间，如果操作系统的物理内存耗尽，那么系统就会把一些内存放入交换空间，这种操作会大大降低系统的性能。vmstat的第6列和第7列(pi和po(page out，从物理内存交换到交换空间))就是关于交换空间的IO指标，一般情况下，这两列应该永远为0，表示没有任何内存交换，如果发现持续的交换发生，那么需要检查当前剩余的内存。

avm指当前系统中已经激活的虚拟内存页的数量，该数值不包含文件系统缓存。

fre指系统中空闲页的数量，操作系统在内核中维护一个空闲页列表。每当应用程序结束时，所有的工作页面(包括应用堆栈、数据和其他的共享内存段)都会被立刻返回给空闲列表，而文件系统缓存则驻留内存，并不会返回给空闲列表，如果一个内存页被一个应用程序请求，那么最先分配的是其他已经结束的程序所占用的文件系统内存。

因此，fre并不能完全代表系统中可用的空闲内存，可以通过svmon –G查看系统可用内存到底有多少(linux中为free命令)，例如：

               size       inuse        free         pin     virtual   mmode

memory      2097152     2069132     28020      548234   2062331     Ded

pg space     2097152      110622

               work        pers        clnt       other

pin          455752          0         907       91575

in use      1959087           0      110045

在如上结果中，clnt与in use交叉的那一项，也就是110045，代表的就是有多少内存被文件系统使用。一般来说，这个值加上free列所对应的28020，就可以初步认为是该系统中可以被应用程序使用的内存，每个页面的单位为4096字节。

结论：如果po(page out，从物理内存交换到交换空间)不为0，说明物理内存资源紧张；交换空间使用率大于10%，说明物理内存资源紧张。如果物理内存资源紧张，需要确认是数据库参数设置不合理还是物理内存的限制，如果是数据库参数设置不合理，需要尽快调整内存使用分布；如果是物理内存的限制，需要通过扩展内存的方式解决。

1.4、懒惰系统

没有CPU瓶颈、IO瓶颈和内存瓶颈，但交易仍然非常慢的系统称为懒惰系统。懒惰系统的根源就是等待，等待包括：IO等待(IO瓶颈)、锁等待、latch等待。

latch是db2的内部锁，用来保证数据结构的并发性，防止并发地对数据结构进行修改，当大量数据操作需要对数据库的控制信息进行修改及读取时，会发生latch争用现象，部分线程处于等待状态。

Latch的监控方法：

1)、db2pd –latch

2)、表函数select * from table(mon_get_extended_latch_wait(null))

3)、db2pd –stack all 或db2pd –stack eduid

案例一：latch等待的例子，insert语句执行性能很差

有一个表，定义如下：

create table tabname(

c1 bigint not null generated by default as identity(

  start with +1

  increment by +1

  minvalue +1

  maxvalue +9223372036854775807

  cycle

  no cache

  order 

  ),

c2 varchar(10),

c3 varchar(10),

c4 varchar(10)

)

insert语句执行很慢，SQL快照信息如下：

平均每条语句执行使用3.36秒：

15482.427607/4597=3.36秒

平均每条语句逻辑读数量为18.5个逻辑页：

(12425+72854)/ 4597=18.5

CPU使用时间几乎可以忽略不计，物理读数量也很少。

从哪个方面看，也没有道理使得每条插入使用3秒以上。

选取一个正在该执行插入语句的应用程序，通过db2pd –stack eduid获取该应用程序对应EDU的堆栈信息，能够发现如下函数调用顺序：sqldSeqGenerateàsqldSeqGetàsqloXlatchConflict(或者直接看<LatchInformation/>标签中描述的等待latch信息)

sqldSeqGenerate是在生成一个sequence数值，然后sqldSeqGet是真正去得到一个数值，然后是一个latch冲突，从这里能够看出其他任务得到了这个Latch，该线程需要等待。Latch在这里是为了确保没有两个任务能够得到同一个sequence数值。

所以，问题发生在sequence不能足够快地为所有的任务分配下一个数值，把identity列的no cache属性调整为cache 50，性能问题得到解决。

2.1、数据库性能数据收集

收集CPU、IO、内存、数据库的性能数据，进行分析，脚本如下：

dbcheck.sh、Syschk_DB2_SQL_Snap_new.sh（详见附件）

比如：

--平均执行时间最长的10条语句

select total_exec_time/(num_executions+1) as avg_exec_time from sysibmadm.snapdyn_sql order by avg_exec_time desc fetch first 10 rows only

--平均排序次数最多的10条语句

select stmt_sorts/(num_executions+1) as avg_sorts,total_exec_time/(num_executions+1) as avg_exec_time,substr(stmt_text,1,1000) as sql_stmt from sysibmadm.snapdyn_sql order by avg_sorts desc fetch first 10 rows only

--平均排序时间最多的10条语句

select total_sort_time/(num_executions+1) as avg_sort_time,total_exec_time/(num_executions+1) as avg_exec_time,substr(stmt_text,1,1000) as sql_stmt from sysibmadm.snapdyn_sql order by avg_sort_time desc fetch first 10 rows only

#当前锁等待详细信息

db2pd -d $dbname -locks wait show locks > lockwait.txt

#当前latch信息

db2pd -latch > latch.txt

2.2、缓冲池命中率

来源：db2pd -d dbname -bufferpools

指标：优秀>95%，良好>80%

描述：从内存中访问数据仅需纳秒级，而从磁盘访问数据则需数毫秒。DB2对数据的获取是通过缓冲池，如果数据已经缓存到缓冲池，就可通过缓冲池直接获取，如果数据不在缓冲池，则需要从磁盘读到缓冲池。因此，命中率越高，代表着读取同样的数据时需要的 I/O 越少，性能就越好。

2.3、包缓存命中率

来源：数据库快照

公式：1-包缓存插入数量/包缓存读取数量(1–Package cache inserts Package cache lookups)

指标：1，或者能够长时间保持接近 1 的稳定数值

描述：该指标表示有多少查询语句可以直接在包缓存中找到。当一条查询被请求的时候，数据库在将其编译之前，首先要从包缓存中查找有没有已经被编译好的包可以直接运行。如果该包已经存在，那么该 SQL 可以直接被运行而不用再次编译。如果应用程序在运行一段时间后，绝大部分的语句都已经被缓存在包缓存中，那么可以节省很多 SQL 编译的时间与 CPU 消耗。

分析：

Package cache lookups=283055153

Package cache inserts= 823216

包缓存命中率=1-823216/283055153=99.7%

数据库参数PCKCACHESZ= AUTOMATIC(8222)

建议：

无

2.4、编目缓存命中率

来源：数据库快照

公式： 编目缓冲区插入次数/编目缓冲区查询次数(Catalog cache inserts/Catalog cache lookups)

指标：0，或者在接近 0 的数值上长时间维持稳定

描述：在系统的每一个分区中，CATALOGCACHE_SZ中都会分配出一块空间用于缓存编目表的信息。在数据库的日常操作中，查询编目表是一个非常频繁的操作。譬如当用户从一个表读取数据的时候，系统就要查询编目表，理解该表在什么表空间、应该如何访问等信息。因此为了性能着想，DB2 在数据库栈内存中单独开辟了一块空间，用于存储编目信息。

但是如果用户有很多数据库对象，而该编目缓存的大小过小，则该内存无法容纳下所有的

信息。那么当新的信息来临时，就会把一些不常用的信息替换出去。

如果该替换经常发生，那么每次当系统想要查找编目数据时，就要从编目表空间中查找，这样会导致系统性能一定程度上的下降。

因此，一般建议将 catalogcache_sz 设置逐渐增大，直到系统中不再频繁出现编目缓冲区插

入的操作。

分析：

Catalog cache lookups                     = 15709916

Catalog cache inserts                      = 3910

Catalog cache high water mark              = 1279630

数据库参数：

CATALOGCACHE_SZ= 300

db2mtrk –I –d –w结果：

catcacheh高水位为：1.6M

建议：

调整CATALOGCACHE_SZ参数值为500，语句为：

db2 update db cfg using CATALOGCACHE_SZ 500

不需要重启数据库

2.5、排序溢出率

来源：数据库快照

公式：排序溢出次数/排序总数(Sort overflows/Total sorts)

指标：OLTP：0，或者在接近 0 的数值上长时间维持稳定

描述：排序溢出就是当排序内存不够时，数据需要使用临时空间进行排序。一般来说，希望数据尽可能在内存中完成。当发现大量的排序溢出时，就要看排序堆内存参数设置是否足够大。

分析：

Sort overflows                             = 10448

Total sorts                                = 145116

指标值=10448/145116=7.19%

数据库参数：

SORTHEAP = 16834

建议：

调整SORTHEAP参数值为51200，语句为：

db2 update db cfg using SORTHEAP 51200

不需要重启数据库

2.6、脏页偷取

来源：数据库快照

公式：脏页偷取触发次数(Dirty page steal cleaner triggers)

指标：非常低

描述：脏页偷取是一种对性能影响极大的操作。当系统中的脏页偷取过多的时候，意味着需要让页清除器工作得更加卖力。

如果发现系统中的页清除器一直很空闲，则可以通过调节softmax 与 chngpgs_thresh 来让它们忙起来。这两个参数都是控制何时触发页清除器的参数，其中 softmax 是按照缓冲池中 MinbuffLSN 与当前 LSN 之间的差距来计算何时需要触发，而chngpgs_thresh 则是计算缓冲池中脏页的数量与可用页面总数来进行计算。两者的作用同样都是触发页清除器，只不过从不同的角度计算而已。

但是如果通过db2pd -stack all 抓取的stack 发现所有的页清除器一直非常繁忙，但是无论如何刷新磁盘的速度也赶不上数据写入缓冲区的速度，这时就需要增加页清除器的数量了。

分析：

Dirty page steal cleaner triggers          = 88831

LSN Gap cleaner triggers                = 837134

Dirty page threshold cleaner triggers      = 28238

数据库参数：

CHNGPGS_THRESH= 80

NUM_IOCLEANERS = AUTOMATIC(12)

SOFTMAX=520

建议：

NUM_IOCLEANERS = AUTOMATIC(12)，表示页清除器可以按需分配，数量充足。

Dirty page steal cleaner triggers= 88831，数值很高，表示页清除器工作的不充分，已经严重影响了数据库的缓冲池读性能，这种工作的不充分是受参数CHNGPGS_THRESH、SOFTMAX影响的，SOFTMAX超过阈值发生了837134，CHNGPGS_THRESH超过阈值发生了28238次，对比发现，有必要调整CHNGPGS_THRESH，由当前的80调整到50。语句为：

db2 update db cfg using CHNGPGS_THRESH 50

需要重启数据库

3.1、SQL编译过程

3.1.1、分析语法

编译器从语法层面分析SQL语句，如果检测到任何语法错误，就会停止处理，返回相应的错误提示给用户。经过语法分析后，SQL语句就被转换成内部表示形式，即QGM(Query Graph Model)，QGM用图结构来表示SQL语句，后续的编译过程将围绕QGM展开

3.1.2、检查语义

编译器从语义层面分析SQL语句，例如类型检查、参照完整性约束、触发器和检查约束等，语义分析的对象也加到QGM中。

3.1.3、重写查询

在不改变语义的基础上，对查询进行等价变换。例如，将谓词下推到基本表作为局部谓词；用MQT重写查询；将子查询转换成连接

3.1.4、下推分析

处理联邦查询独有的步骤，其主要任务是确定查询中的哪些操作可以放到远程数据源执行

创建联邦数据库：

具体详见附件1db2exfmt.txt

3.1.5、生成和优化访问计划

优化器分析QGM，根据表和索引上的统计信息，并参考当前数据库的各种参数配置，估计各种访问计划的成本，然后选择成本最小的计划作为最终的访问计划。

• 以SQL语句作为输入：优化器将重点考虑SQL中谓词的处理效率和表的连接方式

• 系统与数据库的设置：优化器会充分考虑CPU速度、存储的I/O速度、并行方式(NONE、分区内并行、分区间并行)、并发环境(AVG_APPLS=1)、优化级别(DFT_QUERYOPT=5)、缓冲池和排序堆大小等。

• 表和索引统计信息：统计信息是优化器进行SQL语句优化的基础。

• 生成最优访问计划：优化器充分考虑表访问方式、连接方法以及操作顺序，生成各种可能的访问计划，并根据成本模型计算每个访问计划的成本，最后选择一个总成本最小的访问计划。

3.1.6、远程SQL生成

处理联邦查询时独有的步骤，编译器会根据远程表的访问计划，生成用于在远程数据源执行的SQL语句。即联邦数据库执行select id,count(id) from test_view group by id，就会生成一条类似的SQL在源数据库执行：SELECT A0."ID" FROM "DB2IBITS"."TEST" A0 FOR READ ONLY。

3.1.7、生成可执行代码

编译器根据访问计划和QGM生成一个可由DB2引擎执行的二进制代码段(section)供DB2运行时使用。这个代码段不可读，其中包含的访问计划信息可以通过工具db2expln查看。

Package(程序包):程序包是一个存储过程、静态SQL语句或者UDF编译后生成的可执行二进制代码。当一个存储过程被编译时，每一条SQL编译成一个可执行的二进制代码段(section)，这些二进制代码段加上其他的一些必要信息组织成一个DB2程序包，并存储在DB2编目表中。当存储过程被调用时，DB2从编目表中读取相应的DB2程序包直接执行。产生的方案存储在syscat.packages和syscat.statements中。syscat.packages存储概要信息，syscat.statements存储具体的执行计划。比如syscat.packages指明该绑定的程序含有多少section(即多少条SQL语句，每条SQL语句产生一个SECTION)，所以如果syscat.packages的列TOTAL_SEC=3，则syscat.statements会有3条记录。

动态SQL编译后生成的可执行二进制代码段(即访问计划)存储在Package Cache内存中，每个SQL的编译结果大小在100KB左右。

流程图如下：

3.2、查看访问计划

db2exfmt:用于直接处理已收集并存储在解释表中的解释数据。

给定数据库名和其他限定信息，db2exfmt工具将在解释表中查询信息、格式化结果，并生成一份基于文本的报告，此报告可直接显示在终端上或写入ascii文件。

首先进入/home/db2ihqb/sqllib/misc，执行db2 -tvf EXPLAIN.DDL

产生EXPLAIN_INSTANCE等解释结果存储表，表创建在任意表空间中，但非SYSTOOLSPACE表空间。

创建解释表的另一种方式是调用下面的存储过程，其中第二个参数”C”的含义为创建。相应的，如果传入参数”D”即删除解释表。

解释表的删除与创建，是创建在表空间SYSTOOLSPACE下。

db2 "call sysproc.sysinstallobjects('EXPLAIN','D','',CURRENT SCHEMA)"

db2 "call sysproc.sysinstallobjects('EXPLAIN','C','',CURRENT SCHEMA)"

补充：

用户表空间SYSTOOLSPACE和用户临时表空间SYSTOOLSTEMPSPACE通常用于通过下列向导、实用程序或函数自动创建的表：

• 自动维护

• 设计顾问程序(两个都没有也不会报错)

• 控制中心数据库信息面板

• SYSINSTALLOBJECTS存储过程（如果未指定表空间输入参数的话）

• GET_DBSIZE_INFO存储过程(两个都没有也不会报错，执行的时候会自动创建SYSTOOLSPACE表空间)

手工创建：

Create regular tablespace systoolspace in IBMCATGROUP MANAGED BY SYSTEM USING (‘SYSTOOLSPACE’)

CREATE USER TEMPORARY TABLESPACE SYSTOOLSTEMPSAPCE IN IBMCATGROUP MANAGED BY SYSTEM USING (‘SYSTOOLSTEMPSAPCE’)

(1)    获取访问计划到解释表

A)、explain all语句

db2 "explain all with snapshot for select * from test"记录信息最全

db2 "explain all for snapshot for select * from test"只填充表的SNAPSHOT列，其余列都不填充

db2 "explain all for select * from test"不填充表的SNAPSHOT列，其余列都填充

EXPLAIN_INSTANCE表中的SNAPSHOT_TAKEN列（值为Y\N\0,Y表示有快照、N表示不带快照、0表示只有快照）表示每一条被解释的语句是否有一个Visual Explain快照。

B)、使用解释专用寄存器

DB2用两个解释专用寄存器来收集动态SQL语句的解释信息，这些专用寄存器是：

CURRENT EXPLAIN MODE 获取解释信息，不获取快照信息

CURRENT EXPLAIN SNAPSHOT  获取快照信息，不获取解释信息

下面的语句用来设置解释专用寄存器的值：

SET CURRENT EXPLAIN MODE option

SET CURRENT EXPLAIN SNAPSHOT option

解释寄存器的option(选项)有：

NO      不获取动态SQL语句的解释信息和快照信息

YES     当执行动态SQL语句时，获取解释信息或快照信息并返回结果

EXPLAIN 获取动态SQL语句的解释信息或快照信息，无需执行SQL语句。

一旦把寄存器设置成YES或EXPLAIN，将对后续的动态SQL语句进行解释，直到寄存器重置为NO为止。

下面两个选项只对CURRENT EXPLAIN MODE寄存器有效

RECOMMENT INDEXES

EVALUATE INDEXES

等同于db2advis，即获取解释信息的同时推荐索引和评估推荐索引的提升性能。

(2)    格式化二进制访问计划成ascii文本格式

获取的二进制访问计划存放在EXPLAIN_INSTANCE表中，可以使用db2exfmt格式化成ascii文本格式，有如下两种格式化方式：

db2exfmt -d sample -g tic -e DB2IHQB -n % -s % -w % -# 0 -o 1db2exfmt.txt

这种方式能格式化explain_instance中的所有访问计划

获取访问计划的简单有效方式：

db2exfmt -d sample -l –o 1db2exfmt.txt

这种方式只能格式化一条访问计划，默认格式化explain_instance中的最新访问计划，如果在交互的时候，对如下询问项输入了explain_instance中某一行这四列的值，那么就获取对应行的访问计划，而不是固定的只格式化最新访问计划。

Enter up to 26 character Explain timestamp (Default -1) ==>

Enter up to 128 character source name (SOURCE_NAME, Default %%) ==>

Enter source schema (SOURCE_SCHEMA, Default %%) ==>

Enter section number (0 for all, Default 0) ==>

(3)    获取动态SQL和静态SQL访问方案

A)、解释动态SQL

在解释SQL语句时使用snapshot选项，DB2将编译SQL语句时使用的统计信息也包含在访问计划的输出中，这样大大方便了对访问计划的分析。

db2 set current explain mode explain

db2 set current explain snapshot explain

db2 "select ..."

db2 set current explain mode no

db2 set current explain snapshot no

db2exfmt -d sample -l -o 1db2exfmt.txt

B)、解释存储过程中静态SQL语句的访问计划

要通过db2exfmt抓取存储过程的访问计划，需要在创建存储过程之前设置EXPLAIN YES这个编译选项。有两种方法来设置这个选项：

• 调用存储过程SET_ROUTINE_OPTS设置EXPLAIN YES编译选项，于是在当前会话中创建的SQL存储过程，DB2会将其访问计划写入到解释表中，从而可以用db2exfmt工具获取。在每次rebind的时候，该存储过程中的SQL语句会重新编译，其访问计划也将重新被写入到解释表；但如果rebind指定的项为REOPT ALWAYS，则rebind的时候不会生成访问计划，在执行call的时候才生成访问计划到解释表中。

db2 connect to sample

db2 "CALL SET_ROUTINE_OPTS('EXPLAIN YES')"

例子一：

--执行存储过程的rebind，每执行一次，都会生成新的访问计划存放到解释表中

db2 "call sysproc.rebind_routine_package('P','DB2IHQB.GETN','REOPT NONE')"

例子二：

--使用rebind选项REOPT ALWAYS执行存储过程的rebind

db2 "call sysproc.rebind_routine_package('P','DB2IHQB.GETN','REOPT ALWAYS')"

此时不会生成新的访问计划，只有在真正执行存储过程的时候(比如：db2 "call GETN('156',?,?)")才会生成访问计划并存放到解释表中。

• 设置注册变量DB2_SQLROUTINE_PREPOPTS，这需要重新启动DB2实例才能生效，随后创建的SQL存储过程将能用db2exfmt抓取访问计划。

db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN YES"

当发现存储过程访问计划不是最新时，可使用如下语句重新绑定这个存储过程：

-- any表示保留当前编译选项，只是利用最新统计信息重新编译

db2 "rebind package DB2IHQB.P1837332765 resolve any"

或

db2 "call sysproc.rebind_routine_package('P','DB2IHQB.GETN','ANY')"

在syscat.routines的PRECOMPILE_OPTIONS选项存放编译选项

例子三：

db2 "create table test(id int,name char(10))"

db2 "CALL SET_ROUTINE_OPTS('EXPLAIN YES') "

db2 -td@ -vf proc.sql

db2 "call sysproc.rebind_routine_package('P','HUANGH.proc_test','REOPT NONE')"

create procedure proc_test(v1 int)

begin

declare time int default 0;

while(time<v1)

do

insert into test values (1,'testlog');

set time=time+1;

end while;

end@

3.3、解读访问计划

db2exfmt输出信息概述

(1)    全局上下文信息：包括数据库配置、优化级别和隔离级别等

包括：

• DB2版本和生成访问计划的时间

• 数据库管理器和数据库的配置：并行方式、CPU速度和通信带宽、缓冲池、排序堆、AVG_APPLS(平均有多少用户连接这个数据库，根据这个参数分配可用的内存资源和锁资源)

• 语句级别上下文：优化级别、隔离级别、并行度(db2 SET CURRENT DEGREE \'4\')。优化级别可以为0、1、2、3、5、7、9，默认为5。优化级别越高，优化器需要考虑的访问计划也就越多，编译时间也就越长(set current quey optimization=5)。

(2)    SQL语句文本：包括原始SQL语句和优化后的SQL语句

(3)    访问计划：包含访问计划图和每个操作符的细节信息，访问计划中用到的对象以及统计信息，包括表、索引等。

用树型图表示访问计划，包含的节点分为操作节点和对象节点，其中非页节点为操作节点，叶子节点为对象节点。操作节点 描述每个操作的具体信息，从上到下依次为：返回的行数、操作符名称、编号、累计的成本和IO成本；对象节点包括基本表、索引、临时表和表函数等。

访问计划图中操作符的成本是累计的，是该操作符及其子树成本的总和。因此，在分析访问计划时，应该关注成本增长最大的操作符。

操作符描述如下表：

访问计划图后面紧接着是每个操作符的详细信息，包括操作符的成本明细、参数和谓词。需要特别注意谓词的类型和过滤因子。

(4)    访问计划中用到的表和索引等数据库对象

3.4、表连接

如果需要连接两个以上的表，优化器每次也最多只能连接两个表，并保存中间结果，再与其他表做两两连接，优化器在考虑两个表的连接操作时，主要考虑三种连接方法：嵌套循环连接、合并连接和HASH连接。

对于两两连接方法，连接的两个表一个为外表，另一个为内表，外表也称为驱动表，处理连接时，对于外表的每一行，都会去内表中查找是否有相应的匹配行，优化器根据所选连接方法的类型和成本决定哪个是外表、哪个是内表。

3.4.1、嵌套循环连接

适用于外表较小且内表较小或者内表上可以使用索引扫描的场景。对于不等谓词(即>、<、>=、<=和!=)的连接，只能使用嵌套循环连接。对于两个表的笛卡尔积(即没有连接谓词)，也只能使用嵌套循环连接，如select * from t1,t2。

在解释执行话中，左边的表为外表，右边的表为内表，如下图：

db2expln -d sample -q "select * from employee a,department b" -g -t -i|more

外表为：department

内表为：employee

如果通过db2exfmt来看优化后的SQL语句，排在前面的是外表，排在后面的是内表，如：

如上判断内外表的方法对HASH连接也是有效的。

对外表中符合条件的行进行扫描，对于在外部表中找到的每一行，将循环一遍内部表。所以，DB2优化器一般选择行数较小的表作为外部表。

如果内部表上连接列有索引，那嵌套循环连接会更有效，这是主要的。

如果外部表上连接列有索引，那嵌套循环连接也有效，因为可以使外部表有序，但是次要的。

所以，建议在连接列上，外部表和内部表都有索引，在索引不能创建太多的情况下，考虑对内部表的连接列建立索引。

3.4.2、合并连接

合并连接能处理多个连接等式谓词，内表和外表都需要有序，这可以用显式的SORT来保证，也可以是索引中本来就是有序的数据。

当连接表的连接列上没有可用索引时，通常使用该连接方法。

连接开始之前，如果有过滤条件，那么数据库服务器首先会应用它们，然后对连接列上每个表中的行进行排序。因为将所有的连接表排序，所以成本通常极高。

如：MSJOIN

select e.*,d.* from employee e,department d where e.workdept=d.deptname

3.4.3、HASH连接

当一个表或多个连接表上没有索引时，或者当数据库服务器必须从所有连接表中读取大量行时，就使用HASH连接。

HASH连接的连接谓词只能是等于谓词，可处理多个等于谓词，要求连接谓词两端的字段类型相同，且不能含有表达式。就CHAR类型而言，长度必须相同；就DECIMAL类型而言，精度和小数位必须相同。

DB2 V10增强了HASH连接的能力，能将含有表达式或者字段类型不匹配的连接谓词作为HASH连接的连接谓词，比如T1.C1=T2.C2+1，或者T1.C1_INT=T2.C2_DECIMAL等，不过从性能上考虑，应该避免使用复杂表达式或者类型不匹配的连接谓词。。

HASH连接通常比MSJOIN连接快，因为它没有涉及排序操作。

内表一般较小，用内表构建HASH表。对于外表的每一行，在这个HASH表中进行快速查找，

HASH连接的运行原理：

• 构建HASH表(build table)：首先扫描内表，根据连接谓词的字段计算HASH键(hash key)，构建一个HASH表(key为连接谓词，value过滤后的行的某些列)。HASH表被分为几个分区，如果内存中没有足够的空间容纳整个表，则有些分区将被写入磁盘上的临时表。构建HASH表时使用的内存池为SORTHEAP，如果SORTHEAP的内存不够，则会溢出到磁盘上，导致磁盘IO溢出。因此，为了提高HASH连接的性能，避免IO溢出，应该适当调整SORTHEAP的大小。

• 探测表(probe table)：扫描外表，对于外表的每一行，对连接列应用同一HASH算法计算HASH键，然后根据HASH键在HASH表中查找内表的匹配记录。如果HASH键对应的HASH表分区在内存中，则立即返回匹配连接的结果。如果该分区被写入到磁盘上的临时表中，则外表的行也被写入临时表。

如下为一个散列连接的例子：

db2expln -d sample -q "select * from employee a,department b where a.workdept=b.deptno" -g -t -i|more

3.5、统计信息

优化器依赖于准确的统计信息来进行基数估计，凭借基数估计来计算每个操作符的成本以及整个查询的成本，基于成本来评估候选的访问计划，因此，统计信息是优化器的导航仪。

DB2中的统计信息主要包括以下内容：

• 基本统计信息：包括表、列和索引的统计信息

• 分布统计信息：频率统计信息和分位数统计信息

• 列组统计信息：计算具有相关性的列组统计信息，为联合谓词的过滤因子更精确的估计

3.5.1、基本统计信息

db2 "runstats on table db2obits.c_acctday and indexes all"

只有基本统计信息的情况下，DB2优化器认为所有的数据都是均匀分布的，优化器对下面三类谓词的过滤因子计算公式如下所示：

• 局部等于谓词(如C1=7)

ff(Filter Factor)=1/colcard(C1)

• 范围谓词(如key1<C1<=key2)

ff=(key2-key1)/(high2key-low2key)(仅供参考)

• 连接谓词(如C1=C2)

ff=1/MAX(colcard(C1),colcard(C2))

对于单个表T上的谓词条件，相应的基数计算公式为：

card(T)*ff

对于两个表T1和T2上的连接谓词，相应的基数计算公式为：

card(T1)*card(T2)*ff

3.5.2、分布统计信息

列上某个值的数目比较多或者某区间上的数据比较多，使用均匀分布进行基数估计就不准确了，需要利用分布统计信息对基数估计进行修正。

DB2提供了两种分布统计信息：频率统计信息和分位数统计信息

db2 "runstats on table db2obits.c_acctday with distribution and indexes all"

如果SQL语句中的谓词使用的是参数，比如C1=?，则优化器用不上分布统计信息，只能使用基本统计信息来估计基数，但使用REOPT ALWAYS后，能够使用分布统计信息。

(1)    频率统计信息

runstats on table employee with distribution on columns (job num_freqvalues 10) and indexes all

频率统计信息记录出现频率最高的几个值以及出现次数。例如EMPLOYEE表上列JOB的频率统计信息如下所示：

对于查询select count(*) from employee where job='DESIGNER'，如果使用基本统计信息，表的基数为42，列JOB的基数为8，则基数估计为card*1/colcard(job)=42/8=5.25。如果使用频率统计信息，则能精确地得到基数为10

(2)    分位数统计信息

分位数统计信息实际上就是统计数据的区间分布。例如列C1包含下面的值，其数据分布区间不是均匀的：

分位数统计信息如下所示，数据按从小到大排列后，处于第一个位置的为0.0，第3个位置的为6.3等。

用基本统计信息和分位数统计信息分别计算下面两个谓词的基数：

• C1<=8.5：表上共有7条记录满足这个谓词

使用基本统计信息，基数估计为：(key2-key1)/(HIGH2KEY-LOW2KEY)*Card=(8.5-5.1)/(93.6-5.1)*10=0.38，存在很大偏差。而使用分位数统计信息，由于第7个位置的值为8.5，直接得到基数为7，与实际情况完全一致。

• C1<=10：表上共有8条记录满足这个谓词

使用基本统计信息，基数估计为：(key2-key1)/(HIGH2KEY-LOW2KEY)*Card=(10-5.1)/(93.6-5.1)*10=0.55，存在很大偏差。而使用分位数统计信息，需要将这个谓词作为两部分来进行估算：C1<=8.5的基数为7，而C1>8.5 AND C1<=10的基数估计为(10-8.5)/(100-8.5)*(10-7)=0.05，因此，谓词C1<=10估计为7.05，比使用基本统计信息要精确得多。

3.5.3、列组统计信息

如果SQL语句中有两个或多个谓词，优化器在估计基数时假定这两个谓词是独立的。但是，在某些情况下，这两个谓词或者多个谓词并不独立，也就是说这些列上的数据具有相关性，这时，优化器估计的基数就会存在偏差。

假如：EMPLOYEE表上共有42条记录，满足谓词JOB=’ANALYST’的数据行数为3，而满足WORKDEPT=’C01’的行数为4。在收集了分布统计信息后，优化器能精确地估计这两个谓词的过滤因子，分别为3/42=0.0714286和4/42=0.0952381。

对于查询select * from employee where job='ANALYST' and workdept='C01'，优化器会假定这两个谓词是独立的，于是估计基数为Card*(3/42)*(4/42)=0.28571。其访问计划如下所示：

但是，这个查询实际返回行数为3。这里的基数估计偏差正是由于workdept和job的数据具有相关性导致的。这时可以在workdept和job上收集列组统计信息，修正优化器的基数估计。

runstats on table employee on all columns and columns((workdept,job)) with distribution and indexes all

这样，再查看SQL语句的访问计划，如下所示，估计的基数为2.8，与实际行数非常接近。

当多个列的数据具有相关性时，列组统计信息不仅能优化上述局部谓词的基数估计，也能对多个列连接谓词的基数进行更精确的估计。

3.5.4、自动runstats

设置AUTO_MAINT、AUTO_TBL_MAINT、AUTO_RUNSTATS为ON

DB2针对每张表维护一个UDI计数器，UDI计数器记录该表上发生update、delete、insert的记录数，该表每执行一次runstats，UDI计数器就被重置为0。所以，自从上次收集统计信息以来表有多少数据发生改变， 是判断表是否需要runstats的重要依据。

UDI计数器通过如下命令查看，

启用自动收集统计信息后，第一次runstats评估发生在数据库激活后的两个小时，之后每两个小时评估一次是否需要执行新的runstats。

(1)    检查表是否被访问

(2)    检查表是否有统计信息，如果没有统计信息，执行runstats

(3)    检查UDI是否大于10%

(4)    检查UDI是否大于50%，如果大于，执行runstats

(5)    检查表是否属于本次需要评估runstats的表，如果不是，直接跳过。对每个表，一个内部表记录该表历史统计信息改变的多少，如果检测到表的统计信息在一段时间内发生了很多的改变，那么该表被评估的频率将加快(最快两小时一次)；相反地，如果一个表统计信息没有发生变化，该表被评估的频率将减少(最慢可能几个月都不评估)。

(6)    如果表数据页不多，属于小表，就runstats

(7)    如果表数据页大于4000页，就抽样部分数据并收集该部分数据的统计信息，与当前统计信息比较，如果差异很大，就收集该表的统计信息。

流程图如下：

表第一次统计信息基于表大小决定是否抽样生成统计信息，之后每次收集统计信息是使用

SELECT SUBSTR(STATISTICS_PROFILE, 1, 150) AS STATISTICS_PROFILE FROM SYSCAT.TABLES里面的统计概要文件，如果没有概要文件，那就用如下语句收集统计信息：

runstats on table db2obits.tabname with distriution and sampled detailed indexes all;

使用7%的系统资源

不对VOLATILE表收集统计信息

检查健康快照，看自动收集统计信息过程中，哪些表出现了错误(统计信息不一致(表与索引不一致)、锁超时(5秒钟)、STAT_HEAP_SZ对大小不够(建议15000个页面))；或者没有启用自动收集统计信息，查看哪些表需要runstats。

db2 get health snapshot for db on sample WITH FULL COLLECTION

关注db.tb_runstats_req指示器。

默认情况下，健康监视器和db.tb_runstats_req都是启用的。可通过如下方法检查：

db2 get dbm cfg|findstr i heal

 监视实例和数据库的运行状况                 (HEALTH_MON) = OFF

通过如下命令启用

db2 update dbm cfg using HEALTH_MON ON

查看db.tb_runstats_req是否启用

如果没有启用，使用如下命令启用db.tb_runstats_req

UPDATE ALERT CONFIG FOR DATABASE ON sample USING db.tb_runstats_req SET THRESHOLDSCHECKED YES

DB2 V8.2的自动收集统计信息日志会打印在db2diag.log中，不管DIAGLEVEL的值。DB2 V9以后，必须设置DIAGLEVEL=4，该日志信息才会出现在db2diag.log中。

手动收集runstats时，目标表不允许drop或alter，但自动收集runstats时，如果发出drop或alter操作，会强制断开自动收集runstats的连接操作，允许drop或alter执行。

3.5.5、联邦表统计信息

db2 "call sysproc.nnstat('HIS','BDPOUSER','CAP_BTP_HIS',NULL,NULL,0,'/tmp/1.txt',?)"

参数分别为：SERVER、SCHEMA、NICKNAME、COLNAMES、INDEXNAMES、METHOD、LOG_FILE_PATH、OUT_TRACE

其中/tmp/1.txt是日志信息

如果要收集HIS下的所有昵称信息，命令为：

db2 "call sysproc.nnstat('HIS','','',NULL,NULL,0,'/tmp/1.txt',?)"

如果源表表结构发生了变化，或者新增加了索引等，就最好进行一次runstats，然后重建昵称，这样才能把表结构信息和索引信息及统计信息反映在联邦表，如果要重建昵称，登录的时候最好把用户和密码带上，例如：

db2 connect to bdpo user bdpouser using bdpouser

3.6、SQL谓词

谓词包括在以 WHERE 或 HAVING 开头的子句中，用于缩小查询返回的结果集，可以将谓词分为四类，按性能最佳到最差排列：

(1)    范围定界谓词：用于限制索引扫描的范围，提供了索引扫描的起始位置

(2)    索引控制(Index-Sargable)谓词：Sargable (searchable argument)，谓词中的字段是索引的一部分，可以从索引中估计出来。会减少访问数据页的数目，但是并不能减少索引页的访问量。这类谓词一般为=、>、<等谓词。

(3)    数据控制(Data-Sargable)谓词：索引管理器无法起作用，但是数据管理服务可以进行条件过滤，通常这些谓词需要访问基表。这类谓词一般为<>、 LIKE等。

(4)    保留 (residual)谓词：所需的IO非常大，已经超过了对基表的访问成本。这类谓词一般为：相关子查询(NOT EXISTS)、无关子查询(ANY、ALL、SOME)或者读取LONG VARCHAR、LOB等数据。

谓词类型的特征

3.6.1、范围定界谓词和索引控制谓词

范围定界谓词用于限制索引扫描范围。它们提供索引搜索的开始和停止键值。索引控制谓词无法限制搜索范围，但可根据索引进行求值，该谓词中引用的列是索引键的组成部分。

例如：在EMPLOYEE上创建如下索引：

create index em_i2 on employee(firstnme asc,workdept asc,job asc,salary asc,hiredate asc) allow reverse scans collect sampled detailed statistics

执行如下查询：

select * from employee where firstnme = 'CHRISTINE' and workdept = 'A00' and hiredate > '1963-08-23'

前两个谓词（firstnme = 'CHRISTINE' and workdept = 'A00'）是范围定界谓词，而 hiredate > '1963-08-23'是索引 SARGable 谓词。

优化器对这些谓词进行求值时，将使用索引数据，而不是读取基本表。索引控制谓词可减少需要从表中读取的行数，但需要从第一个索引叶子页读到最后一个索引叶子页，一个不落的进行判断，记录下符合条件RID。

3.6.2、数据控制谓词

无法由索引管理器求值但可以由数据管理服务（DMS）求值的谓词称为数据控制谓词。这些谓词通常需要访问表中的各行。

例如，PROJECT 表有索引：

INDEX PK_PROJECT：PROJNO ASC

在以下查询中，deptno = 'D11' 被视为数据 SARGable 谓词。

select projno, projname, respemp     from project     where deptno = 'D11'     order by projno

3.6.3、保留谓词(residual)

这种谓词由关系数据服务（RDS）进行求值，在I/O 成本方面，保留谓词比表扫描更高。如下场景会出现保留谓词的使用：

1)、部分相关子查询，如NOT EXISTS

2)、部分无关子查询，如 ANY、ALL、SOME

3)、读取 LONG VARCHAR 或 LOB 数据

比如：表EMP_RESUME的字段RESUME是CLOB类型，执行如下查询：

SELECT * FROM EMP_RESUME WHERE RESUME LIKE '%September 15%'

3.7、访问路径

访问表的方式称为访问路径(Access Path)，访问路径分索引扫描和表扫描，一般来讲，索引扫描比表扫描快。具体来说，有如下几种：

• 表扫描：TBSCAN

• 索引扫描：IXSCAN+FETCH

• 索引列表预取：IXSCAN+List Prefetch

• 索引与操作：IXSCAN+IXAND，能利用多个索引进行与操作

• 索引或操作：IXSCAN+SORT+RIDSCAN，能利用多个索引并进行或操作

3.7.1、表扫描

3.7.2、索引扫描

3.7.3、索引列表预取

先通过索引扫描得到满足谓词条件的所有RID，然后将这些RID进行排序，再用它们访问数据页。这样可以对相邻的数据行进行连续的访问，从而通过预取减少IO次数。与随机IO相比，这种方式的开销要小得多，因为如果缓冲池没法放下所有的页，随机访问可能导致一个页面从磁盘中读入多次。

下面是List Prefetch的例子：访问计划中的SORT操作用于将索引扫描得出的RID进行排序，然后再用预取的方式读取基本表的数据。预取能够减少磁盘IO次数，从而提高数据访问的性能。

3.7.4、索引AND运算

当SQL语句在同一个表上有多个谓词，并可以使用多个索引扫描时，DB2使用IXAND操作符来执行索引与操作。索引与操作将多个索引得到的RID列表合并，只取共同存在的RID。

create index em_i4 on employee(salary asc) allow reverse scans collect detailed statistics

create index em_i5 on employee(comm asc) allow reverse scans collect detailed statistics

执行如下查询：

select * from employee where  salary between 20000 and 30000 and   comm between 1000 and 3000

访问计划如下，IXAND节点将两个IXSCAN连接起来，而在IXAND上采取了List Prefetch。

参考：单列索引VS复合索引

大多数情况下，组合索引比单字段索引用途广。从性能上考虑：

(1)单列索引性能要比复合索引差。因为多个单列索引作为谓词，需要参与动态位图AND运算，在如上示例中，扫描索引EM_I5(相当于构建表)将生成满足comm between 1000 and 3000谓词的位图，扫描EM_I4(相当于探测表)并探测EM_I5(employee(comm asc))的位图将生成满足这两个谓词的合格RID的列表，为了提升动态位图AND的性能，可能需要增加SORTHEAP的大小。这与HSJOIN的原理一致(内表作为构建表存储在sortheap中，外表作为探测表)

(2)复合索引能够对谓词相关性收集列组统计信息，避免看成独立谓词错误评估基数

一般而言，应该优先设计组合索引

如果索引都是单列，那么统计信息可能需要考虑列相关性。

(3)访问计划对比来看，复合索引比单列索引更有利于查询

(4)复合索引占用较多磁盘空间，因为复合索引的影响DML操作性能会有所降低。

总体而言，创建索引优先考虑复合索引。

3.7.5、索引OR运算

如果SQL语句中的多个谓词是OR组合在一起，并同时可以使用多个索引。这时，DB2通过Index Oring，将多个索引扫描得到的RID进行合并，并将重复的RID去掉。索引或操作没有显示的操作符。

employee表已经存在如下索引：

INDEX XEMP2: WORKDEPT ASC

create index em_i2 on employee(job asc,hiredate asc) allow reverse scans collect sampled detailed statistics

select * from employee where workdept = 'A00' or (job = 'MANAGER' and HIREDATE >= '2003-10-10')

访问计划如下，使用了两个索引扫描，并且对每个索引扫描得到的RID进行排序，然后对两个RID列表上直接做RIDSCAN进行RID的合并操作，最后从基本表上提取数据。

4.1、SQL优化步骤

4.1.1、通过监控发现存在性能问题的关键SQL

以下所有数据都来自PCKCACHESZ内存

• 需要打开快照监控开关

--命令行

db2 get snapshot for dynamic sql on sample

--动态性能视图，显示时间分布信息从少到多

SELECT * FROM SYSIBMADM.LONG_RUNNING_SQL;

SELECT * FROM SYSIBMADM.TOP_DYNAMIC_SQL;

SELECT * FROM SYSIBMADM.SNAPDYN_SQL;

• 不需要打开快照监控开关

--表函数，直接获取PCKCACHESZ内存中的SQL信息

select * from TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T

4.1.2、在测试环境重现SQL执行慢的场景

在测试环境重现SQL执行慢的场景，并保存该场景(一般是数据库备份与恢复的方式进行保存与复原)，以备反复使用该场景。如果不能重现场景，通常的原因是生产环境与测试环境表结构不一致(包括使用的表空间和缓冲池)导致的，如果表结构比较后也难做到一致，那就获取生产环境镜像恢复到测试库进行场景重现。这一步很重要。

4.1.3、生成基准信息

重现SQL慢的场景后，执行db2exfmt获取带表统计信息的访问计划，执行db2batch获取SQL真实执行时间及应用程序快照，执行db2advis获取推荐的索引。这些都是基准信息，方便优化过程中及优化后与这些信息进行比较，一步一步的实施优化，达到优化目标，如果失去基准信息，优化文档难编写，优化取得的效果也缺少根据。

4.1.4、对表执行reorg和runstats

使用reorg和runstats获取最新统计信息，这是优化的起点，确保了数据及索引不存在碎片并且统计信息是最新的，通常而言，这步优化效果不大，但意义重大，确保了访问计划的正确性。reorg和runstats语句类似如下：

db2 "reorg table db2ihqb.EMPLOYEE"

db2 "runstats on table db2ihqb.EMPLOYEE with distribution and sampled detailed indexes all"

这是第一轮优化。

执行db2exfmt、db2batch、db2advis获取第一轮优化后的信息。

4.1.5、评估db2advis推荐的索引

如果第(4)步推荐的索引总体能提高20%以上的性能，每个索引大小不超过500M，对部分复合索引包含列太多情况进行微调(减少组合列个数)，并创建这些索引后表包含的索引个数不超过5个(OLTP数据库)。那么可以创建这些推荐的或微调后的索引。

这是第二轮优化。

执行db2exfmt、db2batch、db2advis获取第二轮优化后的信息。

4.1.6、使用db2batch查看SQL执行时间分布

如果使用推荐的索引后执行时间仍不达标，或根本没有推荐索引，那就查看db2batch中的应用程序快照来确定SQL执行时间的分布。得到SQL执行时间分布后，大多数情况下就能估计出哪些参数不当影响了该SQL的执行，比如：数据库参数、统计信息、缓冲池、排序堆等的影响，然后对这些参数进行调整。

这是第三轮优化。

执行db2exfmt、db2batch、db2advis获取第三轮优化后的信息。

4.1.7、访问计划调整与定制

查看db2exfmt生成的访问计划中的明显可调优点(表扫描、表连接、内表外表的选择)，如果期望使用某个索引，但因为编译器的原因，特殊的情况下总使用不到，那么就使用优化概要来定制访问计划。

如果找不到明显可调优点，那么就获取真实基数，并通过db2exfmt重新打印在访问计划的每个节点上，看哪个节点存在基数明显误估，那么就针对性的收集该表上某些列组的统计信息提高基数估计的准确率。

如果基数估计也正确，产生基数大的原因是谓词不能下推到基本表，导致中间结果集很大，那么就改写该SQL，此种现象一般存在于SQL非常长，并且子查询非常多的情况。改写的方式有多种，典型的一种是使用WITH TEMP，例如：

WITH TEMP(C1,C2) AS (values (6,66),(7,77),(8,NULL)) SELECT * FROM TEMP ORDER BY 2 DESC

这是第四轮优化。

执行db2exfmt、db2batch、db2advis获取第四轮优化后的信息。

如上步骤实施完毕，一般都能把SQL优化到期望的效果。

如果仍没有达到期望效果，那就要考虑启用表压缩(一般情况下建议开启，默认是未开启状态)、创建MQT(物化查询表)(通常使用在OLAP数据库环境)、MDC(多维集群表) (通常使用在OLAP数据库环境)、表分区、找出热点磁盘、重新规划表空间使用容器等方式进行优化。

这些都不行，就需要从业务层面考虑，从业务层面简化需求，使SQL变的简单。

接下来分别讲解如下SQL优化工具：

db2adivs

db2batch

db2exfmt

真实基数

访问概要定制

4.2、SQL优化工具

4.2.1、db2advis

db2advis -d sample -i 1.sql -n db2ihqb -q db2ihqb -m MICP –o 1db2advis.idx>1db2advis.txt

1.sql内容如下：

--#SET FREQUENCY 100

SELECT COUNT(*) FROM EMPLOYEE;

SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS';

--#SET FREQUENCY 100为设置语句的执行频率

其中

-d sample 标识数据库的名称

-i 1.sql标识 db2advis 的输入文件的名称

-n db2ihqb指定推荐创建对象的模式为db2ihqb

-q db2ihqb指定1.sql中SQL的表模式为db2ihqb

-m MICP 是 db2advis 命令的伪指令，用于生成以下提高性能的建议：

M 新的物化查询表

I 新的索引

C 将标准表转换为多维集群表 (MDC)

P 对现有表重新分区

默认为I。

这里只推荐索引，所以命令应该为：

db2advis -d sample -i 1.sql –n db2ihqb -q db2ihqb -m I -o 1db2advis.idx>1db2advis.txt

或

db2advis -d sample -i 1.sql –n db2ihqb -q db2ihqb -o 1db2advis.idx> 1db2advis.txt

4.2.2、db2batch

通过如下db2batch语句获取结果：

db2batch -d sample -f 1.sql -o p 5 f -1 r -1 -iso ur -car cc -i complete -r 1db2batch.txt

默认的iso为RR，car的选项有：cc  - Currently Commited、wfo - Wait for Outcome

通过db2batch获得了某SQL的执行时间，如下：

Prepare Time is:           0.286      seconds 

Execute Time is:           126.595    seconds 

Fetch Time is:             8.979      seconds 

Elapsed Time is:           135.860    seconds

从获取的应用程序快照中得到如下信息：

Total buffer pool read time (ms)             = 21313

Total buffer pool write time (ms)            = 18

Time waited for prefetch (ms)                = 75949

Total User CPU time used by agent (s)        = 27.761703 seconds

Total System CPU time used by agent (s)      = 1.532227 seconds

这是SQL执行时间分布，这5部分相加，值为：126.573

SQL执行时间为Execute Time is:           126.595    seconds 

两者是吻合的。

然后从应用程序快照中还能得到如下信息：

Total sort time (ms)                         = 10076

排序时间10076包括排序活动时间+排序等待的时间，而排序活动时间是用的纯CPU时间，排序等待时间一般都是等待IO，所以10076这个时间已经在Total User CPU和Total buffer pool read time已经包括了，故可以不考虑。

从时间来看，消耗时间的大头为Time waited for prefetch，如下：

Time waited for prefetch (ms)                = 75949

TIME WAITED FOR PREFETCH : 等待预取时间 ,这段时间包含在SQL执行时间中
表示：应用程序等待 I/O 服务器（预取程序）完成将页装入到缓冲池中的操作所耗用的时间 。
如果snapshot中此值较大，建议适当调整 NUM_IOSERVERS，DB2_PARALLEL_IO

4.2.3、db2exfmt

db2exfmt:用于直接处理已收集并存储在解释表中的解释数据。

4.2.4、真实基数

使用Section Actuals分析执行计划

(1)   启用Section Actuals

db2 update db cfg using SECTION_ACTUALS base

创建工作量管理器和activities事件监视器

(2)    创建工作量管理器和事件监视器

必须为真实段创建一个workload和事件监视器。可以使用下列命令创建一个workload，也可以使用默认的的workload管理器。

db2 "create workload MYWORKLOAD current client_acctng('MYWORKLOAD') service class sysdefaultuserclass collect activity data on all database partitions with details,section"

db2 "grant usage on workload MYWORKLOAD to public"

db2 "create event monitor MYMON for activities write to table"

(3)    收集相关语句的Section Actuals

db2 "delete from ACTIVITYSTMT_MYMON"

db2 "call wlm_set_client_info(null,null,null,'MYWORKLOAD',null)"

db2 "set event monitor MYMON state 1"

采用db2 set current explain mode yes来执行语句(如果语句不实际执行，是不能被activities事件监视器捕获的，所以这里要用yes，不能用explain。)

执行类似如下的语句

db2 "select * from db2obits.wf_task where nodeid='ManuNode_REG'"

db2 set current explain mode no

db2 "set event monitor MYMON state 0"

db2 "call wlm_set_client_info(null,null,null,null,null)"

(4)    确定数据的Application、UOW和Activity ID

db2 " select appl_id,uow_id,activity_id,varchar(stmt_text,500) as stmt from ACTIVITYSTMT_MYMON"

(5)    将数据输入到EXPLAIN表中

调用explain_from_activity过程来将有关语句输入到EXPLAIN表中。首先是3个输入Application、UOW_ID和Activity_ID，它们是根据步骤4输出中的有关语句来确定的。第4个输入是步骤3中设置的活动事件监视器的标识符，第5个输入是EXPLAIN表的模式名称，在这里，EXPLAIN表的模式是DB2IBITS。

db2 "call explain_from_activity('*LOCAL.DB2IBITS.140228014048',28,1,'MYMON','DB2IBITS',?,?,?,?,?)"

运行db2exfmt获取执行计划

db2exfmt -d sample -l -o db2exfmt.txt

结果类似于：

4.2.5、优化概要

启用优化概要前必须设置DB2_OPTPROFILE注册表变量为YES并重启DB2服务器。

n  嵌入方式

将优化概要嵌入在SQL语句中，如果需要在SQL调优的过程中对单条SQL语句尝试各种不同的访问计划，那么这种方式就很适合。

例如：如下所示的SQL语句使用了嵌入式优化概要，它对优化器生成访问计划作出了这样的指示：访问employee表使用索引扫描，由优化器根据成本模式为其选择合适的索引。

db2 "select * from db2ihqb.employee c

/* <OPTGUIDELINES><IXSCAN TABLE='c'/></OPTGUIDELINES> */"

用db2exfmt工具打印出这个SQL语句的访问计划，如下：

参考1：定制访问计划

Q9表作为外表，嵌套循环连接Q8表，得到的结果集作为外表，Q9表是使用表扫描还是索引扫描由优化器决定，Q8表使用索引扫描，并且指定使用PERIOD_UIDX索引。表prd是HSJOIN的内表，表prd使用表扫描。每执行该SQL都重新生成访问计划，即使包缓存中存在该访问计划。

/*<OPTGUIDELINES>

<HSJOIN>

  <NLJOIN>

    <ACCESS TABID=’Q9’/>

<IXSACN TABLE=' Q8' INDEX='PERIOD_UIDX'/>

</NLJOIN>

<TBSCAN TABLE='prd'/>

</HSJOIN>

<REOPT VALUE='ALWAYS'/>

</OPTGUIDELINES>*/

注意：优化概要中的表名，如果在SQL语句中对表名使用了别名，那么在优化概要中也必须使用别名，忽略大小写。

n  独立方式

将优化概要嵌入到SQL语句中，在实际项目中并不是一个好的方式，因为数据是变化的，在某一时刻使用这个访问计划是最优的，在另一个时刻，却可能很差，这时再来修改应用中的SQL语句的优化概要，很不方便。独立方式可以解决这个问题，它用独立的文件存储SQL语句和相应的优化概要，并注册到DB2中。这样，当需要改变访问计划时，不用修改SQL语句本身，只要修改独立文件的内容就行了。

下面介绍使用独立方式的四个步骤。

(1)创建OPT_PROFILE表

通过SYSINSTALLOBJECTS存储过程或者使用CREATE TABLE语句手工创建表SYSTOOLS.OPT_PROFILE，该表存储优化概要文件。

db2 "call sysinstallobjects('opt_profiles','c','','' )"

(2)创建OPT_PROFILE文件

优化概要文件是一个标准的XML文件。如下所示，名为Prof1.xml的优化概要文件中，根节点为OPTPROFILE，其下可以有多个STMTPROFILE，每个STMTPROFILE指定一条SQL语句的优化概要。STMTPROFILE主要由两部分组成，STMTKEY包含SQL语句，而OPTGUIDELINES指定相应的优化概要，在STMTKEY中可以用属性SCHEMA指定SQL语句所引用表的默认模式，比如例子中指定默认模式为DB2IHQB。

<?xml version="1.0" encoding="UTF-8"?>

<OPTPROFILE VERSION="10.1.0.3">

<STMTPROFILE ID="Query 0">

  <STMTKEY SCHEMA="DB2IHQB">

    <![CDATA[select * from employee c]]>

  </STMTKEY>

  <OPTGUIDELINES>

    <IXSCAN TABLE="C"/>

  </OPTGUIDELINES>

</STMTPROFILE>

</OPTPROFILE>

注意：优化概要文件的SQL语句匹配能忽略大小写和空格，但其他方面必须一致。在匹配之前，DB2将去除冗余空格和控制字符。

(3)注册优化概要

注册优化概要，即将优化概要文件存储到第一步创建的SYSTOOLS.OPT_PROFILE表中。首先编辑如下内容，并保存到profileData.del中。这里的内容与表SYSTOOLS.OPT_PROFILE结构一致，第一个值指定优化概要的模式DB2IHQB，第二个值指定优化概要的名字PROF1，第三个值指定优化概要文件的名字，示例中为Prof1.xml。

--文件profileData.del

"DB2IHQB","PROF1","Prof1.xml"

然后使用下面的IMPORT命令将profileData.del中的内容导入到表systools.opt_profile中，这样就完成了这个优化概要的注册，所使用的命令如下所示：

db2 "import from profileData.del of del modified by lobsinfile insert_update into systools.opt_profile"

注册概要文件后，使用下面的flush命令刷新缓存中的优化概要

flush optimization profile cache

如果从这个表中更新或删除一个指南，操作完后，也需发出flush optimization profile cache语句更新缓存，使之生效可以被使用。

(4)使用优化概要

最后，使用set current optimization profile命令设置当前要使用的优化概要。优化概要由systools.opt_profile中schema和name组合主键标识，示例中为DB2IHQB.PROF1，于是接下来的查询就能用到这个优化概要：

db2 flush optimization profile cache all

db2 set current optimization profile DB2IHQB.PROF1

db2 set current schema db2ihqb

db2 set current explain mode explain

db2 "select * from c_employee c"

db2 set current explain mode no

db2exfmt -d sample -l -o db2exfmt.txt

查看db2exfmt.txt，结果如下：

如果在应用程序中加载优化概要文件，代码实现如下：

stmt.execute("flush optimization profile cache all ");

stmt.execute("set current optimization profile DB2IHQB.PROF1");

参考1：强制表employee的访问使用索引和MQT，而且MQT对多个STMTPROFILE生效

案例一：IO瓶颈

理财批量凌晨6点执行到早上8点还未结束，一条SQL执行时间超过90分钟，执行多次，有3次每次执行大概90分钟，执行过程中发现IO超过80%，经分析访问计划，发现是统计信息不准，表实际有40万条记录，统计信息显示为3万条记录，访问计划因此生成了以该表作为外表，执行嵌套循环连接。

案例二：懒惰系统

星期六晚上投产的时候进行数据清理，保留最近一年内的数据，导出该数据后，renmae原表为BAK表，新建该表然后执行import insert 操作，到星期一中午11:00业务反馈无法进行放款操作，经检查CPU正常、IO正常、大部分交易正常，类似懒惰系统，因此考虑出了锁等待，使用db2pd –d dbname –wlocks，发现很多应用程序在等待锁。

后续是force该import操作，rename BAK为原表解决问题

在默认情况下，import 会在目标表加X 锁，不允许其他应用访问(allow no access)。如果import 允许其他应用读和写，可指定allow write access 选项，这时import 会在目标表上加IX 锁，但该选项只能用于insert 或insert_update 操作。

案例三：WAS线程使用超过90%

检查sysibamadm.mon_current_sql发现正有些SQL执行返回大量的结果集，或者正在执行的SQL需要执行很长时间才有结果返回(大概10分钟才有结果返回)，前者force解决，后者找时间停应用创建必要索引解决。可归类为IO瓶颈。

案例四：网络瓶颈

比如某个应用程序慢在fetch上，在客户端执行，总时间都耗在fetch上了。在服务器上执行，fetch就没有什么耗时，stack文件显示卡在tcp receive上，如果连续抓的几个stack里面的function都在做tcp receive，基本就能确定这个环境的网络有问题了连续抓的几个stack里面的function都在做tcp receive，基本确定这个客户端到服务的网络有问题。从数据库服务器往本地ping 16384字节的包，结果如下：

可以看到丢包率还挺大的有丢包，丢包率还挺大的，58%的包都丢掉了，网络肯定是有问题。

共享链接：https://pan.baidu.com/s/17uSLp0mPTRoW-vD9lA8lmQ 密码：makf