這篇文章給大家介紹DB2執(zhí)行計(jì)劃怎么分析，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

10多年的南潯網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。營(yíng)銷型網(wǎng)站的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整南潯建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)從事“南潯網(wǎng)站設(shè)計(jì)”,“南潯網(wǎng)站推廣”以來(lái)，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

分析一個(gè)長(zhǎng)時(shí)間運(yùn)行SQL的執(zhí)行計(jì)劃，懷疑優(yōu)化器的估計(jì)值和實(shí)際值出入很大，造成執(zhí)行計(jì)劃不準(zhǔn)確，于是采用下面的方法在執(zhí)行計(jì)劃中把實(shí)際值和估計(jì)值都輸出。

####修改DB級(jí)別的參數(shù)
db2 update db cfg for SAMPLE using SECTION_ACTUALS BASE

####連接數(shù)據(jù)庫(kù)
db2 connect to SAMPLE

####創(chuàng)建EXPLAIN表，如果已有，請(qǐng)忽略這一步
db2 "call sysproc.sysinstallobjects('EXPLAIN','C',null,null)"

####創(chuàng)建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"

####查看當(dāng)前的連接
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 ')"

####執(zhí)行SQL
db2 -tvf s1.sql > s1.out

db2 flush event monitor actEvmon buffer

####查看所執(zhí)行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. ####調(diào)用EXPLAIN_FROM_ACTIVITY
db2 "CALL EXPLAIN_FROM_ACTIVITY ( '*LOCAL.db2inst1.200403101703', 8, 1, 'ACTEVMON', 'DB2INST1',?, ?, ?, ?, ? )"

####生成執(zhí)行計(jì)劃
db2exfmt -1 -d SAMPLE -o explain.out

####關(guān)閉監(jiān)控
db2 "CALL WLM_SET_CONN_ENV(10372, 'NONE')"
db2 "set event monitor actEvmon state 0"

關(guān)于DB2執(zhí)行計(jì)劃怎么分析就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。