3 Replies Latest reply: Dec 22, 2013 12:06 PM by Hussein Sawwan-Oracle RSS

    slow WFBG for deferred activities

    Habiburrokhman Sjarbini

      Dear All,

      EBS: 12.1.0.3 - Shared Multi Node

      DB: 11.2.0.2 - RAC

      OS: RHEL x86-64 5.5

       

      In our PROD system we have 3 WFBG schedulers. Below are the configurations:

      REQUEST_ID REQUESTED_BY P USER_CONCURRENT_PROGRAM_NAME                   Arguments             EVERY SO_OFTEN                   RESUBMIT_END_DATE

      ---------- ------------ - -------------------------------------------------- -------------------- ---------- ------------------------------ ------------------

         8289882        0 P Workflow Background Process                    , , , N, Y, N             1 HOURS
         8287054        0 P Workflow Background Process                    , , , N, N, Y             1 DAYS
         8290413        0 P Workflow Background Process                    , , , Y, N, N             5 MINUTES

       

      Currently, we have performance issue with WFBG for the deferred activities. Last time it completed in almost 8 Hours. From v$active_session_history, below database events are captured:

      select sql_id, event, sum(time_waited) from v$active_session_history where session_id=715 and

      sample_time between

          to_date('09-DEC-13 12:30:00','DD-MON-YY HH24:MI:SS')

          and

          to_date('09-DEC-13 15:00:00','DD-MON-YY HH24:MI:SS')

      group by sql_id, event

      having sum(time_waited) > 0

      order by sum(time_waited);

      SQL_ID        EVENT                          SUM(TIME_WAITED)

      ------------- ------------------------------ ----------------

      56ubx75p2k7x5 db file sequential read                    1693

      aug9kqdfu0s6p gc cr multi block request                  5036

      66tmsr3446uqn db file sequential read                    5948

      9dzhn01z3qy3b Disk file operations I/O                   7048

      65qczrm0turbh db file sequential read                    8076

      aug9kqdfu0s6p db file parallel read                      8543

      1pmvmpmhdjdrk db file sequential read                   12409

      ccprbqfuu8wb1 db file sequential read                   16375

      dq5v9g4z9jbrq db file sequential read                   18043

      0fxqsqwhkn03b db file sequential read                   22790

      aug9kqdfu0s6p db file scattered read                    43602

      aug9kqdfu0s6p db file sequential read                   65505

      d02kbfjwq7ywv db file sequential read                   89022

      9dzhn01z3qy3b direct path read                         167570

      9n126p922amjm direct path read                         173267

      aug9kqdfu0s6p direct path read                         282216

      d02kbfjwq7ywv direct path read                         330143

      9dzhn01z3qy3b db file sequential read                 2588002

       

      18 rows selected.

       

      SQL Plan for SQL_ID 9dzhn01z3qy3b is as below:

      SELECT RILA.ROWID , RILA.TRX_DATE , RILA.INTERFACE_LINE_ATTRIBUTE1 ,

      OOLA.INVOICED_QUANTITY , OOHA.REQUEST_DATE FROM RA_INTERFACE_LINES_ALL

      RILA , OE_ORDER_LINES_ALL OOLA , OE_ORDER_HEADERS_ALL OOHA WHERE

      RILA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OOLA.LINE_ID) AND

      RILA.INTERFACE_LINE_ATTRIBUTE6 = :B1 AND RILA.INTERFACE_LINE_CONTEXT =

      'ORDER ENTRY' AND OOLA.LINE_CATEGORY_CODE = 'RETURN' AND OOHA.HEADER_ID

      = OOLA.HEADER_ID

      ---------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                      | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT               |                            |       |       |       | 17483 (100)|          |

      |   1 |  NESTED LOOPS                  |                            |       |       |       |            |          |

      |   2 |   NESTED LOOPS                 |                            |     1 |    85 |       | 17483   (2)| 00:03:30 |

      |   3 |    MERGE JOIN                  |                            |     1 |    72 |       | 17482   (2)| 00:03:30 |

      |   4 |     TABLE ACCESS BY INDEX ROWID| RA_INTERFACE_LINES_ALL     |     1 |    51 |       |  1512   (2)| 00:00:19 |

      |   5 |      INDEX FULL SCAN           | XX01_RA_INTERFACE_LINES_N7 |     2 |       |       |  1511   (2)| 00:00:19 |

      |   6 |     SORT JOIN                  |                            | 39404 |   808K|  2808K| 15970   (2)| 00:03:12 |

      |   7 |      TABLE ACCESS FULL         | OE_ORDER_LINES_ALL         | 39404 |   808K|       | 15710   (2)| 00:03:09 |

      |   8 |    INDEX UNIQUE SCAN           | OE_ORDER_HEADERS_U1        |     1 |       |       |     0   (0)|          |

      |   9 |   TABLE ACCESS BY INDEX ROWID  | OE_ORDER_HEADERS_ALL       |     1 |    13 |       |     1   (0)| 00:00:01 |

      ---------------------------------------------------------------------------------------------------------------------

       

      Note:

      We already added index,XX01_RA_INTERFACE_LINES_N7, on AR.RA_INTERFACE_LINES_ALL as suggested by Metalink Engineer.

      But, based on the SQL Plan, it's still doing FTS on OE_ORDER_LINES_ALL.

       

      Also WF tables are very huge since some parents are not close yet since they still have open child items (Purging is scheduled everyday to keep 14days runtime data).

      SQL> select table_name, num_rows from dba_tables where table_name in ('WF_ITEM_ATTRIBUTE_VALUES','WF_ITEM_ACTIVITY_STATUSES','WF_NOTIFICATION_ATTRIBUTES');

       

      TABLE_NAME                       NUM_ROWS

      ------------------------------ ----------

      WF_ITEM_ACTIVITY_STATUSES         3863143

      WF_ITEM_ATTRIBUTE_VALUES          6350137

      WF_NOTIFICATION_ATTRIBUTES         156220

       

       

       

      Our questions are:

      1. Should we change the interval of this WFBG. 5 minutes are suggested in MOS ID, but is it too frequent?

      2. If 5 minutes are normal, is the performance issue in WFBG deferred activity  caused by huge WF tables?

      3. Should we add another index on OE_ORDER_LINES_ALL table to reduce the wait time?

       

      Kind Regards,

      Abip