DB2执行计划怎么分析

这篇文章给大家介绍DB2执行计划怎么分析，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

分析一个长时间运行SQL的执行计划，怀疑优化器的估计值和实际值出入很大，造成执行计划不准确，于是采用下面的方法在执行计划中把实际值和估计值都输出。

####修改DB级别的参数
db2 update db cfg for SAMPLE using SECTION_ACTUALS BASE

####连接数据库
db2 connect to SAMPLE

####创建EXPLAIN表，如果已有，请忽略这一步
db2 "call sysproc.sysinstallobjects('EXPLAIN','C',null,null)"

####创建activity event monitor
db2 "
create event monitor actEvmon for activities write to table
activity (table activity, in USERSPACE1),
activityvals (table activityvals, in USERSPACE1),
activitystmt (table activitystmt, in USERSPACE1),
activitymetrics (table activitymetrics, in USERSPACE1),
control (table control, in USERSPACE1)
manualstart"

db2 "set event monitor actEvmon state 1"

####查看当前的连接
db2 "values sysproc.mon_get_application_id()"

1                           
----------------------------
*LOCAL.db2inst1.200403101703

  1 record(s) selected.

db2 "values sysproc.mon_get_application_handle()"

1
--------------------
               10372

  1 record(s) selected.

####Turn on the collection of activity data for this specific application-handle
db2 "CALL WLM_SET_CONN_ENV(10372, '<collectactdata> WITH DETAILS, SECTION AND VALUES </collectactdata><collectactpartition> ALL </collectactpartition>')"

####执行SQL
db2 -tvf s1.sql > s1.out

db2 flush event monitor actEvmon buffer

####查看所执行SQL的UOW_ID和ACTIVITY_ID
db2 "select a.APPL_ID, a.ACTIVITY_ID, a.UOW_ID, a.ACT_EXEC_TIME, a.TIME_CREATED, SUBSTR (b.STMT_TEXT, 1, 20) as STMT_TEXT
from activity a, activitystmt b
where a.APPL_ID=b.APPL_ID and a.ACTIVITY_ID=b.ACTIVITY_ID and a.UOW_ID=b.UOW_ID
AND a.APPL_ID like '%db2inst1.200403101703%'
order by ACT_EXEC_TIME desc"

APPL_ID                      ACTIVITY_ID  UOW_ID      ACT_EXEC_TIME        TIME_CREATED               STMT_TEXT
---------------------------- ------------ ----------- -------------------- -------------------------- --------------------
*LOCAL.db2inst1.200403101703            1           8           1797313075 2020-04-03-05.18.33.868968 with temp as ( selec
*LOCAL.db2inst1.200403101703            1           9                 1549 2020-04-03-05.48.52.396869 flush event monitor
*LOCAL.db2inst1.200403101703            1           7                   50 2020-04-03-05.18.33.860221 select current CLIEN

  3 record(s) selected. ####调用EXPLAIN_FROM_ACTIVITY
db2 "CALL EXPLAIN_FROM_ACTIVITY ( '*LOCAL.db2inst1.200403101703', 8, 1, 'ACTEVMON', 'DB2INST1',?, ?, ?, ?, ? )"

####生成执行计划
db2exfmt -1 -d SAMPLE -o explain.out

####关闭监控
db2 "CALL WLM_SET_CONN_ENV(10372, '<collectactdata>NONE</collectactdata>')"
db2 "set event monitor actEvmon state 0"

关于DB2执行计划怎么分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。