- 浏览: 38824 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
kebo:
-define(INTEGER_MAX, 268435455) ...
erlang版的adobe amf3格式解析模块 -
kebo:
tks,我试试
erlang 的mysql driver 不支持 bit类型的问题 -
mryufeng:
可以考虑用odbc 最新的mysql驱动常有 就没有这个问题 ...
erlang 的mysql driver 不支持 bit类型的问题 -
kebo:
呵呵,等我搞完了这段,在写点想法
基于osgi框架采用mina+springdm+hibernate实现的网络服务器 -
boyingking:
楼主主要在哪些方面应用了OSGI呢?能否进行一下更深点的交流? ...
基于osgi框架采用mina+springdm+hibernate实现的网络服务器
在项目进入性能测试阶段,终于爆发了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性能优化非常可观。至此优化结束:)
发表评论
-
oracle修改processes参数导致无法启动
2008-12-03 20:54 2533今天朋友在window上修改oracle processes参 ... -
人生不如意十有八九
2006-01-08 09:33 776今天要去给数据库装Partition组件, ... -
oracle分页的一种写法
2006-01-30 12:00 831SELECT * FROM ( SELECT row_.*, ... -
sql
2006-03-29 13:41 1025(转)-- 逻辑读多的SQL select * from (s ... -
sqlplus执行@sql.sql的错误
2006-12-27 22:24 1501今天给数据库执行@spcreate.sql老是出 SP2-07 ... -
分析函数一例
2007-07-17 16:55 905select z.a,z.b,z.c from (sele ...
相关推荐
sql调优.rar sql调优.rar sql调优.rar sql调优.rar
高性能SQL调优code+书签
sql调优工具mytun ,提供高性能的SQL优化,提高数据库的运行速度
SQL调优与案例与架构优化,通过本书可以了解到如何sql语句优化
ORACLE执行计划和SQL调优
Oracle 19C SQL调优优化指南,全面提升SQL优化能力,DBA必备,开发必备
ORACLE 19C SQL调优指南 中文版,很牛逼的文档,Oracle DBA必备
ORACLE执行计划和SQL调优.pptx
某大型公司内部培训,通过实际案例代码讲解SQL调优注意事项
百万级别的sql优化及索引的使用,为了避免全表扫描查询语句的优化
资源名称:DB2 SQL性能调优秘笈资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
sql调优 oracle sql调优技术文档
111 案例实战:千万级用户场景下的运营系统SQL调优(3).pdf
109 案例实战:千万级用户场景下的运营系统SQL调优(1).pdf
NULL 博文链接:https://zhengfc323.iteye.com/blog/1455767
实战MySQL慢SQL调优
本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在sql语句执行的...