DB2调优工具包– db2exfmt

2012年6月19日,作者: 杰克·范瓦斯(Jack Vamvas)

db2exfmt   -说明表格格式命令-提供调整查询所需的信息。 数据库管理员 目的是降低查询成本。 降低的查询成本等于更快的用户响应。降低查询成本–提高整体性能。 

阅读帖子- DB2调优工具包– db2expln    -分析 特定的访问路径  query.

阅读帖子-  DB2 调音 Toolkit – DB2设计顾问-Ddb2advis -用于索引建议

 

语法 和过程示例:

步骤1 –创建源文件

放一个 在名为index.sql的输入文件中查询。这只是一个示例查询

select SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10),
substr(name,1,30), SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard, sequential_pages, density, iid, uniquerule, stats_time, colnames
from sysibm.sysindexes a
ORDER BY tbcreator, TBNAME, NAME;

 

 Step 2 执行db2exfmt command

db2 set current explain mode explain
db2 –tvf  在 dextune.sql
db2exfmt -1 –d myDB –o  db2exfmt .out

 

输出将是  be similar to :

 

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 		09.05.3
SOURCE_NAME: 		SQLC2G15
SOURCE_SCHEMA: 		NULLID  
SOURCE_VERSION: 	
EXPLAIN_TIME: 		2012-06-19-14.40.37.846644 
EXPLAIN_REQUESTER: 	***********  

Database Context:
----------------
	Parallelism: 		None
	CPU Speed: 		2.361721e-07
	Comm Speed: 		0
	Buffer Pool size: 	138228
	Sort Heap size: 	7157
	Database Heap size: 	2761
	Lock List size: 	72726
	Maximum Lock List: 	97
	Average Applications: 	1
	Locks Available: 	4514830

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 SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10), substr(name,1,30), 
        SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard, 
        sequential_pages, density, iid, uniquerule, stats_time, colnames 
from sysibm.sysindexes a 
WHERE density = 1 AND iid = 3


Optimized Statement:
-------------------
SELECT SUBSTR(Q1.TBNAME, 1, 40), SUBSTR(Q1.TBCREATOR, 1, 10), SUBSTR(Q1.NAME, 
        1, 30), SUBSTR(Q1.CREATOR, 1, 8), SUBSTR(Q1.COLNAMES, 1, 60), 
        Q1.FIRSTKEYCARD AS "FIRSTKEYCARD", Q1.FULLKEYCARD AS "FULLKEYCARD", 
        Q1.SEQUENTIAL_PAGES AS "SEQUENTIAL_PAGES", 1 AS "DENSITY", Q1.IID AS 
        "IID", Q1.UNIQUERULE AS "UNIQUERULE", Q1.STATS_TIME AS "STATS_TIME", 
        Q1.COLNAMES AS "COLNAMES" 
FROM SYSIBM.SYSINDEXES AS Q1 
WHERE (Q1.IID = 3) AND (Q1.DENSITY = 1)

Access Plan:
-----------
	Total Cost: 		140.165
	Query Degree:		1

      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
    0.107904 
     TBSCAN
     (   2)
     140.165 
       72 
       |
      1261 
 TABLE: SYSIBM  
   SYSINDEXES
       Q1



Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statement.


Plan Details:
-------------


	1) RETURN: (Return Result)
		Cumulative Total Cost: 		140.165
		Cumulative CPU Cost: 		3.57774e+06
		Cumulative I/O Cost: 		72
		Cumulative Re-Total Cost: 	0.755584
		Cumulative Re-CPU Cost: 	3.19929e+06
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	140.06
		Estimated Bufferpool Buffers: 	72

		Arguments:
		---------
		BLDLEVEL: (Build level)
			DB2 v9.5.0.3 : s081118
		HEAPUSE : (Maximum Statement Heap Usage)
			112 Pages
		STMTHEAP: (Statement heap size)
			4096

		Input Streams:
		-------------
			2) From Operator #2

				Estimated number of rows: 	0.107904
				Number of columns: 		13
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.COLNAMES+Q2.STATS_TIME+Q2.UNIQUERULE
				+Q2.IID+Q2.DENSITY+Q2.SEQUENTIAL_PAGES
				+Q2.FULLKEYCARD+Q2.FIRSTKEYCARD+Q2.$C4+Q2.$C3
				+Q2.$C2+Q2.$C1+Q2.$C0


	2) TBSCAN: (Table Scan)
		Cumulative Total Cost: 		140.165
		Cumulative CPU Cost: 		3.57724e+06
		Cumulative I/O Cost: 		72
		Cumulative Re-Total Cost: 	0.755467
		Cumulative Re-CPU Cost: 	3.1988e+06
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	140.06
		Estimated Bufferpool Buffers: 	72

		Arguments:
		---------
		MAXPAGES: (Maximum pages for prefetch)
			ALL
		PREFETCH: (Type of Prefetch)
			SEQUENTIAL
		ROWLOCK : (Row Lock  在 tent)
			NEXT KEY SHARE
		SCANDIR : (Scan Direction)
			FORWARD
		TABLOCK : (Table Lock  在 tent)
			INTENT SHARE
		TBISOLVL: (Table access Isolation Level)
			CURSOR STABILITY

		Predicates:
		----------
		2) Sargable Predicate
			Comparison Operator: 		Equal (=)
			Subquery Input Required: 	No
			Filter Factor: 			0.0412371

			Predicate Text:
			--------------
			(Q1.IID = 3)

		3) Sargable Predicate
			Comparison Operator: 		Equal (=)
			Subquery Input Required: 	No
			Filter Factor: 			0.00207507

			Predicate Text:
			--------------
			(Q1.DENSITY = 1)


		Input Streams:
		-------------
			1) From Object SYSIBM.SYSINDEXES

				Estimated number of rows: 	1261
				Number of columns: 		13
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q1.$RID$+Q1.STATS_TIME+Q1.UNIQUERULE
				+Q1.SEQUENTIAL_PAGES+Q1.FULLKEYCARD
				+Q1.FIRSTKEYCARD+Q1.COLNAMES+Q1.CREATOR
				+Q1.NAME+Q1.TBCREATOR+Q1.TBNAME+Q1.IID
				+Q1.DENSITY


		Output Streams:
		--------------
			2) To Operator #1

				Estimated number of rows: 	0.107904
				Number of columns: 		13
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.COLNAMES+Q2.STATS_TIME+Q2.UNIQUERULE
				+Q2.IID+Q2.DENSITY+Q2.SEQUENTIAL_PAGES
				+Q2.FULLKEYCARD+Q2.FIRSTKEYCARD+Q2.$C4+Q2.$C3
				+Q2.$C2+Q2.$C1+Q2.$C0

 Related Posts

 DB2调优工具包– db2expln   

DB2 调音 Toolkit – DB2设计顾问-Ddb2advis   

作者:Jack Vamvas(http://www.dba-db2.com)

分享:

验证您的评论

预览您的评论

这仅仅是一个预览。您的评论尚未发布。

 加工...
您的评论无法发布。错误类型:
您的评论已保存。评论经过审核,只有在获得作者批准后才会显示。 发表其他评论

您输入的字母和数字与图像不匹配。请再试一次。

作为发表评论的最后一步,请输入下图中显示的字母和数字。这样可以防止自动程序发布评论。

读取这张图片有困难吗? 查看备用。

 加工...

在DB2 调音 Toolkit – db2exfmt 上发表评论

评论是经过审核的,直到作者批准后才会显示。


ysaijiu.com | DB2性能调优 | 数据库管理员 DB2 :一切 | 常问问题 | 联系 | 版权