Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB2 db2expln/db2advis/db2batch简单介绍和实战

2016-02-23
DB2

摘要

db2expln将存取计划以文本形式输出,它只提供存取计划中主要的信息,并不包含每一个操作占用多少 CPU、I/O、占用 Buffer 的大小以及使用的数据库对象等信息,方便阅读。但是 db2expln 也会将各项有关存取计划的信息存入 Explain 表中,用户可以使用 db2exfmt 察看详细的格式化文本信息。

db2advis是DB2提供的另外一种非常有用的命令。通过该命令DB2可以根据优化器的配置以及机器性能给出提高查询性能的建议。这种建议主要集中于如何创建索引,这些索引可以降低多少查询代价,需要创建哪些表或者 Materialized Query Table(MQT) 等。

db2advis结合db2top重放抓取top5 sql语句可以使用db2advis进行简单的分析

前面工具和命令只提供了查询的估算代价,有些时候估算代价和实际的执行时间并不是完全呈线形关系,有必要实际执行这些查询。db2batch就是这样一个 Benchmark工具,它能够提供从准备到查询完成中各个阶段所花费地具体时间,CPU时间,以及返回的记录。

db2expln/db2advis的使用

Cars表和数据

cars.ddl

----cars.ddl 2000 rows for car
 
DROP TABLE CARS;

CREATE TABLE CARS
(
	ID CHAR(13) FOR BIT DATA NOT NULL PRIMARY KEY,
	TYPE CHAR(10) NOT NULL,
	STATE SMALLINT NOT NULL CHECK (STATE BETWEEN 1 AND 100)
)
NOT LOGGED INITIALLY;


INSERT INTO CARS (ID, TYPE, STATE)
WITH TEMP (COUNTER, ID, TYPE, STATE) AS
(   VALUES (0, GENERATE_UNIQUE(), 'A', 100)
	UNION ALL
	SELECT
		(COUNTER + 1),
		GENERATE_UNIQUE(),
		CHR(MOD(INT(RAND() * 1000), 10) + 65),
		100
	FROM TEMP
	WHERE
		(COUNTER + 1) < 1000
)
SELECT  ID, TYPE, STATE
FROM  TEMP;

INSERT INTO CARS (ID, TYPE, STATE)
WITH TEMP (COUNTER, ID, TYPE, STATE) AS
(
	VALUES (0, GENERATE_UNIQUE(), 'A', 1)
	UNION ALL
	SELECT
		(COUNTER + 1),
		GENERATE_UNIQUE(),
		CHR(MOD(INT(RAND() * 1000), 10) + 65),
		MOD(INT(RAND() * 1000), 99) + 1
	FROM
		TEMP
	WHERE
		(COUNTER + 1) < 1000
)
SELECT
	ID, TYPE, STATE
FROM
	TEMP
;

COMMIT;

db2 -tvf cars.ddl

查询部分结果:

db2 “select * from cars where state<10 and type=’A’”;

dbv97i01:/dbhome/dbv97i01$db2 "select * from cars where state<10 and type='A'";

ID                            TYPE       STATE 
----------------------------- ---------- ------
x'20160226081742243849000000' A               1
x'20160226081742244208000000' A               8
x'20160226081742244230000000' A               9
x'20160226081742244315000000' A               6
x'20160226081742244985000000' A               2
x'20160226081742245358000000' A               8
x'20160226081742245459000000' A               7
x'20160226081742245465000000' A               8
x'20160226081742245708000000' A               1
x'20160226081742249518000000' A               6
x'20160226081742249575000000' A               5
x'20160226081742249637000000' A               8
x'20160226081742249927000000' A               6
x'20160226081742250612000000' A               3
x'20160226081742253942000000' A               5
x'20160226081742254800000000' A               7

  16 record(s) selected.

dbv97i01:/dbhome/dbv97i01$
测试的sql语句

dbv97i01:/dbhome/dbv97i01$db2 “select * from cars where ID=TO_CHAR(x’20160226081742243849000000’)”

ID                            TYPE       STATE 
----------------------------- ---------- ------
x'20160226081742243849000000' A               1

  1 record(s) selected.

dbv97i01:/dbhome/dbv97i01$

test01.sql

select * from cars where ID=TO_CHAR(x'20160226081742243849000000');
执行计划:
db2 runstats on table cars with distribution and detailed indexes all;

db2expln -database sample -stmtfile test01.sql -terminator ";" -terminal -g 

or

db2expln -d sample -q "select * from cars where ID=TO_CHAR(x'20160226081742243849000000')" -g -t

Log:

Optimizer Plan:

				 Rows   
			   Operator 
				 (ID)   
				 Cost   
					   
				  1    
			   RETURN  
				( 1)   
			   13.6319 
				 |     
				  1    
				FETCH  
				( 2)   
			   13.6319 
			  /       \
		  1            2000   
	   IXSCAN        Table:   
		( 3)         DBV97I01 
	   6.82237       CARS     
		 |          
		2000        
 Index:             
 SYSIBM             
 SQL160226171742170 

说明是自动创建了个index

另外一个sql

另外一个sql语句的执行计划和db2advis: select * from cars where state<100 and type=’A’

dbv97i01:/dbhome/dbv97i01$db2expln -d sample -q "select * from cars where state<100 and type='A'" -g -t

Optimizer Plan:

   Rows   
 Operator 
   (ID)   
   Cost   
		 
   99.5  
  RETURN 
   ( 1)  
  62.845 
	|    
   99.5  
  TBSCAN 
   ( 2)  
  62.845 
	|     
   2000   
 Table:   
 DBV97I01 
 CARS     

