本篇文章主要介绍了"DB2中，查看SQL语句在package cache中的执行计划。"，主要涉及到方面的内容，对于DB2感兴趣的同学可以参考一下：
如何收集SQL语句在package cache中的执行计划：如果应用程序的SQL中，使用了邦定变量的方式，必须得从package中抓取执行计划，以下是方法：Fo...
如何收集SQL语句在package cache中的执行计划：
如果应用程序的SQL中，使用了邦定变量的方式，必须得从package中抓取执行计划，以下是方法：
For bad case, we need to collect it from package cache.
a. flush package cache first
db2 flush package cache dynamic
b. run query ,do not run the same query from other place any more
c. run the following SQL to identify the query statement we are interested in and get its executable_id
SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(stmt_text,400) as stmt_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D', NULL,NULL,-1)) AS T
d. put the found executable_id in following command, then run the procedure:
CALL EXPLAIN_FROM_SECTION(
, 'M', NULL, 0, , ?, ?, ?, ?, ? )
e. run db2exfmt to create the access plan file
db2exfmt -d -o bad.out
----------------------------------------------------------------
测试过程：
$ db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL
$ db2 flush package cache dynamic
DB20000I The SQL command completed successfully.
$ db2 "select * from employee"
..
$ db2 "SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(stmt_text,400) as stmt_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D', NULL,NULL,-1)) AS T"
EXECUTABLE_ID STMT_EXEC_TIME TOTAL_CPU_TIME STMT_TEXT
------------------------------------------------------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x'0000000100000000000000000000005700000000000220160329231133977182' 0 152 select * from employee
x'0000000100000000000000000000005800000000000220160329231216327002' 0 0 SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(stmt_text,400) as stmt_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D', NULL,NULL,-1)) AS T
2 record(s) selected.
$ db2 "CALL EXPLAIN_FROM_SECTION( x'0000000100000000000000000000005700000000000220160329231133977182', 'M', NULL, 0, 'E105Q3C', ?, ?, ?, ?, ? )"
Value of output parameters
--------------------------
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : E105Q3C
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : E105Q3C
Parameter Name : EXPLAIN_TIME
Parameter Value : 2016-03-29-23.13.41.297943
Parameter Name : SOURCE_NAME
Parameter Value : SQLC2K26
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
Return Status = 0
$ db2exfmt -d SAMPLE -o bad.out
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Connecting to the Database.
Connect to Database Successful.
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) ==>
Output is in bad.out.
Executing Connect Reset -- Connect Reset was Successful.
下面是看执行的时间都花在哪些项目上了：
$ db2 -tvf a.sql > 1.out
$ db2 flush package cache dynamic
$ db2 "select * from employee"
$ db2 -tvf a.sql > 2.out
其中q.sql内容如下：
$ cat a.sql
select
MEMBER ,
SECTION_TYPE ,
INSERT_TIMESTAMP ,
EXECUTABLE_ID ,
PACKAGE_SCHEMA ,
PACKAGE_NAME ,
PACKAGE_VERSION_ID ,
SECTION_NUMBER ,
EFFECTIVE_ISOLATION ,
NUM_EXECUTIONS ,
NUM_EXEC_WITH_METRICS ,
PREP_TIME ,
TOTAL_ACT_TIME ,
TOTAL_ACT_WAIT_TIME ,
TOTAL_CPU_TIME ,
POOL_READ_TIME ,
POOL_WRITE_TIME ,
DIRECT_READ_TIME ,
DIRECT_WRITE_TIME ,
LOCK_WAIT_TIME ,
TOTAL_SECTION_SORT_TIME ,
TOTAL_SECTION_SORT_PROC_TIME ,
TOTAL_SECTION_SORTS ,
LOCK_ESCALS ,
LOCK_WAITS ,
ROWS_MODIFIED ,
ROWS_READ ,
ROWS_RETURNED ,
DIRECT_READS ,
DIRECT_READ_REQS ,
DIRECT_WRITES ,
DIRECT_WRITE_REQS ,
POOL_DATA_L_READS ,
POOL_TEMP_DATA_L_READS ,
POOL_XDA_L_READS ,
POOL_TEMP_XDA_L_READS ,
POOL_INDEX_L_READS ,
POOL_TEMP_INDEX_L_READS ,
POOL_DATA_P_READS ,
POOL_TEMP_DATA_P_READS ,
POOL_XDA_P_READS ,
POOL_TEMP_XDA_P_READS ,
POOL_INDEX_P_READS ,
POOL_TEMP_INDEX_P_READS ,
POOL_DATA_WRITES ,
POOL_XDA_WRITES ,
POOL_INDEX_WRITES ,
TOTAL_SORTS ,
POST_THRESHOLD_SORTS ,
POST_SHRTHRESHOLD_SORTS ,
SORT_OVERFLOWS ,
WLM_QUEUE_TIME_TOTAL ,
WLM_QUEUE_ASSIGNMENTS_TOTAL ,
DEADLOCKS ,
FCM_RECV_VOLUME ,
FCM_RECVS_TOTAL ,
FCM_SEND_VOLUME ,
FCM_SENDS_TOTAL ,
FCM_RECV_WAIT_TIME ,
FCM_SEND_WAIT_TIME ,
LOCK_TIMEOUTS ,
LOG_BUFFER_WAIT_TIME ,
NUM_LOG_BUFFER_FULL ,
LOG_DISK_WAIT_TIME ,
LOG_DISK_WAITS_TOTAL ,
LAST_METRICS_UPDATE ,
NUM_COORD_EXEC ,
NUM_COORD_EXEC_WITH_METRICS ,
VALID ,
TOTAL_ROUTINE_TIME ,
TOTAL_ROUTINE_INVOCATIONS ,
ROUTINE_ID ,
STMT_TYPE_ID ,
QUERY_COST_ESTIMATE ,
STMT_PKG_CACHE_ID ,
COORD_STMT_EXEC_TIME ,
STMT_EXEC_TIME ,
TOTAL_SECTION_TIME ,
TOTAL_SECTION_PROC_TIME ,
TOTAL_ROUTINE_NON_SECT_TIME ,
TOTAL_ROUTINE_NON_SECT_PROC_TIME ,
LOCK_WAITS_GLOBAL ,
LOCK_WAIT_TIME_GLOBAL ,
LOCK_TIMEOUTS_GLOBAL ,
LOCK_ESCALS_MAXLOCKS ,
LOCK_ESCALS_LOCKLIST ,
LOCK_ESCALS_GLOBAL ,
RECLAIM_WAIT_TIME ,
SPACEMAPPAGE_RECLAIM_WAIT_TIME ,
CF_WAITS ,
CF_WAIT_TIME ,
POOL_DATA_GBP_L_READS ,
POOL_DATA_GBP_P_READS ,
POOL_DATA_LBP_PAGES_FOUND ,
POOL_DATA_GBP_INVALID_PAGES ,
POOL_INDEX_GBP_L_READS ,
POOL_INDEX_GBP_P_READS ,
POOL_INDEX_LBP_PAGES_FOUND ,
POOL_INDEX_GBP_INVALID_PAGES ,
POOL_XDA_GBP_L_READS ,
POOL_XDA_GBP_P_READS ,
POOL_XDA_LBP_PAGES_FOUND ,
POOL_XDA_GBP_INVALID_PAGES ,
AUDIT_EVENTS_TOTAL ,
AUDIT_FILE_WRITES_TOTAL ,
AUDIT_FILE_WRITE_WAIT_TIME ,
AUDIT_SUBSYSTEM_WAITS_TOTAL ,
AUDIT_SUBSYSTEM_WAIT_TIME ,
DIAGLOG_WRITES_TOTAL ,
DIAGLOG_WRITE_WAIT_TIME ,
FCM_MESSAGE_RECVS_TOTAL ,
FCM_MESSAGE_RECV_VOLUME ,
FCM_MESSAGE_RECV_WAIT_TIME ,
FCM_MESSAGE_SENDS_TOTAL ,
FCM_MESSAGE_SEND_VOLUME ,
FCM_MESSAGE_SEND_WAIT_TIME ,
FCM_TQ_RECVS_TOTAL ,
FCM_TQ_RECV_VOLUME ,
FCM_TQ_RECV_WAIT_TIME ,
FCM_TQ_SENDS_TOTAL ,
FCM_TQ_SEND_VOLUME ,
FCM_TQ_SEND_WAIT_TIME ,
NUM_LW_THRESH_EXCEEDED ,
THRESH_VIOLATIONS ,
TOTAL_APP_SECTION_EXECUTIONS ,
TOTAL_ROUTINE_USER_CODE_PROC_TIME ,
TOTAL_ROUTINE_USER_CODE_TIME ,
TQ_TOT_SEND_SPILLS ,
EVMON_WAIT_TIME ,
EVMON_WAITS_TOTAL ,
TOTAL_EXTENDED_LATCH_WAIT_TIME ,
TOTAL_EXTENDED_LATCH_WAITS ,
MAX_COORD_STMT_EXEC_TIME ,
MAX_COORD_STMT_EXEC_TIMESTAMP ,
TOTAL_DISP_RUN_QUEUE_TIME ,
QUERY_DATA_TAG_LIST ,
TOTAL_STATS_FABRICATION_TIME ,
TOTAL_STATS_FABRICATIONS ,
TOTAL_SYNC_RUNSTATS_TIME ,
TOTAL_SYNC_RUNSTATS ,
TOTAL_PEDS ,
DISABLED_PEDS ,
POST_THRESHOLD_PEDS ,
TOTAL_PEAS ,
POST_THRESHOLD_PEAS ,
TQ_SORT_HEAP_REQUESTS ,
TQ_SORT_HEAP_REJECTIONS ,
POOL_QUEUED_ASYNC_DATA_REQS ,
POOL_QUEUED_ASYNC_INDEX_REQS ,
POOL_QUEUED_ASYNC_XDA_REQS ,
POOL_QUEUED_ASYNC_TEMP_DATA_REQS ,
POOL_QUEUED_ASYNC_TEMP_INDEX_REQS ,
POOL_QUEUED_ASYNC_TEMP_XDA_REQS ,
POOL_QUEUED_ASYNC_OTHER_REQS ,
POOL_QUEUED_ASYNC_DATA_PAGES ,
POOL_QUEUED_ASYNC_INDEX_PAGES ,
POOL_QUEUED_ASYNC_XDA_PAGES ,
POOL_QUEUED_ASYNC_TEMP_DATA_PAGES ,
POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES ,
POOL_QUEUED_ASYNC_TEMP_XDA_PAGES ,
POOL_FAILED_ASYNC_DATA_REQS ,
POOL_FAILED_ASYNC_INDEX_REQS ,
POOL_FAILED_ASYNC_XDA_REQS ,
POOL_FAILED_ASYNC_TEMP_DATA_REQS ,
POOL_FAILED_ASYNC_TEMP_INDEX_REQS ,
POOL_FAILED_ASYNC_TEMP_XDA_REQS ,
POOL_FAILED_ASYNC_OTHER_REQS ,
PREFETCH_WAIT_TIME ,
PREFETCH_WAITS ,
POOL_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP ,
POOL_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP ,
POOL_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP ,
NUM_WORKING_COPIES ,
IDA_SEND_WAIT_TIME ,
IDA_SENDS_TOTAL ,
IDA_SEND_VOLUME ,
IDA_RECV_WAIT_TIME ,
IDA_RECVS_TOTAL ,
IDA_RECV_VOLUME ,
STMTNO ,
NUM_ROUTINES ,
ROWS_DELETED ,
ROWS_INSERTED ,
ROWS_UPDATED ,
TOTAL_HASH_JOINS ,
TOTAL_HASH_LOOPS ,
HASH_JOIN_OVERFLOWS ,
HASH_JOIN_SMALL_OVERFLOWS ,
POST_SHRTHRESHOLD_HASH_JOINS ,
TOTAL_OLAP_FUNCS ,
OLAP_FUNC_OVERFLOWS ,
INT_ROWS_DELETED ,
INT_ROWS_INSERTED ,
INT_ROWS_UPDATED ,
POOL_COL_L_READS ,
POOL_TEMP_COL_L_READS ,
POOL_COL_P_READS ,
POOL_TEMP_COL_P_READS ,
POOL_COL_LBP_PAGES_FOUND ,
POOL_COL_WRITES ,
POOL_COL_GBP_L_READS ,
POOL_COL_GBP_P_READS ,
POOL_COL_GBP_INVALID_PAGES ,
POOL_COL_GBP_INDEP_PAGES_FOUND_IN_LBP ,
POOL_QUEUED_ASYNC_COL_REQS ,
POOL_QUEUED_ASYNC_TEMP_COL_REQS ,
POOL_QUEUED_ASYNC_COL_PAGES ,
POOL_QUEUED_ASYNC_TEMP_COL_PAGES ,
POOL_FAILED_ASYNC_COL_REQS ,
POOL_FAILED_ASYNC_TEMP_COL_REQS ,
TOTAL_COL_TIME ,
TOTAL_COL_PROC_TIME ,
TOTAL_COL_EXECUTIONS ,
COMM_EXIT_WAIT_TIME ,
COMM_EXIT_WAITS ,
POST_THRESHOLD_HASH_JOINS ,
TOTAL_HASH_GRPBYS ,
HASH_GRPBY_OVERFLOWS ,
POST_THRESHOLD_HASH_GRPBYS ,
POST_THRESHOLD_OLAP_FUNCS ,
varchar(stmt_text,400) as STMT_TEXT
from table (mon_get_pkg_cache_stmt('D',null,null,-1));
以上就介绍了DB2中，查看SQL语句在package cache中的执行计划。，包括了方面的内容，希望对DB2有兴趣的朋友有所帮助。
本文网址链接：http://www.codes51.com/article/detail_542270.html
