摘要
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
上面参数会有更多结果