一次sql调优历程

在项目进入性能测试阶段，终于爆发了sql运行缓慢，系统吞吐量下降，甚至一度出现oracle服务器cpu100%的情况。具体开发和测试人员报告情况，开始介入处理。

具体查找性能缓慢的过程略除。
发现一条sql运行缓慢。通过跟踪发现一下信息
select alias_p2.pendingid, alias_p2.workitemid, alias_p2.operationid, alias_p2.operationkey,

  2  alias_p2.title, alias_p2.sendercn, alias_p2.operatedes, alias_p2.pendingstate,

  3  alias_p2.parameter, alias_p2.createdate, alias_p2.deptname, alias_p2.completeddate ,

  4  alias_p2.openstate , alias_p2.name, alias_p2.processinstanceid, alias_p2.asset

  5   from ( select alias_p1.pendingid, alias_p1.workitemid, alias_p1.operationid,

  6   alias_p1.operationkey, alias_p1.title, alias_p1.sendercn, alias_p1.operatedes,

  7    alias_p1.pendingstate, alias_p1.parameter, alias_p1.createdate, alias_p1.deptname,

  8      alias_p1.completeddate , alias_p1.openstate , alias_p1.name, alias_p1.processinstanceid ,

  9        alias_p1.asset , rownum rn from(select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,

 10        alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,

 11        alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,

 12         pd.name, w.processinstanceid , eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )

 13          asset from WF_Pending alias_p, WF_WorkItem w, WF_ProcessDefinition pd, WF_ProcessInstance pi

 14          where alias_p.ownerid='qinxue'   and alias_p.pendingstate in(0,3,5,7,9,10,11,12)

 15            and (alias_p.deptname=' 审控部信息处 ' or alias_p.deptname='' or alias_p.deptname is null)

 16            and w.workitemid = alias_p.workitemid   and pi.processinstanceid = w.processinstanceid

 17  and pi.completeddate is null   and pd.processdefinitionid = w.processdefinitionid  order by alias_p.createdate desc) alias_p1 where rownum <=10)

alias_p2 where rn>=1;

<!----> <o:p> </o:p>

已选择 10 行。

<o:p> </o:p>

<o:p> </o:p>

执行计划

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=2507

          )

<o:p> </o:p>

   1    0   VIEW (Cost=10 Card=1 Bytes=2507)

   2    1     COUNT (STOPKEY)

   3    2       VIEW (Cost=10 Card=1 Bytes=2494)

   4    3         SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=167)

   5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=167)

   6    5             NESTED LOOPS (Cost=7 Card=1 Bytes=162)

   7    6               NESTED LOOPS (Cost=6 Card=1 Bytes=134)

   8    7                 TABLE ACCESS (FULL) OF 'WF_PENDING' (Cost=5

          Card=1 Bytes=111)

<o:p> </o:p>

   9    7                 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE

          M' (Cost=1 Card=3 Bytes=69)

<o:p> </o:p>

  10    9                   INDEX (UNIQUE SCAN) OF 'SYS_C003694' (UNIQ

          UE)

<o:p> </o:p>

  11    6               TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDE

          FINITION' (Cost=1 Card=1 Bytes=28)

<o:p> </o:p>

  12   11                 INDEX (UNIQUE SCAN) OF 'SYS_C003684' (UNIQUE

          )

<o:p> </o:p>

  13    5             TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSINST

          ANCE' (Cost=1 Card=1 Bytes=5)

<o:p> </o:p>

  14   13               INDEX (UNIQUE SCAN) OF 'SYS_C003662' (UNIQUE)

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

统计信息

----------------------------------------------------------

        314  recursive calls

          0  db block gets

      29433  consistent gets<o:p></o:p>

          0  physical reads

          0  redo size

       2153  bytes sent via SQL*Net to client

        372  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

        101  sorts (memory)<o:p></o:p>

          0  sorts (disk)

         10  rows processed

其中一致读达到近3万次，关联调用出现314次。排序数值也非常多，显然第一目标是把这两个数据降下来。
通过进一步的分析。发现出现这些问题的主要原因是调用eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )这个包。
开始考虑直接在sql外层做关联，不用function来实现。利用聚集函数来合并数据。
着手建立：

聚集函数： CREATE OR REPLACE FUNCTION F_ASSETLINK(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING asset_link;

----------------------
创建type：CREATE OR REPLACE TYPE ASSET_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ASSET_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ASSET_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ASSET_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ASSET_LINK, CTX2 IN ASSET_LINK) RETURN NUMBER
)
------------------------------------------------------

