|
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 staff" 执行你想要分析的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的运行开销,示例输出如下 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 |
|
|