执行了表扫描

db2advis index推荐工具:

db2 runstats on table cars with distribution and detailed indexes all;

cd ~/sqllib/misc

db2 -tvf EXPLAIN.DDL

db2advis -d sample -i test01.sql -delim “;”

dbv97i01:/dbhome/dbv97i01$db2 runstats on table cars with distribution and detailed indexes all;
DB20000I  The RUNSTATS command completed successfully.
dbv97i01:/dbhome/dbv97i01$db2advis -d sample -i test01.sql -delim ";"

Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2016-02-26-17.00.57.328368
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [   0.044] MB
total disk space constrained to         [  35.434] MB
Trying variations of the solution set.
  1  indexes in current solution
 [ 63.0000] timerons  (without recommendations)
 [  7.0000] timerons  (with current solution)
 [88.89%] improvement


--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1],    0.044MB
   CREATE INDEX "DBV97I01"."IDX1602260801010" ON "DBV97I01"."CARS"
   ("TYPE" ASC, "STATE" ASC, "ID" ASC) ALLOW REVERSE
   SCANS COLLECT SAMPLED DETAILED STATISTICS;
   COMMIT WORK ;


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================


--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--

-- ====ADVISOR DETAILED XML OUTPUT=============
-- ==(Benefits do not include clustering recommendations)==
--

--<?xml version="1.0"?>
--<design-advisor>
--<index>
--<identifier>
--<name>IDX1602260801010</name>
--<schema>DBV97I01</schema>
--</identifier>
--<table><identifier>
--<name>CARS</name>
--<schema>DBV97I01</schema>
--</identifier></table>
--<statementlist>0</statementlist>
--<benefit>56.000000</benefit>
--<overhead>0.000000</overhead>
--<diskspace>0.043969</diskspace>
--</index>
--<statement>
--<statementnum>0</statementnum>
--<statementtext>
-- select * from cars where state<100 and type='A'
--</statementtext>
--<objects>
--<identifier>
--<name>CARS</name>
--<schema>DBV97I01</schema>
--</identifier>
--<identifier>
--<name>IDX1602260801010</name>
--<schema>DBV97I01</schema>
--</identifier>
--</objects>
--<benefit>56.000000</benefit>
--<frequency>1</frequency>
--</statement>
--</design-advisor>

-- ====ADVISOR DETAILED XML OUTPUT=============
--

14 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.
dbv97i01:/dbhome/dbv97i01$

cars_indx.ddl

CREATE INDEX CARS.I_TYPE ON CARS (TYPE ASC) ALLOW REVERSE SCANS@
CREATE INDEX CARS.I_STATE ON CARS (STATE ASC) ALLOW REVERSE SCANS@

db2 -td@ -vf cars_indx.ddl

dbv97i01:/dbhome/dbv97i01$cat cars_indx.ddl                                                                     
CREATE INDEX CARS.I_TYPE ON CARS (TYPE ASC) ALLOW REVERSE SCANS@
CREATE INDEX CARS.I_STATE ON CARS (STATE ASC) ALLOW REVERSE SCANS@

dbv97i01:/dbhome/dbv97i01$db2 -td@ -vf cars_indx.ddl 
CREATE INDEX CARS.I_TYPE ON CARS (TYPE ASC) ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.

CREATE INDEX CARS.I_STATE ON CARS (STATE ASC) ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.

dbv97i01:/dbhome/dbv97i01$

db2 runstats on table cars with distribution and detailed indexes all;

db2expln -d sample -q “select * from cars where state<100 and type=’A’” -g -t

Optimizer Plan:

Optimizer Plan:

		Rows   
	  Operator 
		(ID)   
		Cost   
			  
	   99.5   
	  RETURN  
	   ( 1)   
	  15.8646 
		|     
	   99.5   
	   FETCH  
	   ( 2)   
	  15.8646 
	 /       \
   199      2000   
 IXSCAN   Table:   
  ( 3)    DBV97I01 
 6.92601  CARS     
   |    
   10   
 Index: 
 CARS   
 I_TYPE 

COST:62.845 -> 15.8646

db2advis的常用语法:

db2advis -d <DATABASE NAME> -i <INPUT FILENAME> -t <TIME (Mins)> > <OUTPUT FILENAME>

-t:限制评估时间

db2advis -d <DATABASE NAME> -i <INPUT FILENAME> -t <TIME (Mins)> -m <Options M,C,P,I> > <OUTPUT FILENAME>

-m的参数:

I index

C MDC

M MQT

P Partition

db2advis -d SAMPLE -i TROUBLE_QUERY_IN -t 5 -m ICM -l 1000 > TROUBLE_QUERY_OUT_1

-l 限制大小

db2batch基准测试

db2batch -d sample -f test01.sql

* 104 row(s) fetched, 104 row(s) output.

* Elapsed Time is:       0.014443 seconds

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.014443       0.014443       0.014443        0.014443       0.014443            104           104

* Total Entries:              1
* Total Time:                 0.014443 seconds
* Minimum Time:               0.014443 seconds
* Maximum Time:               0.014443 seconds
* Arithmetic Mean Time:       0.014443 seconds
* Geometric Mean Time:        0.014443 seconds
---------------------------------------------
* Timestamp: Fri Feb 26 2016 17:33:17 JST

db2batch -d sample -f test01.sql -o r 0 p 2

上面参数会有更多结果


参考资料

DB2V10.5 db2expln

DB2V10.5 db2advis


Comments