原文：http://blog.51cto.com/freebile/1068610
db2有图形执行计划显示工具,如果没有图形环境,如unix主机,可以生成文本的
文件来显示执行计划
1.如果第一次执行,请先 connect to dbname,
执行db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL建立执行计划表
2.db2 set current explain mode explain
设置成解释模式,并不真正执行下面将发出的sql命令
3.db2 "select count(*) from DOA.ODS_SFC_SALE_BARCODE_T"
执行你想要分析的sql语句
4.db2 set current explain mode no
取消解释模式
5.db2exfmt -d sample -g TIC -w -l -s % -n % -o db2exmt.out
执行计划输出到文件db2exmt.out
相关参数设置可以参考
(This application formats the contents of the Explain tables.
Given a database name and other qualifying information, this tool will,
query the Explain tables for information and format the results.
Syntax is:
db2exfmt [[-1] [-d <dbname>;] [-e <schema>;] [-f O] [-h] [-l] [-n <name>;] [-o <outfile>;]
[-s <schema>;] [-t]] [-u <user>; <pw>;] [-w <timestamp>;] [-# <sectnbr>;] [-v <srcvers>;]
Input Fields:
-d <dbname>; = database name containing packages
-e <schema>; = Explain table schema
-f O = Formatting flags (O = Operator summary)
-g[x] [O[T|F]IC] - Graph plan. x - turn off options (default is to turn them on) Options include:
O = only generate graph
T = Include Total Cost in graph
F = Include First Tuple Cost in graph
I = Include I/O Cost in graph
C = Include Cardinality in graph
Any combination of these options is allowed,
except 'F' and 'T', which are mutually exclusive.
-h = help
-l = respect package name case
-n <name>; = name of source of Explain
request (SOURCE_NAME)
-no_map_char = do no map a non-printable character to a '.'
-no_prompt = do not prompt for user input
-o <outfile>; = name of output file
-r <requester>; = id of explain requester
-s <schema>; = Schema or qualifier of source of Explain
request (SOURCE_SCHEMA)
-t = terminal output desired
-u <user>; <pw>; = user ID and password for connecting to database
-v <srcvers>; = Source Version of source of Explain request (default %)
-w <timestamp>; = Explain timestamp (use -1 to get newest Explain request)
-# <sectnbr>; = section number in source (use zero for all sections)
-1 = Use defaults -e % -n % -s % -v % -w -1 -# 0 If Explain schema is not supplied, the contents of the environment
variable $USER, or $USERNAME will be used as a default. If this
variable is not found, the user will be prompted for an Explain schema.
Source name, source schema, and Explain timestamp may be supplied
in LIKE predicate form, which allows percent sign (%) and
underscore (_) to be used as pattern matching characters to select
multiple sources with one invocation.
Prompting will occur for all fields that are not supplied or are
incompletely specified (except for the -h, -l and -no_map_char options).
If -o is specified without a file name, and -t is not specified,
the user will be prompted for a file name (the default name is
db2exfmt.out).
If neither -o nor -t is specified, the user will be prompted for
a file name (the default is terminal output).
If -o and -t are both specified, then the output will be directed
to the terminal.
)
6.查看输出文件分析sql的运行开销,示例输出如下
-------------------
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: 2012-11-23-16.02.53.924371
EXPLAIN_REQUESTER: DOAADMIN
Database Context:
----------------
Parallelism: None
CPU Speed: 4.959615e-07
Comm Speed: 100
Buffer Pool size: 438463
Sort Heap size: 256
Database Heap size: 2645
Lock List size: 100
Maximum Lock List: 20
Average Applications: 1
Locks Available: 1280
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
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 count(*)
from DOA.ODS_SFC_SALE_BARCODE_T
Optimized Statement:
-------------------
SELECT Q3.$C0
FROM
(SELECT COUNT(*)
FROM
(SELECT $RID$
FROM DOA.ODS_SFC_SALE_BARCODE_T AS Q1) AS Q2) AS Q3
Access Plan:
-----------
Total Cost: 51435.9
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
51435.9
2027
|
765027
IXSCAN
( 3)
51341.1
2027
|
765027
INDEX: DOA
S_SALE_T1
Q1
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 51435.9
Cumulative CPU Cost: 1.53426e+09
Cumulative I/O Cost: 2027
Cumulative Re-Total Cost: 747.847
Cumulative Re-CPU Cost: 1.50787e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 51435.9
Estimated Bufferpool Buffers: 2028
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.5.0.4 : s090429
HEAPUSE : (Maximum Statement Heap Usage)
80 Pages
PREPTIME: (Statement prepare time)
1 milliseconds
STMTHEAP: (Statement heap size)
6402
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
2) GRPBY : (Group By)
Cumulative Total Cost: 51435.9
Cumulative CPU Cost: 1.53426e+09
Cumulative I/O Cost: 2027
Cumulative Re-Total Cost: 747.847
Cumulative Re-CPU Cost: 1.50787e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 51435.9
Estimated Bufferpool Buffers: 2028
Arguments:
---------
AGGMODE : (Aggregration Mode)
COMPLETE
GROUPBYC: (Group By columns)
FALSE
GROUPBYN: (Number of Group By columns)
0
ONEFETCH: (One Fetch flag)
FALSE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 765027
Number of columns: 0
Subquery predicate ID: Not Applicable
Output Streams:
--------------
3) To Operator #1
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
3) IXSCAN: (Index Scan)
Cumulative Total Cost: 51341.1
Cumulative CPU Cost: 1.343e+09
Cumulative I/O Cost: 2027
Cumulative Re-Total Cost: 652.991
Cumulative Re-CPU Cost: 1.31662e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 50.0327
Estimated Bufferpool Buffers: 2028
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
2026
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Input Streams:
-------------
1) From Object DOA.S_SALE_T1
Estimated number of rows: 765027
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 765027
Number of columns: 0
Subquery predicate ID: Not Applicable
Objects Used in Access Plan:
---------------------------
Schema: DOA
Name: ODS_SFC_SALE_BARCODE_T
Type: Table (reference only)
Schema: DOA
Name: S_SALE_T1
Type: Index
Time of creation: 2011-01-15-14.03.41.217506
Last statistics update: 2012-04-23-18.45.51.142593
Number of columns: 1
Number of rows: 765027
Width of rows: -1
Number of buffer pool pages: 50996
Distinct row values: No
Tablespace name: TS_INDEX_4K
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Index clustering statistic: 97.000000
Index leaf pages: 2026
Index tree levels: 3
Index full key cardinality: 18700
Index first key cardinality: 18700
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 2026
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 765027
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: DOA
Base Table Name: ODS_SFC_SALE_BARCODE_T
Columns in index:
BILL(A)
Base Table For Index Not Already Shown:
---------------------------------------
Schema: DOA
Name: ODS_SFC_SALE_BARCODE_T
Time of creation: 2011-01-15-13.40.52.458517
Last statistics update: 2012-04-23-18.45.51.142593
Number of data partitions: 1
Number of columns: 21
Number of rows: 765027
Number of pages: 50996
Number of pages with rows: 50996
Table overflow record count: 0
Indexspace name: TS_INDEX_4K
Tablespace name: TS_4K
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: TS_4K