DB2优化计划

字数 11008阅读 2986评论 1赞 0

<DIV>
DB2优化计划
一、     查看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语句
 
<SPAN style="FONT-FAMILY: 华文楷体; COLOR: black; mso-bidi-font-size: 10.5pt; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-themecolor: text1">例子如下：
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 42pt; WIDTH: 409.95pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0 width=547>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 409.95pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=547>
./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</TD></TR></TBODY></TABLE>
 
2.        分析结果
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 42pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 426.1pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
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.</TD></TR></TBODY></TABLE>
 
优化结果可以分为4个部分
                        i.              磁盘的消耗和优化后性能的提升幅度
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 63pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 426.1pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
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</TD></TR></TBODY></TABLE>
                     这里显示了磁盘的消耗以及改良的效率，这里改良的效率是99.93%
                      ii.              建议增加的索引
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 63pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 426.1pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
-- 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 ;</TD></TR></TBODY></TABLE>
这里给出了优化的建议，上面给出了优化的脚本，增加一个索引。
                    iii.              建议保留的索引
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 63pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 426.1pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
-- 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 ;</TD></TR></TBODY></TABLE>
这里是建议保留的索引，即上述SQL语句执行时所用的索引。
                    iv.              未使用的索引
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 63pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 426.1pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "PBWS    "."INDX_INDICATOROPT_ASSGINSTATEID";
-- DROP INDEX "PBWS    "."INDX_INDICATOROPT_BANKID";
-- DROP INDEX "PBWS    "."INDX_INDICATOROPT_INDSTATE";
-- ===========================</TD></TR></TBODY></TABLE>
 
这里是在上述SQL语句中没有使用的索引，你可以评估一下如果没有其他SQL语句使用到可以考虑删除这些无用的索引。
 
三、     使用db2advis查看多条SQL语句建议
1、          准备要执行的SQL脚本
Sql脚本文件名：input.sql
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 57pt; BORDER-COLLAPSE: collapse; BACKGROUND: #e5e5e5; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-shading: windowtext; mso-pattern: gray-10 auto; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 369.1pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=492>
--#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;</TD></TR></TBODY></TABLE>
“FREQUENCY”表示的是执行这个SQL语句的频率。
2、          执行命令
db2advis -d pbwsdb –i input.sql -o output.out
 
注意：" input.sql "是你要执行的SQL脚本文件
3.        分析结果
分析结果同单条SQL语句的结果，只是在建议中会权衡多条语句并给出相应的建议。
 </DIV>

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论1 条评论

fengsh系统工程师电信行业
2011-06-13 10:15

不错

Ctrl+Enter 发表

匿名评论

DB2优化计划

添加新评论1 条评论

作者其他文章