前两天处理了一个很常见的存储过程执行慢的问题，执行计划不对，rebind包

字数 23187阅读 8895评论 17赞 5

<DIV><STRONG>应用同事反映但是对应到执行存储过程，执行了2，3个小时了，还没出来结果。</STRONG></DIV>
<DIV><STRONG>存储过程主要是执行一条update sql语句，单独将语句拿出来，clp命令行执行很快，2－3s即可执行完成。</STRONG></DIV>
<DIV> </DIV>
<DIV>执行的SP： 
<DIV>call pdw.P_OCS_ACTIVE_UPDATE('20120304',?) </DIV>
<DIV> </DIV>
<DIV><STRONG>存储过程主要业务SQL：</STRONG></DIV>
<DIV>
<DIV> /***********************开始实现业务******************************/</DIV>
<DIV>   /*******/</DIV>
<DIV>SET vn_Step=1;--</DIV>
<DIV>call papp.p_debug(txdate,vn_PrcName,vn_Step,vv_sql); --</DIV>
<DIV><STRONG><FONT color=#f00000>  UPDATE PODS.T_ODS_PAR_CUSTMSG a</FONT></STRONG></DIV>
<DIV><STRONG><FONT color=#f00000>  SET run_code='UU'</FONT></STRONG></DIV>
<DIV><STRONG><FONT color=#f00000>WHERE sm_code in ('o3','os','om','ol','ox') AND NOT EXISTS</FONT></STRONG></DIV>
<DIV><STRONG><FONT color=#f00000>  (SELECT * FROM PODS.T_ODS_PAR_OCSACTIVEMSG b where a.ID_NO=b.ID_NO );</FONT></STRONG></DIV>
<DIV> </DIV>
<DIV>COMMIT;--</DIV>
<DIV> /************************结束业务逻辑******************************/</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><STRONG>根据了解，这个存储过程有些时候了，最近无改动，以前都正常。</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV>
<DIV><STRONG>根据具体情况，第一反应是这个存储过程中语句的执行计划不正确了。静态sql的访问计划是在第一次编译后存储在数据库的包中的，之后运行都是使用包中的执行计划。解决办法很简单，对存储过程包重新绑定一下，使用最新的数据库统计信息生成最新的访问计划。</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>先查出此存储过程对应的包：</STRONG></DIV>
<DIV>SELECT   bname,<BR>         pkgname,<BR>         BSCHEMA<BR>FROM     syscat.packagedep<BR>WHERE    btype='T'<BR>AND      pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='P_OCS_ACTIVE_UPDATE'<BR>        AND PROCSCHEMA='PDW'))<BR></DIV>
<DIV>[DWE3:/tmp]db2 "SELECT   bname,<BR>>          pkgname,<BR>>          BSCHEMA<BR>> FROM     syscat.packagedep<BR>> WHERE    btype='T'<BR>> AND      pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='P_OCS_ACTIVE_UPDATE'<BR>>         AND PROCSCHEMA='PDW'))<BR>> "</DIV>
<DIV>BNAME                                                                                                                            PKGNAME                                                                                                                          BSCHEMA                                                                                                                         <BR>-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------<BR>T_ODS_PAR_CUSTMSG                                                                                                                P8414315                                                                                                                         PODS                                                                                                                            <BR>T_ODS_PAR_OCSACTIVEMSG                                                                                                           P8414315                                                                                                                         PODS                                                                                                                            </DIV>
<DIV>  2 record(s) selected.<BR><BR>对包执行rebind，操作前需连库</DIV>
<DIV>[DWE3:/tmp]time db2 rebind package pdw.P8414315<BR>DB20000I  The REBIND PACKAGE command completed successfully.</DIV>
<DIV>real    0m0.63s<BR>user    0m0.02s<BR>sys     0m0.00s</DIV>
<DIV> </DIV>
<DIV><STRONG>对存储过程包重新绑定后，存储过程几秒钟即执行完成，恢复正常。</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV> </DIV></DIV>
<DIV><STRONG>拿出sql单独执行计划：</STRONG></DIV>
<DIV><EM>[DWE3:/newdb2home/db2inst3/fengsh]more 2.out<BR>DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006<BR>Licensed Material - Program Property of IBM<BR>IBM DB2 Universal Database SQL and XQUERY Explain Tool</EM></DIV>
<DIV><EM>******************** DYNAMIC ***************************************</EM></DIV>
<DIV><EM>==================== STATEMENT ==========================================</EM></DIV>
<DIV><EM>        Isolation Level          = Cursor Stability<BR>        Blocking                 = Block Unambiguous Cursors<BR>        Query Optimization Class = 5</EM></DIV>
<DIV><EM>        Partition Parallel       = Yes<BR>        Intra-Partition Parallel = No</EM></DIV>
<DIV><EM>        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", <BR>                                   "DB2INST3"</EM></DIV>
<DIV><BR><EM>Statement:<BR>  <BR>  UPDATE PODS.T_ODS_PAR_CUSTMSG a SET run_code='UU' <BR>  WHERE sm_code in ('o3' , 'os' , 'om' , 'ol' , 'ox' )AND NOT EXISTS <BR>     (SELECT * <BR>     FROM PODS.T_ODS_PAR_OCSACTIVEMSG b <BR>     where a.ID_NO=b.ID_NO )</EM></DIV>
<DIV><BR><EM>Section Code Page = 1386</EM></DIV>
<DIV><EM>Estimated Cost = 1512832.000000<BR>Estimated Cardinality = 183703.968750</EM></DIV>
<DIV><EM>Coordinator Subsection - Main Processing:<BR>   Distribute Subsection #1<BR>   |  Broadcast to Node List<BR>   |  |  Nodes = 1, 2, 3, 4, 5, 6, 7</EM></DIV>
<DIV><EM>Subsection #1:<BR>   Access Table Name = PODS.T_ODS_PAR_OCSACTIVEMSG  ID = 11,3174<BR>   |  #Columns = 1<BR>   |  Skip Inserted Rows<BR>   |  Skip Deleted Rows<BR>   |  Relation Scan<BR>   |  |  Prefetch: Eligible<BR>   |  Lock Intents<BR>   |  |  Table: Intent Share<BR>   |  |  Row  : Next Key Share<BR>   |  Sargable Predicate(s)<BR>   |  |  Process Build Table for Hash Join<BR>   Anti Left Outer Hash Join<BR>   |  Early Out: Single Match Per Outer Row<BR>   |  Estimated Build Size: 7584000<BR>   |  Estimated Probe Size: 6960000<BR>   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155<BR>   |  |  #Columns = 3<BR>   |  |  Skip Inserted Rows<BR>   |  |  Skip Deleted Rows<BR>   |  |  Evaluate Block/Data Predicates Before Locking Row<BR>   |  |  Relation Scan<BR>   |  |  |  Prefetch: Eligible<BR>Isolation Level: Read Stability<BR>   |  |  Lock Intents<BR>   |  |  |  Table: Intent Exclusive<BR>   |  |  |  Row  : Update<BR>   |  |  Sargable Predicate(s)<BR>   |  |  |  #Predicates = 1<BR>   |  |  |  Process Probe Table for Hash Join<BR>   Establish Row Position<BR>   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155<BR>   Update:  Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155</EM></DIV>
<DIV><EM>End of section</EM></DIV>
<DIV><BR><EM>Optimizer Plan:</EM></DIV>
<DIV><EM>                                                  UPDATE <BR>                                                  (   2) <BR>                                       /---------/      <BR>                                 FETCH                 Table:            <BR>                                 (   3)                PODS              <BR>                        /-------/                     T_ODS_PAR_CUSTMSG <BR>                  HSJOIN            Table:            <BR>                  (   4)            PODS              <BR>             /---/      --        T_ODS_PAR_CUSTMSG <BR>       TBSCAN               TBSCAN <BR>       (   5)               (   6) <BR>         |                    |    <BR> Table:             Table:                 <BR> PODS               PODS                   <BR> T_ODS_PAR_CUSTMSG  T_ODS_PAR_OCSACTIVEMSG <BR></EM></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><STRONG>rebind后存储过程中此update语句执行计划：</STRONG></DIV>
<DIV><STRONG>db2expln -d newdssdb -g -c pdw -p P8414315 -s 0 -t>2.explain_rebind</STRONG></DIV>
<DIV><BR>-------------------- SECTION ---------------------------------------<BR>Section = 8</DIV>
<DIV><BR>Statement:<BR>  <BR>  UPDATE PODS.T_ODS_PAR_CUSTMSG A<BR>         SET RUN_CODE='UU' <BR>        WHERE <BR>          SM_CODE in ('o3' , 'os' , 'om' , 'ol' , 'ox' )AND NOT <BR>          EXISTS<BR>         <BR>     (SELECT * <BR>     FROM PODS.T_ODS_PAR_OCSACTIVEMSG B <BR>     where A.ID_NO=B.ID_NO )</DIV>
<DIV><BR>Section Code Page = 1386</DIV>
<DIV>Estimated Cost = 1512832.000000<BR>Estimated Cardinality = 183703.968750</DIV>
<DIV>Coordinator Subsection - Main Processing:<BR>   Distribute Subsection #1<BR>   |  Broadcast to Node List<BR>   |  |  Nodes = 1, 2, 3, 4, 5, 6, 7</DIV>
<DIV>Subsection #1:<BR>   Access Table Name = PODS.T_ODS_PAR_OCSACTIVEMSG  ID = 11,3174<BR>   |  #Columns = 1<BR>   |  Skip Inserted Rows<BR>   |  Skip Deleted Rows<BR>   |  Relation Scan<BR>   |  |  Prefetch: Eligible<BR>   |  Lock Intents<BR>   |  |  Table: Intent Share<BR>   |  |  Row  : Next Key Share<BR>   |  Sargable Predicate(s)<BR>   |  |  Process Build Table for Hash Join<BR>   Anti Left Outer Hash Join<BR>   |  Early Out: Single Match Per Outer Row<BR>   |  Estimated Build Size: 7584000<BR>   |  Estimated Probe Size: 6960000<BR>   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155<BR>   |  |  #Columns = 3<BR>   |  |  Skip Inserted Rows<BR>   |  |  Skip Deleted Rows<BR>   |  |  Evaluate Block/Data Predicates Before Locking Row<BR>   |  |  Relation Scan<BR>   |  |  |  Prefetch: Eligible<BR>Isolation Level: Read Stability<BR>   |  |  Lock Intents<BR>   |  |  |  Table: Intent Exclusive<BR>   |  |  |  Row  : Update<BR>   |  |  Sargable Predicate(s)<BR>   |  |  |  #Predicates = 1<BR>   |  |  |  Process Probe Table for Hash Join<BR>   Establish Row Position<BR>   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155<BR>   Update:  Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155</DIV>
<DIV>End of section</DIV>
<DIV><BR>Optimizer Plan:</DIV>
<DIV>                                                  UPDATE <BR>                                                  (   2) <BR>                                       /---------/      <BR>                                 FETCH                 Table:            <BR>                                 (   3)                PODS              <BR>                        /-------/                     T_ODS_PAR_CUSTMSG <BR>                  HSJOIN            Table:            <BR>                  (   4)            PODS              <BR>             /---/      --        T_ODS_PAR_CUSTMSG <BR>       TBSCAN               TBSCAN <BR>       (   5)               (   6) <BR>         |                    |    <BR> Table:             Table:                 <BR> PODS               PODS                   <BR> T_ODS_PAR_CUSTMSG  T_ODS_PAR_OCSACTIVEMSG <BR></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>参考：</DIV>
<DIV><STRONG><FONT face=Verdana size=4>REBIND 实用程序和 FLUSH PACKAGE CACHE 命令：</FONT></STRONG></DIV>
<DIV><FONT face=Verdana size=2></FONT>在执行数据库应用程序或任何 SQL 语句之前，必须先由 DB2 对它们进行预编译，并生成一个<I>包</I>。包是一种数据库对象，其中含有应用程序源文件中所使用的已编译 SQL 语句。DB2 使用该包来访问 SQL 语句中引用的数据。DB2 优化器如何为这些包选择数据存取方案呢？它依靠包创建时的数据库统计信息。</DIV>
<DIV>
<P><STRONG>对于静态 SQL 语句，</STRONG>包在编译时创建，并且被绑定到数据库上。如果对统计信息进行了更新，以反映物理数据库特征，那么也应该更新现有的包。<FONT face=Courier size=1>REBIND</FONT> 实用程序允许您重新创建包，以便可以利用当前的数据库统计信息。命令十分简单：</P>
<P>REBIND PACKAGE <I>package_name</I><BR>不过，如果您要更改应用程序源代码，那么就得显式地删除现有的相关包，然后重新创建包。<FONT face=Courier size=1>REBIND</FONT> 实用程序不用于这一目的。这里，我们之所以就此对您加以提醒，是因为 DBA 经常误解了 <FONT face=Courier size=1>REBIND</FONT> 的用法。</P>
<P><STRONG>对于动态 SQL 语句，</STRONG>它们是在运行时预编译的，而且被存储在包高速缓存中。如果更新了统计信息，那么您可能会刷新高速缓存，以便重新编译动态 SQL 语句，从而获取更新的统计信息。命令类似下面的样子：<BR>FLUSH PACKAGE CACHE DYNAMIC<BR><BR><STRONG>静态sql动态sql的比较可参考：</STRONG></P></DIV>
<DIV><A href="http://dev.21tx.com/2009/12/24/12999.html" target=_blank>http://dev.21tx.com/2009/12/24/12999.html</A></DIV></DIV></DIV>

著作权归作者所有

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