处理Oracle EBS R12标准功能切换职责速度慢的问题

最近用户（用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.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=424809528957945&id=1283180.1&displayIndex=2&_afrWindowMode=0&_adf.ctrl-state=q0pq3u7bz_4