DB2执行计划怎么分析

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

10多年的南浔网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。营销型网站的优势是能够根据用户设备显示端的尺寸不同，自动调整南浔建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。成都创新互联从事“南浔网站设计”,“南浔网站推广”以来，每个客户项目都认真落实执行。

分析一个长时间运行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, ' WITH DETAILS, SECTION AND VALUES ALL ')"

####执行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, 'NONE')"
db2 "set event monitor actEvmon state 0"

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