在項目進入性能測試階段，終于爆發了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;
?
已選擇
10
行。
?
?
執行計劃
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=2507
????????? )
?
?? 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)
?
?? 9??? 7???????????????? TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE
????????? M' (Cost=1 Card=3 Bytes=69)
?
? 10??? 9?????????????????? INDEX (UNIQUE SCAN) OF 'SYS_C003694' (UNIQ
????????? UE)
?
? 11??? 6?????????????? TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDE
????????? FINITION' (Cost=1 Card=1 Bytes=28)
?
? 12?? 11???????????????? INDEX (UNIQUE SCAN) OF 'SYS_C003684' (UNIQUE
????????? )
?
? 13??? 5???????????? TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSINST
????????? ANCE' (Cost=1 Card=1 Bytes=5)
?
? 14?? 13?????????????? INDEX (UNIQUE SCAN) OF 'SYS_C003662' (UNIQUE)
?
?
?
?
統計信息
----------------------------------------------------------
??????? 314? recursive calls
????????? 0? db block gets
????
?29433? consistent gets
????????? 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)
????????? 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性能優化非常可觀。至此優化結束：)
posted on 2007-09-10 19:35
kebo 閱讀(480)
評論(0) 編輯 收藏 所屬分類:
oracle