db2expln_db2advis操作记录
原语句如下，执行两边merge 更新操作时间很慢：
merge into maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103 a USING
maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M b ON a.user_id=b.user_id and
b.svc_type_cde in('gh','gp','gi','go')
WHEN matched
THEN
UPDATE SET a.call_dur=b.item_val01,a.bll_dur=b.item_val13,
a.call_times=b.item_val03, a.to_call_dur=b.item_val02,
a.to_bll_dur=b.item_val14,a.to_call_times=b.item_val04,
a.loc_call_times=b.item_val05,a.loc_dur=b.item_val15,
a.loc_call_dur=b.item_val06, a.tlrm_times=b.item_val07,
a.tlrm_dur=b.item_val16,a.tlrm_call_dur=b.item_val08,
a.toll_call_times=b.item_val09,a.toll_dur=b.item_val17,
a.toll_call_dur=b.item_val10, a.rmlc_call_times=b.item_val11,
a.rmlc_dur=b.item_val18,a.rmlc_call_dur=b.item_val12
两表一个400万记录，一个表800万记录：
[DWE3:/home/maint1]time db2 "select count(*) from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M"
1
-----------
4546787
1 record(s) selected.
real 0m5.30s
user 0m0.02s
sys 0m0.01s
[DWE3:/home/maint1]time db2 "select count(*) from pdw.T_DWU_PAR_MOBILE_USER_STAT_M1103"
1
-----------
8116263
1 record(s) selected.
real 0m15.31s
user 0m0.02s
sys 0m0.02s
创建两张测试表：
[DWE3:/home/maint1]db2 "create table maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103 like pdw.T_DWU_PAR_MOBILE_USER_STAT_M1103 in tbs_prt"
DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]
使用游标方式复制数据到测试表
[DWE3:/home/maint1]db2 "declare c1 cursor for select * from PDW.T_DWU_PAR_MOBILE_USER_STAT_M1103"
DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]time db2 "load from c1 of cursor insert into maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103"
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 1 of 1 LOADs completed successfully.
______________________________________________________________________________
Summary of LOAD Agents:
Number of rows read = 8116263
Number of rows skipped = 0
Number of rows loaded = 8116263
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 8116263
real 2m34.40s
user 0m0.02s
sys 0m0.01s
[DWE3:/home/maint1]db2 "declare c2 cursor for select * from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M"
time db2 "load from c2 of cursor insert into maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M"DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]
[DWE3:/home/maint1]time db2 "load from c2 of cursor insert into maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M"
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 1 of 1 LOADs completed successfully.
______________________________________________________________________________
Summary of LOAD Agents:
Number of rows read = 4546787
Number of rows skipped = 0
Number of rows loaded = 4546787
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 4546787
real 0m27.32s
user 0m0.02s
sys 0m0.01s
[DWE3:/home/maint1]
[DWE3:/home/maint1]time db2 "select count(*) from maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103"
1
-----------
8116263
1 record(s) selected.
real 3m38.62s
user 0m0.02s
sys 0m0.03s
[DWE3:/home/maint1]time db2 "select count(*) from maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M"
1
-----------
4546787
1 record(s) selected.
real 0m23.75s
user 0m0.02s
sys 0m0.01s
使用执行计划分析工具前，需要创建执行计划说明表
[DWE3:/home/maint1]db2 -tvf /db2home/db2inst1/sqllib/misc/EXPLAIN.DDL
******* IMPORTANT **********
USAGE: db2 -tf EXPLAIN.DDL
******* IMPORTANT **********
UPDATE COMMAND OPTIONS USING C OFF
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
CREATE TABLE EXPLAIN_INSTANCE ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL, EXPLAIN_TIME TIMESTAMP NOT NULL, SOURCE_NAME VARCHAR(128) NOT NULL, SOURCE_SCHEMA VARCHAR(128) NOT NULL, SOURCE_VERSION VARCHAR(64) NOT NULL, EXPLAIN_OPTION CHAR(1) NOT NULL, SNAPSHOT_TAKEN CHAR(1) NOT NULL, DB2_VERSION CHAR(7) NOT NULL, SQL_TYPE CHAR(1) NOT NULL, QUERYOPT INTEGER NOT NULL, BLOCK CHAR(1) NOT NULL, ISOLATION CHAR(2) NOT NULL, BUFFPAGE INTEGER NOT NULL, AVG_APPLS INTEGER NOT NULL, SORTHEAP INTEGER NOT NULL, LOCKLIST INTEGER NOT NULL, MAXLOCKS SMALLINT NOT NULL, LOCKS_AVAIL INTEGER NOT NULL, CPU_SPEED DOUBLE NOT NULL, REMARKS VARCHAR(254), DBHEAP INTEGER NOT NULL, COMM_SPEED DOUBLE NOT NULL, PARALLELISM CHAR(2) NOT NULL, DATAJOINER CHAR(1) NOT NULL, PRIMARY KEY (EXPLAIN_REQUESTER, EXPLAIN_TIME, SOURCE_NAME, SOURCE_SCHEMA, SOURCE_VERSION))
DB20000I The SQL command completed successfully.
.............................
.............................
COMMIT WORK
DB20000I The SQL command completed successfully.
设置解释模式为explain，仅生成语句的说明信息，语句并不执行
[DWE3:/home/maint1]db2 set current explain mode explain
DB20000I The SQL command completed successfully.
执行语句
[DWE3:/home/maint1]db2 -tvf merge.sql
merge into maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103 a USING maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M b ON a.user_id=b.user_id and b.svc_type_cde in('gh','gp','gi','go') WHEN matched THEN UPDATE SET a.call_dur=b.item_val01,a.bll_dur=b.item_val13, a.call_times=b.item_val03, a.to_call_dur=b.item_val02, a.to_bll_dur=b.item_val14,a.to_call_times=b.item_val04, a.loc_call_times=b.item_val05,a.loc_dur=b.item_val15, a.loc_call_dur=b.item_val06, a.tlrm_times=b.item_val07, a.tlrm_dur=b.item_val16,a.tlrm_call_dur=b.item_val08, a.toll_call_times=b.item_val09,a.toll_dur=b.item_val17, a.toll_call_dur=b.item_val10, a.rmlc_call_times=b.item_val11, a.rmlc_dur=b.item_val18,a.rmlc_call_dur=b.item_val12
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
关闭说明模式
[DWE3:/home/maint1]db2 set explain mode no
格式化生成语句说明信息
[DWE3:/home/maint1]
[DWE3:/home/maint1]db2exfmt -db bssdb -g TIC -w -1 -n % -s % -# 0 -o merge2.txt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Connecting to the Database.
Connect to Database Successful.
Output is in merge2.txt.
Executing Connect Reset -- Connect Reset was Successful.
查看分析语句的访问计划
DB2优化器编译优化生成的语句
Optimized Statement:
-------------------
UPDATE MAINT1.T_DWU_PAR_MOBILE_USER_STAT_M1103 AS Q1 SET (Q1."RMLC_CALL_DUR",
Q1."RMLC_DUR", Q1."RMLC_CALL_TIMES", Q1."TOLL_CALL_DUR",
Q1."TOLL_DUR", Q1."TOLL_CALL_TIMES", Q1."TLRM_CALL_DUR",
Q1."TLRM_DUR", Q1."TLRM_TIMES", Q1."LOC_CALL_DUR", Q1."LOC_DUR",
Q1."LOC_CALL_TIMES", Q1."TO_CALL_TIMES", Q1."TO_BLL_DUR",
Q1."TO_CALL_DUR", Q1."CALL_TIMES", Q1."BLL_DUR", Q1."CALL_DUR") =
SELECT Q8.$C19, Q8.$C18, Q8.$C17, Q8.$C16, Q8.$C15, Q8.$C14, Q8.$C13,
Q8.$C12, Q8.$C11, Q8.$C10, Q8.$C9, Q8.$C8, Q8.$C7, Q8.$C6, Q8.$C5,
Q8.$C4, Q8.$C3, Q8.$C2,
CASE
WHEN Q8.$C1 IS NOT NULL
THEN 1
ELSE 0 END
FROM
(SELECT Q7.$C2, Q7.$C3, Q7.$C4, Q7.$C5, Q7.$C6, Q7.$C7, Q7.$C8, Q7.$C9,
Q7.$C10, Q7.$C11, Q7.$C12, Q7.$C13, Q7.$C14, Q7.$C15, Q7.$C16,
Q7.$C17, Q7.$C18, Q7.$C19
FROM
(SELECT Q6.$C2, Q6.$C3, Q6.$C4, Q6.$C5, Q6.$C6, Q6.$C7, Q6.$C8,
Q6.$C9, Q6.$C10, Q6.$C11, Q6.$C12, Q6.$C13, Q6.$C14, Q6.$C15,
Q6.$C16, Q6.$C17, Q6.$C18, Q6.$C19, ROWNUMBER() OVER
(PARTITION BY Q6.$C0)
FROM
(SELECT Q5.$C16, Q5.$C10, Q5.$C0, Q5.$C17, Q5.$C11, Q5.$C1,
Q5.$C2, Q5.$C12, Q5.$C3, Q5.$C4, Q5.$C13, Q5.$C5, Q5.$C6,
Q5.$C14, Q5.$C7, Q5.$C8, Q5.$C15, Q5.$C9
FROM
(SELECT Q2."ITEM_VAL03", Q2."ITEM_VAL04", Q2."ITEM_VAL05",
Q2."ITEM_VAL06", Q2."ITEM_VAL07", Q2."ITEM_VAL08",
Q2."ITEM_VAL09", Q2."ITEM_VAL10", Q2."ITEM_VAL11",
Q2."ITEM_VAL12", Q2."ITEM_VAL13", Q2."ITEM_VAL14",
Q2."ITEM_VAL15", Q2."ITEM_VAL16", Q2."ITEM_VAL17",
Q2."ITEM_VAL18", Q2."ITEM_VAL01", Q2."ITEM_VAL02"
FROM MAINT1.TMP_DWU_PAR_MOBILE_USER_STAT_M AS Q2 LEFT OUTER
JOIN
(SELECT $RID$
FROM MAINT1.T_DWU_PAR_MOBILE_USER_STAT_M1103 AS Q3
WHERE (Q3."USER_ID" = Q2."USER_ID") AND Q2."SVC_TYPE_CDE" IN
('gh', 'gp', 'gi', 'go')) AS Q4) AS Q5) AS Q6) AS Q7
WHERE (1 =
CASE
WHEN ((Q7.$C20 > 1) AND (Q7.$C0 IS NOT NULL AND Q7.$C1 IS NOT NULL))
THEN $INTERNAL_FUNC$()
ELSE 1 END SELECTIVITY 1.000000)) AS Q8
WHERE Q8.$C1 IS NOT NULL
分析访问计划会发现，优化后的语句最终通过对两表全表扫描，使用NLjoin（嵌套循环连接），排序，过滤，生成符合条件的结果集后，对T_DWU_PAR_MOBILE_USER_STAT_M1103表进行update操作，语句总的成本为9.03788e+11（CPU,IO等综合成本）
Access Plan:
-----------
Total Cost: 9.03788e+11
Query Degree: 1
Rows
RETURN
( 1)
FstTup
I/O
|
4.36547e+06
UPDATE
( 2)
9.03788e+11
1.60718e+12
/---------+--------
4.36547e+06 8.48229e+06
FETCH TABLE: MAINT1
( 3) T_DWU_PAR_MOBILE_USER_STAT_M1103
9.03722e+11 Q1
1.60718e+12
/---------+--------
4.36547e+06 8.48229e+06
FILTER TABLE: MAINT1
( 4) T_DWU_PAR_MOBILE_USER_STAT_M1103
9.03722e+11
1.60717e+12
|
4.54737e+06
FILTER
( 5)
9.03722e+11
1.60717e+12
|
4.54737e+06
TBSCAN
( 6)
9.03722e+11
1.60717e+12
|
4.54737e+06
SORT
( 7)
9.03722e+11
1.60717e+12
|
4.54737e+06
>NLJOIN
( 8)
198743
1.60717e+12
/-------------+-------------
4.54737e+06 0.298451
TBSCAN TBSCAN
( 9) ( 10)
7.58484 198735
61451 353429
| |
4.54737e+06 8.48229e+06
TABLE: MAINT1 TABLE: MAINT1
TMP_DWU_PAR_MOBILE_USER_STAT_M T_DWU_PAR_MOBILE_USER_STAT_M1103
Q2 Q3
附加的诊断信息会提示此两表都没有收集统计信息，可能影响性能：
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0020W Table has no statistics. The table
"MAINT1 "."TMP_DWU_PAR_MOBILE_USER_STAT_M" has not
had runstats run on it. This may result in a
sub-optimal access plan and poor performance.
Diagnostic Identifier: 2
Diagnostic Details: EXP0020W Table has no statistics. The table
"MAINT1 "."T_DWU_PAR_MOBILE_USER_STAT_M1103" has
not had runstats run on it. This may result in a
sub-optimal access plan and poor performance.
格式化的说明文件中，对每一步访问方案步骤有详细的说明信息
对两表的user_id列创建索引，对表收集统计信息
[DWE3:/home/maint1]db2 "create index maint1.t_userid on maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103(user_id)"
DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]db2 "create index maint1.tmp_userid on maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M(user_id)"
DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]
[DWE3:/home/maint1]
[DWE3:/home/maint1]db2 "runstats on table maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M for indexes all allow read access tablesample system (5)"
DB20000I The RUNSTATS command completed successfully.
[DWE3:/home/maint1]
[DWE3:/home/maint1]db2 "runstats on table maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103 for indexes all allow read access tablesample system (5)"
DB20000I The RUNSTATS command completed successfully.
重新生成新的语句说明信息
[DWE3:/home/maint1]db2 set current explain mode explain
DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]db2 -tvf merge.sql
merge into maint1.T_DWU_PAR_MOBILE_USER_STAT_M1103 a USING maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M b ON a.user_id=b.user_id and b.svc_type_cde in('gh','gp','gi','go') WHEN matched THEN UPDATE SET a.call_dur=b.item_val01,a.bll_dur=b.item_val13, a.call_times=b.item_val03, a.to_call_dur=b.item_val02, a.to_bll_dur=b.item_val14,a.to_call_times=b.item_val04, a.loc_call_times=b.item_val05,a.loc_dur=b.item_val15, a.loc_call_dur=b.item_val06, a.tlrm_times=b.item_val07, a.tlrm_dur=b.item_val16,a.tlrm_call_dur=b.item_val08, a.toll_call_times=b.item_val09,a.toll_dur=b.item_val17, a.toll_call_dur=b.item_val10, a.rmlc_call_times=b.item_val11, a.rmlc_dur=b.item_val18,a.rmlc_call_dur=b.item_val12
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
[DWE3:/home/maint1]db2 set current explain mode no
DB20000I The SQL command completed successfully.
[DWE3:/home/maint1]db2exfmt -db bssdb -g TIC -w -1 -n % -s % -# 0 -o merge3.txt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Connecting to the Database.
Connect to Database Successful.
Output is in merge3.txt.
Executing Connect Reset -- Connect Reset was Successful.
新的访问计划中，NLjoin连接中，使用了表的USER_ID列索引，总的成本降到了6.62538e+07
Access Plan:
-----------
Total Cost: 6.62392e+07
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4.36492e+06
UPDATE
( 2)
6.62392e+07
8.80179e+06
/---------+--------
4.36492e+06 8.11626e+06
FETCH TABLE: MAINT1
( 3) T_DWU_PAR_MOBILE_USER_STAT_M1103
3.32212e+07 Q1
4.43687e+06
/---------+--------
4.36492e+06 8.11626e+06
FILTER TABLE: MAINT1
( 4) T_DWU_PAR_MOBILE_USER_STAT_M1103
200043
71958.8
|
4.54679e+06
FILTER
( 5)
198991
71958.8
|
4.54679e+06
TBSCAN
( 6)
189556
71958.8
|
4.54679e+06
SORT
( 7)
188965
71958.8
|
4.54679e+06
>NLJOIN
( 8)
176250
71958.8
/------------+------------
4.54679e+06 0.5
TBSCAN FETCH
( 9) ( 10)
36681.4 18.9384
61451 2.5
| /--------+-------
4.54679e+06 0.5 8.11626e+06
TABLE: MAINT1 IXSCAN TABLE: MAINT1
TMP_DWU_PAR_MOBILE_USER_STAT_M ( 11) T_DWU_PAR_MOBILE_USER_STAT_M1103
Q2 15.1554 Q3
2
|
8.11626e+06
INDEX: MAINT1
T_USERID
Q3
还可以通过db2建议工具生成优化调整建议
[DWE3:/home/maint1]db2advis -d bssdb -i ./merge.sql -n maint1 -o ./merge_db2advis.out
execution started at timestamp 2011-04-15-17.25.28.448576
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 0.000] MB
total disk space constrained to [2159490.901] MB
Trying variations of the solution set.
0 indexes in current solution
[66239196.0000] timerons (without recommendations)
[66239196.0000] timerons (with current solution)
[0.00%] improvement
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- no indexes are recommended for this workload.
--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "MAINT1 "."T_DWU_PAR_MOBILE_USER_STAT_M1103" FOR INDEX "MAINT1 "."T_USERID" ;
-- COMMIT WORK ;
--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "MAINT1 "."TMP_USERID";
-- ===========================
--
3 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.
一、 查看DB2执行计划
1. 创建执行计划表注意账户db2管理员帐户
db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL
注意：在你使用的Linux账户的目录下可以找到“/sqllib/misc/EXPLAIN.DDL”这个文件
2. 清除生成执行计划记录
db2 delete from db2inst2.explain_instance;
3. 生成新的执行计划
db2 "EXPLAIN PLAN FOR select statment"
注意：“select statement”是你要执行的SQL语句
4. 查看生成执行计划
db2 "SELECT O.Operator_ID, S2.Target_ID, O.Operator_Type,
S.Object_Name, CAST(O.Total_Cost AS INTEGER) Cost
FROM db2inst2.EXPLAIN_OPERATOR O
LEFT OUTER JOIN db2inst2.EXPLAIN_STREAM S2
ON O.Operator_ID=S2.Source_ID
LEFT OUTER JOIN db2inst2.EXPLAIN_STREAM S
ON O.Operator_ID = S.Target_ID
AND O.Explain_Time = S.Explain_Time
AND S.Object_Name IS NOT NULL
ORDER BY O.Explain_Time ASC, Operator_ID ASC";
得到的结果如下，具体的的情况要根据你的select语句有所不同：
5. 清除生成的执行计划
db2 ROLLBACK
二、 使用db2advis查看单条SQL语句建议
1. 使用命令
db2advis -d pbwsdb –s " select statement " -o output.out
注意：" select statement "是你要执行的SQL语句
例子如下：
|
./db2advis -d pbwsdb -s "select * from (select IO.ID, IO.IND_BANK_NAME, IO.IND_ID, IO.IND_NAME, IO.IND_EXPLANATION, IO.IND_UNIT, IO.DATA_SOURCE, IO.IND_YEAR, IO.IND_BANK_NAME, IO.IND_IMPORTANCE, IO.REMARK, IO.IND_VALUE, ROW_NUMBER() over() as ROWNUM from PBWS.T_PBWS_INDICATOROPERATER IO where 1 = 1 and IO.IND_BANK_ID in (3008, 3029, 3030, 3031, 3032, 3033) and IO.DELSTATE = 0 and IO.IND_YEAR = '2009' and exists (select 1 from PBWS.T_PBWS_INDICATORREPORT IR where IR.DELSTATE = 0 and IR.BANK_ID = IO.IND_BANK_ID and IR.ISSUE_STATE = 25) and ((IO.IND_TYPE_ID in (4)) and (IO.IND_TABLE_ID in (23)) and (IO.IND_ID in (2517, 2518, 2519, 2520, 2521, 2522, 2523, 2524, 2525, 2526, 2527, 2528, 2529, 2530, 2531, 2532, 2533, 2534, 2535)))) a where a.rownum between 1 and 10 order by a.IND_ID" -o output.out |
2. 分析结果
|
Using user id as default schema name. Use -n option to specify schema execution started at timestamp 2013-05-14-15.35.43.393083 Recommending indexes... total disk space needed for initial set [ 76.950] MB total disk space constrained to [ 379.892] MB Trying variations of the solution set. Optimization finished. 2 indexes in current solution [399750.6250] timerons (without recommendations) [269.2212] timerons (with current solution) [99.93%] improvement
-- -- -- LIST OF RECOMMENDED INDEXES -- =========================== -- index[1], 76.950MB CREATE INDEX "DB2INST2"."IDX305140736040000" ON "PBWS "."T_PBWS_INDICATOROPERATER" ("IND_TABLE_ID" ASC, "IND_TYPE_ID" ASC, "IND_YEAR" ASC, "DELSTATE" ASC, "IND_ID" ASC, "IND_BANK_ID" ASC) ALLOW REVERSE SCANS ; COMMIT WORK ; RUNSTATS ON TABLE "PBWS "."T_PBWS_INDICATOROPERATER" FOR INDEX "DB2INST2"."IDX305140736040000" ; COMMIT WORK ;
-- -- -- RECOMMENDED EXISTING INDEXES -- ============================ -- RUNSTATS ON TABLE "PBWS "."T_PBWS_INDICATORREPORT" FOR INDEX "pbws "."IDX305140540340000" ; -- COMMIT WORK ; -- RUNSTATS ON TABLE "PBWS "."T_PBWS_INDICATOROPERATER" FOR INDEX "PBWS "."INDX_INDICATOROPT_IND_ID" ; -- COMMIT WORK ;
-- -- -- UNUSED EXISTING INDEXES -- ============================ -- DROP INDEX "PBWS "."INDX_INDICATOROPT_ASSGINSTATEID"; -- DROP INDEX "PBWS "."INDX_INDICATOROPT_BANKID"; -- DROP INDEX "PBWS "."INDX_INDICATOROPT_INDSTATE"; -- =========================== --
10 solutions were evaluated by the advisor DB2 Workload Performance Advisor tool is finished. |
优化结果可以分为4个部分
i. 磁盘的消耗和优化后性能的提升幅度
|
total disk space needed for initial set [ 76.950] MB total disk space constrained to [ 379.892] MB Trying variations of the solution set. Optimization finished. 2 indexes in current solution [399750.6250] timerons (without recommendations) [269.2212] timerons (with current solution) [99.93%] improvement |
这里显示了磁盘的消耗以及改良的效率，这里指效率提升了99.93%
ii. 建议增加的索引
|
-- LIST OF RECOMMENDED INDEXES -- =========================== -- index[1], 76.950MB CREATE INDEX "DB2INST2"."IDX305140736040000" ON "PBWS "."T_PBWS_INDICATOROPERATER" ("IND_TABLE_ID" ASC, "IND_TYPE_ID" ASC, "IND_YEAR" ASC, "DELSTATE" ASC, "IND_ID" ASC, "IND_BANK_ID" ASC) ALLOW REVERSE SCANS ; COMMIT WORK ; RUNSTATS ON TABLE "PBWS "."T_PBWS_INDICATOROPERATER" FOR INDEX "DB2INST2"."IDX305140736040000" ; COMMIT WORK ; |
这里给出了优化的建议，上面给出了优化的脚本，增加一个索引。
iii. 建议保留的索引
|
-- RECOMMENDED EXISTING INDEXES -- ============================ -- RUNSTATS ON TABLE "PBWS "."T_PBWS_INDICATORREPORT" FOR INDEX "pbws "."IDX305140540340000" ; -- COMMIT WORK ; -- RUNSTATS ON TABLE "PBWS "."T_PBWS_INDICATOROPERATER" FOR INDEX "PBWS "."INDX_INDICATOROPT_IND_ID" ; -- COMMIT WORK ; |
这里是建议保留的索引，即上述SQL语句执行时所用的索引。
iv. 未使用的索引
|
-- UNUSED EXISTING INDEXES -- ============================ -- DROP INDEX "PBWS "."INDX_INDICATOROPT_ASSGINSTATEID"; -- DROP INDEX "PBWS "."INDX_INDICATOROPT_BANKID"; -- DROP INDEX "PBWS "."INDX_INDICATOROPT_INDSTATE"; -- =========================== |
这里是在上述SQL语句中没有使用的索引，你可以评估一下如果没有其他SQL语句使用到可以考虑删除这些无用的索引。
三、 使用db2advis查看多条SQL语句建议
1、 准备要执行的SQL脚本
Sql脚本文件名：input.sql
|
--#SET FREQUENCY 100 SELECT id as a_id, state_name as a_stateName FROM pbws.t_pbws_state WHERE state_type = 'indUnit';
--#SET FREQUENCY 10 UPDATE pbws.t_pbws_indicatoroperater SET ind_unit_id = 40 WHERE ind_year = '2009' AND ind_bank_id = 3008 AND ind_unit = '1';
--#SET FREQUENCY 1 SELECT id as a_id, ind_name as a_name, ind_type_id as a_typeId, ind_table_id as a_tableId, cast(ind_unit as int) as unitID FROM pbws.t_pbws_indicatorinfo; |
“FREQUENCY”表示的是执行这个SQL语句的频率。
2、 执行命令
db2advis -d pbwsdb –i input.sql -o output.out
注意：" input.sql "是你要执行的SQL脚本文件
3. 分析结果
分析结果同单条SQL语句的结果，只是在建议中会权衡多条语句并给出相应的建议。
"aa.sql" 1 line, 54 characters
select * from db2inst2.employee where workdept='C01'；
$ db2expln -d sample -f aa.sql -t
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Intra-Partition Parallel = No
SQL Statement:
select *
from db2inst2.employee
where workdept='C01'
Section Code Page = 819
Estimated Cardinality = 4.000000
| Index Scan: Name = DB2INST2.IDX605250941000000 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: WORKDEPT (Ascending)
| | | 2: COMM (Ascending)
| | | 3: BONUS (Ascending)
| | | 4: SALARY (Ascending)
| | | 5: BIRTHDATE (Ascending)
| | | 6: SEX (Ascending)
| | | 7: EDLEVEL (Ascending)
| | | 8: JOB (Ascending)
| | | 9: HIREDATE (Ascending)
| | | 10: PHONENO (Ascending)
| | | 11: LASTNAME (Ascending)
| | | 12: MIDINIT (Ascending)
| | | 13: FIRSTNME (Ascending)
| | | 14: EMPNO (Ascending)
| #Columns = 13
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 'C01'
| | Stop Key: Inclusive Value
| | | | 1: 'C01'
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Return Data to Application
| | | #Columns = 14
Return Data Completion
$ set -o vi
$ db2expln -d sample -f aa.sql -t -g
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Intra-Partition Parallel = No
SQL Statement:
select *
from db2inst2.employee
where workdept='C01'
Section Code Page = 819
Estimated Cardinality = 4.000000
| Index Scan: Name = DB2INST2.IDX605250941000000 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: WORKDEPT (Ascending)
| | | 2: COMM (Ascending)
| | | 3: BONUS (Ascending)
| | | 4: SALARY (Ascending)
| | | 5: BIRTHDATE (Ascending)
| | | 6: SEX (Ascending)
| | | 7: EDLEVEL (Ascending)
| | | 8: JOB (Ascending)
| | | 9: HIREDATE (Ascending)
| | | 10: PHONENO (Ascending)
| | | 11: LASTNAME (Ascending)
| | | 12: MIDINIT (Ascending)
| | | 13: FIRSTNME (Ascending)
| | | 14: EMPNO (Ascending)
| #Columns = 13
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 'C01'
| | Stop Key: Inclusive Value
| | | | 1: 'C01'
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Return Data to Application
| | | #Columns = 14
Return Data Completion
Optimizer Plan:
( 1)
|
IXSCAN
( 2)
/ -
Index: Table:
DB2INST2 DB2INST2
IDX605250941000000 EMPLOYEE
3.对该sql语句解析并保存执行计划
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Intra-Partition Parallel = No
SQL Statement:
select *
from db2inst2.employee
where workdept='C01'
Section Code Page = 819
Estimated Cardinality = 4.000000
| Index Scan: Name = DB2INST2.IDX605250941000000 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: WORKDEPT (Ascending)
| | | 2: COMM (Ascending)
| | | 3: BONUS (Ascending)
| | | 4: SALARY (Ascending)
| | | 5: BIRTHDATE (Ascending)
| | | 6: SEX (Ascending)
| | | 7: EDLEVEL (Ascending)
| | | 8: JOB (Ascending)
| | | 9: HIREDATE (Ascending)
| | | 10: PHONENO (Ascending)
| | | 11: LASTNAME (Ascending)
| | | 12: MIDINIT (Ascending)
| | | 13: FIRSTNME (Ascending)
| | | 14: EMPNO (Ascending)
| #Columns = 13
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 'C01'
| | Stop Key: Inclusive Value
| | | | 1: 'C01'
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Return Data to Application
| | | #Columns = 14
Return Data Completion
Optimizer Plan:
( 1)
|
IXSCAN
( 2)
/ -
Index: Table:
DB2INST2 DB2INST2
IDX605250941000000 EMPLOYEE
$ db2advis -d sample -i aa.sql -n db2inst2 -t 0
execution started at timestamp 2006-05-25-17.35.07.600281
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 0.021] MB
total disk space constrained to [ 8.901] MB
Trying variations of the solution set.
Optimization finished.
1 indexes in current solution
[ 26.0000] timerons (without recommendations)
[ 13.0000] timerons (with current solution)
[50.00%] improvement
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 0.021MB
CREATE INDEX "DB2INST2"."IDX605250935100000" ON "DB2INST2"."EMPLOYEE" ("WORKDEPT" ASC, "COMM" ASC, "BONUS" ASC, "SALARY" ASC, "BIRTHDATE" ASC, "SEX" ASC, "EDLEVEL" ASC, "JOB" ASC, "HIREDATE" ASC, "PHONENO" ASC, "LASTNAME" ASC, "MIDINIT" ASC, "FIRSTNME" ASC, "EMPNO" ASC) ALLOW REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "DB2INST2"."EMPLOYEE" FOR INDEX "DB2INST2"."IDX605250935100000" ;
COMMIT WORK ;
--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--
DB2 Workload Performance Advisor tool is finished.
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞0作者其他文章
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 0
添加新评论0 条评论