db2有圖形執(zhí)行計劃顯示工具,如果沒有圖形環(huán)境,如unix主機,可以生成文本的
文件來顯示執(zhí)行計劃
1.如果第一次執(zhí)行,請先 connect to dbname,
執(zhí)行db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL建立執(zhí)行計劃表
2.db2 set current explain mode explain
設置成解釋模式,并不真正執(zhí)行下面將發(fā)出的sql命令
3.db2 "select count(*) from staff"
執(zhí)行你想要分析的sql語句
4.db2 set current explain mode no
取消解釋模式
5.db2exfmt -d sample -g TIC -w -l -s % -n % -o db2exmt.out
執(zhí)行計劃輸出到文件db2exmt.out
相關參數(shù)設置可以參考
(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的運行開銷,示例輸出如下
AQADB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 08.01.0
SOURCE_NAME: SQLC2E03
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2005-04-15-14.52.21.917001
EXPLAIN_REQUESTER: GONGJS
Database Context:
----------------
Parallelism: None
CPU Speed: 3.581944e-007
Comm Speed: 0
Buffer Pool size: 250
Sort Heap size: 256
Database Heap size: 600
Lock List size: 50
Maximum Lock List: 22
Average Applications: 1
Locks Available: 1243
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select count(*)
from staff
Optimized Statement:
-------------------
SELECT Q3.$C0
FROM
(SELECT COUNT(* )
FROM
(SELECT $RID$
FROM GONGJS.STAFF AS Q1) AS Q2) AS Q3
Access Plan:
-----------
Total Cost: 25.0428
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
25.0425
1
|
35
TBSCAN
( 3)
25.0393
1
|
35
TABLE: GONGJS
STAFF
1) RETURN: (Return Result)
Cumulative Total Cost: 25.0428
Cumulative CPU Cost: 119475
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0257477
Cumulative Re-CPU Cost: 71882
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0419
Estimated Bufferpool Buffers: 1
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.0.36 : s021023
ENVVAR : (Environment Variable)
DB2_LIKE_VARCHAR = Y,Y
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$C0
2) GRPBY : (Group By)
Cumulative Total Cost: 25.0425
Cumulative CPU Cost: 118585
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0254289
Cumulative Re-CPU Cost: 70992
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0418
Estimated Bufferpool Buffers: 1
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: 35
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:
------------
+$C0
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 25.0393
Cumulative CPU Cost: 109585
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0222052
Cumulative Re-CPU Cost: 61992
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0177
Estimated Bufferpool Buffers: 1
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
Input Streams:
-------------
1) From Object GONGJS.STAFF
Estimated number of rows: 35
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 35
Number of columns: 0
Subquery predicate ID: Not Applicable
Objects Used in Access Plan:
---------------------------
Schema: GONGJS
Name: STAFF
Type: Table
Time of creation: 2005-02-28-17.40.36.579009
Last statistics update: 2005-04-13-16.26.02.304000
Number of columns: 7
Number of rows: 35
Width of rows: 12
Number of buffer pool pages: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 16
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
