使用命令行查看执行计划
1.如果第一次执行,请先 connect to dbname,
2.执行db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL建立执行计划表
3.db2 set current explain mode explain
设置成解释模式,并不真正执行下面将发出的sql命令
4.db2 "select order_number from <table_name>where order_number=‘000000000036’ and mer_ID=‘111’ and order_time=‘20121111111111’ and order_type=‘01’ "
执行你想要分析的sql语句
5.db2 set current explain mode no
取消解释模式
6.db2exfmt -d XXX -g TIC -w -l -s % -n % -o db2exmt.out
执行计划输出到文件db2exmt.out
详细步骤：
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven > db2 set current explain mode explain
DB20000I The SQL command completed successfully.
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven > db2 "SELECT 'grant all on table ' ||tabschema||'.'||tabname|| ' to user GZ851;' FROM syscat.tables WHERE owner in ('LIUNIAN1','SUNJING1','CHENJ1','FUWEI1','WANGP1','ZHOUY1') and CREATE_TIME>(current TIMESTAMP - 1 days)"
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven > db2 set current explain mode no
DB20000I The SQL command completed successfully.
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven > db2exfmt -d gzdmt -g TIC -w -l -s % -n % -o db2exmt.out
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
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 section number (0 for all, Default 0) ==>
Output is in db2exmt.out.
Executing Connect Reset -- Connect Reset was Successful.
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven >
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven > cat db2exmt.out
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.05.4
SOURCE_NAME: SQLC2G15
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2014-09-23-13.50.10.797690
EXPLAIN_REQUESTER: DB2INST1
Database Context:
----------------
Parallelism: Inter-Partition Parallelism
CPU Speed: 3.542582e-07
Comm Speed: 100
Buffer Pool size: 303110
Sort Heap size: 50000
Database Heap size: 2610
Lock List size: 3200
Maximum Lock List: 60
Average Applications: 1
Locks Available: 122880
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 7
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 3
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT 'grant all on table ' ||tabschema||'.'||tabname|| ' to user GZ851;'
FROM syscat.tables
WHERE owner in ('LIUNIAN1','SUNJING1','CHENJ1','FUWEI1','WANGP1','ZHOUY1')
and CREATE_TIME>(current TIMESTAMP - 1 days)
Optimized Statement:
-------------------
SELECT ('grant all on table ' || Q3."CREATOR" || '.' || Q3."NAME" || ' to
user GZ851;')
FROM SYSIBM.SYSTABLES AS Q3
WHERE ($C0 < Q3."CTIME") AND Q3."DEFINER" IN ('LIUNIAN1', 'SUNJING1',
'CHENJ1', 'FUWEI1', 'WANGP1', 'ZHOUY1')
Access Plan:
-----------
Total Cost: 87045.4
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
0.697114
DTQ
( 2)
87045.4
44496
|
0.697114
TBSCAN
( 3)
87045.4
44496
|
314720
TABLE: SYSIBM
SYSTABLES
Q3
Extended Diagnostic Information:
--------------------------------
Diagnostic formatting function does no exist. No extended Diagnostic Information is available.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 87045.4
Cumulative CPU Cost: 8.91113e+08
Cumulative I/O Cost: 44496
Cumulative Re-Total Cost: 241.791
Cumulative Re-CPU Cost: 6.82527e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 87045.2
Cumulative Comm Cost: 1.14643
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 44496
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.5.0.4 : s090429
HEAPUSE : (Maximum Statement Heap Usage)
192 Pages
PREPNODE: (Prepare Node Number)
0
PREPTIME: (Statement prepare time)
17 milliseconds
STMTHEAP: (Statement heap size)
4096
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 0.697114
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
Partition Column Names:
----------------------
+NONE
2) TQ : (Table Queue)
Cumulative Total Cost: 87045.4
Cumulative CPU Cost: 8.91113e+08
Cumulative I/O Cost: 44496
Cumulative Re-Total Cost: 241.791
Cumulative Re-CPU Cost: 6.82527e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 87045.2
Cumulative Comm Cost: 1.14643
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 44496
Arguments:
---------
LISTENER: (Listener Table Queue type)
FALSE
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
TQSEND : (Table Queue Write type)
DIRECTED
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 0.697114
Partition Map ID: 0
Partitioning: ( 0)
Single Node (# 0) Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
3) To Operator #1
Estimated number of rows: 0.697114
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
Partition Column Names:
----------------------
+NONE
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 87045.4
Cumulative CPU Cost: 8.91016e+08
Cumulative I/O Cost: 44496
Cumulative Re-Total Cost: 241.791
Cumulative Re-CPU Cost: 6.82527e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 87045.1
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 44496
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
4) Sargable Predicate
Comparison Operator: Less Than (<)
Subquery Input Required: No
Filter Factor: 0.0031687
Predicate Text:
--------------
($C0 < Q3."CTIME")
5) Sargable Predicate
Comparison Operator: In List (IN), evaluated by binary search (list sorted at compile-time)
Subquery Input Required: No
Filter Factor: 0.000699034
Predicate Text:
--------------
Q3."DEFINER" IN ('LIUNIAN1', 'SUNJING1', 'CHENJ1',
'FUWEI1', 'WANGP1', 'ZHOUY1')
Input Streams:
-------------
1) From Object SYSIBM.SYSTABLES
Estimated number of rows: 314720
Partition Map ID: 0
Partitioning: ( 0)
Single Node (# 0) Partition
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.$RID$+Q3."CTIME"+Q3."DEFINER"+Q3."NAME"
+Q3."CREATOR"
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 0.697114
Partition Map ID: 0
Partitioning: ( 0)
Single Node (# 0) Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
Partition Column Names:
----------------------
+NONE
Objects Used in Access Plan:
---------------------------
Schema: SYSIBM
Name: SYSAUDITPOLICIES
Type: Table (reference only)
Schema: SYSIBM
Name: SYSCOLLATIONS
Type: Table (reference only)
Schema: SYSCAT
Name: TABLES
Type: View (reference only)
Schema: SYSIBM
Name: SYSTABLES
Type: Table
Time of creation: 2009-06-30-09.32.06.716136
Last statistics update: 2014-08-30-09.15.44.749749
Number of columns: 71
Number of rows: 314720
Width of rows: 3383
Number of buffer pool pages: 44384
Number of data partitions: 1
Distinct row values: No
Tablespace name: SYSCATSPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 24
Container extent page count: 4
Table overflow record count: 112
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
db2inst1@mkt1:/dmt01_inst/db2inst1/enmotech/steven >
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
