Sunday, November 29, 2015

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
             itm.item_type ='&Item_type'
             and itm.end_date is null;
             counter number; 
    counter := 1 ;
    for item in c_item_keys loop
             EXCEPTION when others then
            dbms_output.put(item.item_type|| ' ' ||item.item_key);
         counter := counter + 1 ;
         if counter > 1000 then
                counter := 1 ;
         end if;
     end loop;
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 = wi.item_type
group by item_type,
         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;