Oracle Workflow Performance Issue

In this blogpost, we can see how to overcome the performance issues in our EBS environments, All our EBS workflows undergoes many test and incomplete workflow cycles, that starts giving performance issues over a time period. This  is a step-by-step guide.

1) Please check the item type activity status by using the following query:

SQL> select item_type,activity_status,count(*) from wf_item_activity_statuses where item_type like 'XX%' group by item_type,activity_status ;

2) Please check count for each item_type using the below query and note down the item types.

SQL> select item_type,count(*) from wf_item_attribute_values group by item_type ;

 

 3) Please check the AWR report and wait events on workflow tables

4) Run the below script in apps user and given item_type value which we identified in  Step :2 

SQL> Declare

         cursor c_item_keys is

         select itm.item_key,itm.item_type

             from wf_items itm

             where

             itm.item_type ='&Item_type'

             and itm.end_date is null;

             counter number; 

  Begin

    counter := 1 ;

    for item in c_item_keys loop

         begin

             wf_engine.abortprocess(item.item_type,item.item_key);

             EXCEPTION when others then

            dbms_output.put(item.item_type|| ' ' ||item.item_key);

             end;

         counter := counter + 1 ;

         if counter > 1000 then

                counter := 1 ;

                commit;

         end if;

     end loop;

     commit;

End;

/

  

5) Please run concurrent Program “Purge Obsolete Workflow Runtime Data”  

a.    Null for item_types so it is applicable for all item types

b.    10000 as batch size

c.    Retention 10

6) Please run the below script to get the count again and  run Step5,  once again until count comes down.

SQL> select wi.item_type ITEM_TYPE,

       wit.persistence_type P_TYPE,

       decode (wi.end_date, NULL, 'OPEN', 'CLOSED') Status,

       count(*) COUNT

from applsys.wf_items wi,

     applsys.wf_item_types wit

where wit.name = wi.item_type

group by item_type,

         wit.persistence_type,

         WIT.PERSISTENCE_DAYS,

         decode (wi.end_date, NULL, 'OPEN', 'CLOSED')

order by decode (wi.end_date, NULL, 'OPEN', 'CLOSED'), 4 desc;

 

7) If count still high , Please run below script for each high count item types which came from above step 6.

SQL> @$FND_TOP/sql/wfrmtype.sql

8) Please continue the above effort until count comes down in parallel with purging job

9) After completing the above steps, Need to identify the fragmented tables, indexes and run the below commands to re-org

SQL> alter index owner.indexname COALESCE;

SQL> alter table owner.table_name enable row movement;

SQL> alter table owner.table_name SHRINK SPACE CASCADE;

HAPPY LEARNING!