创建type body：CREATE OR REPLACE TYPE BODY ASSET_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ASSET_LINK) RETURN NUMBER IS
BEGIN
SCTX := ASSET_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ASSET_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR ||','|| VALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ASSET_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ASSET_LINK, CTX2 IN ASSET_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
调整sql如下：
select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
   alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
   alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
   pd.name, w.processinstanceid
   --,T.ASSETCLASS3  ASSET  
   ,f_assetlink(d3.typename) ASSET
   --,eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )  asset
   from WF_Pending alias_p, WF_WorkItem w,
   WF_ProcessDefinition pd, WF_ProcessInstance pi
   , tb_asset_dizhiyihao T,dic_app_wfconfig wfc,dic_app_assettype3 d3
   where alias_p.ownerid='qinxue'  
   and alias_p.pendingstate in(0,3,5,7,9,10,11,12)
   and (alias_p.deptname='审控部信息处' or alias_p.deptname='' or alias_p.deptname is null)
   and w.workitemid = alias_p.workitemid  
   and pi.processinstanceid = w.processinstanceid
   and pi.completeddate is null  
   and pd.processdefinitionid = w.processdefinitionid
   AND    t.pk_businessid = alias_p.operationid
          and alias_p.operationkey = wfc.memo_1
          and wfc.wfconfig_code = t.wfconfig_code
   and t.assetclass3 = d3.assettype3_id
   group by alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
   alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
   alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
   pd.name, w.processinstanceid
   order by alias_p.createdate desc
得到统计数据如下：
C:\Documents and Settings\ibm>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 10 19:27:33 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn jic/jic@name
已连接。
SQL> set autotrace traceonly
SQL> select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
  2     alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
  3     alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
  4     pd.name, w.processinstanceid
  5     --,T.ASSETCLASS3  ASSET
  6     ,f_assetlink(d3.typename) ASSET
  7     --,eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )  asset
  8     from WF_Pending alias_p, WF_WorkItem w,
  9     WF_ProcessDefinition pd, WF_ProcessInstance pi
 10     , tb_asset_dizhiyihao T,dic_app_wfconfig wfc,dic_app_assettype3 d3
 11     where alias_p.ownerid='qinxue'
 12     and alias_p.pendingstate in(0,3,5,7,9,10,11,12)
 13     and (alias_p.deptname='审控部信息处' or alias_p.deptname='' or alias_p.deptname is null)
 14     and w.workitemid = alias_p.workitemid
 15     and pi.processinstanceid = w.processinstanceid
 16     and pi.completeddate is null
 17     and pd.processdefinitionid = w.processdefinitionid
 18     AND    t.pk_businessid = alias_p.operationid
 19            and alias_p.operationkey = wfc.memo_1
 20            and wfc.wfconfig_code = t.wfconfig_code
 21     and t.assetclass3 = d3.assettype3_id
 22     group by alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
 23     alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
 24     alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
 25     pd.name, w.processinstanceid
 26     order by alias_p.createdate desc;

已选择30行。

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=205)
   1    0   SORT (GROUP BY) (Cost=19 Card=1 Bytes=205)
   2    1     NESTED LOOPS (Cost=17 Card=1 Bytes=205)
   3    2       HASH JOIN (Cost=16 Card=1 Bytes=191)
   4    3         HASH JOIN (Cost=11 Card=1 Bytes=183)
   5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=167)
   6    5             NESTED LOOPS (Cost=7 Card=1 Bytes=139)
   7    6               NESTED LOOPS (Cost=6 Card=1 Bytes=134)
   8    7                 TABLE ACCESS (FULL) OF 'WF_PENDING' (Cost=5
          Card=1 Bytes=111)

   9    7                 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE
          M' (Cost=1 Card=1 Bytes=23)

  10    9                   INDEX (UNIQUE SCAN) OF 'SYS_C004347' (UNIQ
          UE)

  11    6               TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSIN
          STANCE' (Cost=1 Card=1 Bytes=5)

  12   11                 INDEX (UNIQUE SCAN) OF 'SYS_C004334' (UNIQUE
          )

  13    5             TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDEFI
          NITION' (Cost=1 Card=1 Bytes=28)

  14   13               INDEX (UNIQUE SCAN) OF 'SYS_C004329' (UNIQUE)
  15    4           TABLE ACCESS (FULL) OF 'DIC_APP_WFCONFIG' (Cost=2
          Card=24 Bytes=384)

  16    3         TABLE ACCESS (FULL) OF 'TB_ASSET_DIZHIYIHAO' (Cost=4
           Card=310 Bytes=2480)

  17    2       TABLE ACCESS (BY INDEX ROWID) OF 'DIC_APP_ASSETTYPE3'
          (Cost=1 Card=1 Bytes=14)

  18   17         INDEX (UNIQUE SCAN) OF 'PK_DIC_APP_ASSETTYPE3' (UNIQ
          UE)

 

 

统计信息
----------------------------------------------------------
          6  recursive calls
          0  db block gets
        847  consistent gets
          0  physical reads
          0  redo size
       4102  bytes sent via SQL*Net to client
        383  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

其中排序由101变为1次
一致读降为847。下降非常客观
关联调用仅有6次。
此sql性能优化非常可观。至此优化结束：)