最近用户(用EBS系统的时候)普遍反馈一个问题:
切换职责的时候,很慢,基本要5秒左右。有时候甚至要等上7秒以上。
就是下图,在点确定之后,切换到另外一个职责,经常要5秒左右。
这个问题如何处理?
其实是这样子的,如果以前使用系统的时候正常,现在突然变慢,在服务器的性能没大幅度下降的前提下,应该是某个SQL的执行计划出现问题了。
所以,现在的处理这类“突然慢”的问题,核心的解决方法还是:找出是哪条SQL,慢在哪里。这样子才可以解决问题!
怎么找出是哪条SQL慢呢?正常情况下,一般跟踪会话的执行情况即可。例如开个10046事件跟踪会话的执行SQL情况等等。
鉴于有些人不懂如何这种跟踪的方式,这里说的找出该会话执行慢的SQL的办法是一个比较简单的办法。
看下面的步骤:
1 首先,打开系统,在职责的界面,直接查看帮助–>关于Oracle Application,找出该职责对应的会话ID(SID),然后执行下面的SQL。
可以看出这里是4208:
SELECT A.INST_ID,A.SID,A.PROCESS,A.ACTION,A.STATUS,A.SQL_ID,A.SQL_CHILD_NUMBER
FROM GV$SESSION A
WHERE A.SID=4208
AND A.INST_ID=1;
2 接着,回到EBS界面,做切换职责的动作,然后瞬速切回到开发工具查询上面的SQL!确认STATUS=ACTIVE的时候,哪条SQL是停留时间最长的。
需要注意的是:要反复操作多几次,如果确实是切换职责的时候卡在某条SQL,那应该很容易可以看出来。然后记录下对应的SQL_ID以及SQL_CHILD_NUMBER
这样子,就基本可以定位解决问题的关键点:切换职责的时候,究竟跑哪条SQL特别的慢!
SELECT SQL_TEXT
,INST_ID
,SQL_ID
,CHILD_NUMBER
,ROUND(ELAPSED_TIME / 1000000 / DECODE(EXECUTIONS, 0, 1, EXECUTIONS),5) PER_ELAPSED_TIME--单条SQL的平均执行时间(秒)
FROM GV$SQL
WHERE SQL_ID='0y7y17bcappxk'
AND CHILD_NUMBER=0
AND INST_ID=1;
我这边切换职责,卡的SQL是:
SELECT P.PID, P.SERIAL#, P.SPID FROM V$PROCESS P, V$SESSION S WHERE S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR
3 找到是哪条SQL慢,问题就可以分析了:为什么慢。
我最近处理这个问题,是因为SQL的执行计划出现的问题!
速度慢的:
SQL_ID 0y7y17bcappxk, child number 0
-------------------------------------
SELECT P.PID, P.SERIAL#, P.SPID FROM V$PROCESS P, V$SESSION S WHERE
S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR
Plan hash value: 1245246913
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | NESTED LOOPS | | 1 | 60 | 0 (0)|
| 2 | MERGE JOIN CARTESIAN | | 149 | 5960 | 0 (0)|
| 3 | NESTED LOOPS | | 9 | 108 | 0 (0)|
| 4 | FIXED TABLE FULL | X$KSLWT | 100 | 800 | 0 (0)|
|* 5 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 4 | 0 (0)|
| 6 | BUFFER SORT | | 17 | 476 | 0 (0)|
|* 7 | FIXED TABLE FULL | X$KSUPR | 17 | 476 | 0 (0)|
|* 8 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 20 | 0 (0)|
------------------------------------------------------------------------------------
执行计划正常的应该是:
SQL_ID 0y7y17bcappxk, child number 0
-------------------------------------
SELECT P.PID, P.SERIAL#, P.SPID FROM V$PROCESS P, V$SESSION S WHERE
S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR
Plan hash value: 2285488774
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | 2 | 120 | 3 (100)| 00:00:01 |
|* 2 | HASH JOIN | | 2 | 112 | 3 (100)| 00:00:01 |
| 3 | NESTED LOOPS | | 2 | 56 | 1 (100)| 00:00:01 |
| 4 | FIXED TABLE FULL | X$KSLWT | 1154 | 9232 | 0 (0)| |
|* 5 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 20 | 0 (0)| |
|* 6 | FIXED TABLE FULL | X$KSUPR | 686 | 19208 | 2 (100)| 00:00:01 |
|* 7 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 0 (0)| |
-----------------------------------------------------------------------------------------------
由于执行计划在Toad里面直接执行,也是没问题的。
所以,基本可以断定切换职责慢的数据库环境的执行计划出现问题了。
解决办法:
在INST_ID=2环境里面,将执行计划给Purge掉,让Oracle重新弄一个执行计划:
–如何踢掉某个SQL ID的执行计划:
–http://blog.csdn.net/stevendbaguo/article/details/43796433
select s.SQL_TEXT,'exec sys.dbms_shared_pool.purge('''||s.ADDRESS||','||s.HASH_VALUE||''',''c'');'
from v$sqlarea s
where sql_id = '0y7y17bcappxk';
exec sys.dbms_shared_pool.purge('0700000673865A50,3635074994','c');
将这个执行计划Purge掉之后,Oracle再重新执行SQL的时候,会重新找执行计划。
这边重新找的执行计划是正常的,所以,切换职责的速度也就正常了!实测基本是2秒左右就可以切换好职责。
问题得以解决!
2017.6.4更新:
有一个朋友刚刚好也咨询我这个问题。
让他监控哪个sql慢,发给我之后,我发现我这边的数据库环境实际上运行没这么慢。
所以基本确定还是执行计划出问题。但是他的问题是,切换职责一直很慢。所以基本猜测应该是bug导致。
果然是这样子。oracle Support上有解决的办法,打个补丁即可:
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=78046284798938&id=745701.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=v3t5eiv1f_76
The application may take up to 30-40 seconds to display list of responsibilities LOV.