Recently, when using the system, users generally feedback a question:
When switching responsibilities, it's very slow. It takes about 5 seconds. Sometimes it takes more than seven seconds.
In the following figure, it often takes about 5 seconds to switch to another duty after the point is determined.

How to deal with this problem?

In fact, if the previous use of the system is normal, now suddenly slowed down, in the premise that the performance of the server does not significantly decline, it should be some SQL execution plan problems.
Therefore, the core solution to this kind of "sudden slow" problem is to find out which SQL is and where the slow is. Only in this way can we solve the problem!

How to find out which SQL is slow? Normally, you can track the execution of a session. For example, opening a 10046 event to track the execution of SQL for a session, and so on.
Given that some people don't know how to trace this way, it's a relatively simple way to find out the slow execution of SQL in this session.
Look at the following steps:

First of all, open the system, in the interface of the responsibility, look directly at Help --> about Oracle Application, find the corresponding session ID(SID) of the responsibility, and then execute the following SQL.


You can see here is 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;

Then, go back to the EBS interface, do the action of switching responsibilities, and then quickly cut back to the development tool to query the SQL above! When confirming STATUS=ACTIVE, which SQL has the longest residence time.
It should be noted that it is easy to see how many times you have to repeat the operation, if you are stuck in a certain SQL when you are really switching responsibilities. Then record the corresponding SQL_ID and SQL_CHILD_NUMBER
In this way, we can basically locate the key point to solve the problem: when switching responsibilities, which SQL is particularly slow!

SELECT SQL_TEXT
      ,INST_ID
      ,SQL_ID
      ,CHILD_NUMBER
      ,ROUND(ELAPSED_TIME / 1000000 / DECODE(EXECUTIONS, 0, 1, EXECUTIONS),5) PER_ELAPSED_TIME--Single stripSQLAverage execution time(second)
  FROM GV$SQL
 WHERE SQL_ID='0y7y17bcappxk'
   AND CHILD_NUMBER=0
   AND INST_ID=1;

I switch responsibilities here. The card's SQL is:

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 Find which SQL is slow, and the question can be analyzed: Why is it slow?
I recently dealt with this problem because of the problems in the execution plan of SQL!
Slow:

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)|
------------------------------------------------------------------------------------

The normal implementation plan should be:

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)|          |
-----------------------------------------------------------------------------------------------

Since the implementation plan is implemented directly in Toad, it is no problem.
Therefore, it can be concluded that the execution plan of the database environment with slow switching responsibilities is in trouble.

Solution:
In the INST_ID=2 environment, the execution plan is dropped to Purge, and Oracle gets another execution plan:
How to kick out an execution plan for an 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');

When the execution plan Purge is removed, Oracle will find the execution plan again when it re-executes the SQL.
It's normal to rediscover the execution plan here, so the speed of switching responsibilities is normal! The actual measurement is basically about 2 seconds to switch responsibilities.
The problem has been solved!