DB2 存储过程执行计划的查看及监控方法
一，编写存储过程。
[db2inst1@db2lab ~]$ cat test.sql
create procedure sales_status
(in quota integer)
dynamic result sets 2
language sql
begin
declare SQLSTATE char(5);
declare rs cursor with return for
select * from t1;
open rs;
end
@
二，建立存储过程
[db2inst1@db2lab ~]$ db2 -td@ -f 1.sql
DB20000I The SQL command completed successfully.
三，执行存储过程
[db2inst1@db2lab ~]$ db2 "call sales_status(1)"
Result set 1
--------------
ID
-------
1.
1 record(s) selected.
Return Status = 0
四，利用表函数MON_GET_PKG_CACHE_STMT抓取static的信息，获取PACKAGE_NAME及SQL语句
[db2inst1@db2lab ~]$ db2 "select PACKAGE_NAME,SECTION_NUMBER,EXECUTABLE_ID
from TABLE(MON_GET_PKG_CACHE_STMT ( 'S', NULL, NULL, -1)) as T"
PACKAGE_NAME SECTION_NUMBER EXECUTABLE_ID
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------
P0462483 1 x'0100000000000000BE0100000000000001000000010020140415004624839232'
1 record(s) selected.
五，利用EXECUTABLE_ID，获取SQL语句
[db2inst1@db2lab ~]$ db2 "SELECT STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT
> (null, x'0100000000000000BE0100000000000001000000010020140415004624839232', null, -2))"
STMT_TEXT
-------------------------------------------------------
DECLARE RS cursor with return for
select * from T1 where ID = :HV00008 :HI00008
1 record(s) selected.
六，查看package_name信息，valid列信息需要重点关注，信息中心解释如下：
|
[db2inst1@db2lab ~]$ db2 list packages for all |grep -i P0462483
Bound Total Isolation
Package Schema Version by sections Valid Format level Blocking
----------- --------- ----------- --------- ------------- ------- -------- --------- --------
P0462483 DB2INST1 DB2INST1 1 Y 0 CS U
七，获取该package_name的执行计划信息
[db2inst1@db2lab ~]$ db2expln -d pos -g -c db2inst1 -p P0462483 -s 0 -t
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** PACKAGE ***************************************
Package Name = "DB2INST1"."P0462483" Version =
Prep Date = 2014/04/15
Prep Time = 00:46:24
Bind Timestamp = 2014-04-15-00.46.24.839232
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"DB2INST1"
-------------------- SECTION ---------------------------------------
Section = 1
Statement:
DECLARE RS cursor
with return
for
select *
from T1
where ID =:HV00008 :HI00008
Section Code Page = 1208
Estimated Cost = 7.569436
Estimated Cardinality = 1.000000
Access Table Name = DB2INST1.T1 ID = 2,4
| #Columns = 1
| Skip Inserted Rows
| Evaluate Block/Data Predicates Before Locking Committed Row
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
| | Return Data to Application
| | | #Columns = 1
Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1
RETURN
( 1)
7.56944
|
1
TBSCAN
( 2)
7.56944
|
1
Table:
DB2INST1
T1
总结：DB2的执行计划变化较多，不经常runstats和rebind的时候，有可能本地执行计划很好，但在实际生产环境上执行计划较差，这需要DBA能抓取实时SQL执行计划，静态sql通过上述方式抓取，动态sql需要借助db2expln的cache选项抓取，来分析sql的消耗情况
Dynamic Statement Options:
-cache <anchID>,<stmtUID>,<envID>,<varID>
= Retrieve the statement identified by the given IDs
from the dynamic SQL cache. (The IDs can be
obtained by running db2pd with the -dynamic
option.
著作权归作者所有
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞6Ctrl+Enter 发表
添加新评论16 条评论
2014-05-08 15:35
2014-05-08 10:09
2014-05-08 10:09
2014-05-07 09:10
2014-04-30 22:22
2014-04-30 17:59
2014-04-27 09:29
2014-04-24 10:22
2014-04-22 16:38
request (SOURCE_NAME)
你研究下呗
2014-04-22 16:14
2014-04-22 16:09
2014-04-22 16:03
db2 "select * from t1"
db2 set current explain mode no
db2exfmt -l -d db2name -o exptp2.txt
2014-04-22 15:44
db2 "select * from t1"
db2 set current explain mode no
db2exfmt -l -d db2name -o exptp2.txt
2014-04-22 15:12
db2exfmt -d dbname -o xxx.txt
2014-04-22 14:24
db2exfmt -d dbname -o xxx.txt
2014-04-22 13:46