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"