现象:有一条sql在plsql中执行起来非常快47ms,但通过java程序执行的时候很慢,执行了
87279ms ,通过执行计划看在plsql中执行命中索引消耗很少,在程序中执行的执行计划不知怎么得到.
可以通过
select * from table(dbms_xplan.display_awr('45m6cx0g6dmwu'));这个命令获取在java程序执行时的解析计划.
45m6cx0g6dmwu 这个id是通过v$session表获取的sql_id.
执行sql如下:
---------------# 2015-12-28 11:09:59:157 | took 87279ms | statement | connection 1
SELECT l.ID,
p.REQUESTER,
l.WORKFLOWID,
l.CURRENTACTIVITY,
l.CURRENTACTIVITYID,
l.PREVACTIVITY,
l.ACCEPTER,
l.STATUS,
l.SENDER,
l.SENDERNAME,
l.CREATETIME,
l.PROPOSAL,
l.companyid,
l.companyname,
l.authorizername
FROM T_APPROVE_LOG l
left join T_APPROVE_PROCESS p
on p.workflowid = l.workflowid
WHERE p.PARENTWORKFLOWID = 18407004
and p.PARENTWORKFLOWID > 0
order by p.workflowid, l.id;
SELECT l.ID,
p.REQUESTER,
l.WORKFLOWID,
l.CURRENTACTIVITY,
l.CURRENTACTIVITYID,
l.PREVACTIVITY,
l.ACCEPTER,
l.STATUS,
l.SENDER,
l.SENDERNAME,
l.CREATETIME,
l.PROPOSAL,
l.companyid,
l.companyname,
l.authorizername
FROM T_APPROVE_LOG l
left join T_APPROVE_PROCESS p
on p.workflowid = l.workflowid
WHERE p.PARENTWORKFLOWID = 18407004
and p.PARENTWORKFLOWID > 0
order by p.workflowid, l.id;
在plsql中执行的解析计划如下:
在java中执行的解析计划如下:
最终解决的方式是:重建相关的索引.
现象:有一条sql在plsql中执行起来非常快47ms,但通过java程序执行的时候很慢,执行了
87279ms ,通过执行计划看在plsql中执行命中索引消耗很少,在程序中执行的执行计划不知怎么得到.
可以通过
select * from table(dbms_xplan.display_awr('45m6cx0g6dmwu'));这个命令获取在java程序执行时的解析计划.
45m6cx0g6dmwu 这个id是通过v$session表获取的sql_id.
执行sql如下:
---------------# 2015-12-28 11:09:59:157 | took 87279ms | statement | connection 1
SELECT l.ID,
p.REQUESTER,
l.WORKFLOWID,
l.CURRENTACTIVITY,
l.CURRENTACTIVITYID,
l.PREVACTIVITY,
l.ACCEPTER,
l.STATUS,
l.SENDER,
l.SENDERNAME,
l.CREATETIME,
l.PROPOSAL,
l.companyid,
l.companyname,
l.authorizername
FROM T_APPROVE_LOG l
left join T_APPROVE_PROCESS p
on p.workflowid = l.workflowid
WHERE p.PARENTWORKFLOWID = 18407004
and p.PARENTWORKFLOWID > 0
order by p.workflowid, l.id;
SELECT l.ID,
p.REQUESTER,
l.WORKFLOWID,
l.CURRENTACTIVITY,
l.CURRENTACTIVITYID,
l.PREVACTIVITY,
l.ACCEPTER,
l.STATUS,
l.SENDER,
l.SENDERNAME,
l.CREATETIME,
l.PROPOSAL,
l.companyid,
l.companyname,
l.authorizername
FROM T_APPROVE_LOG l
left join T_APPROVE_PROCESS p
on p.workflowid = l.workflowid
WHERE p.PARENTWORKFLOWID = 18407004
and p.PARENTWORKFLOWID > 0
order by p.workflowid, l.id;
在plsql中执行的解析计划如下:
在java中执行的解析计划如下: