EBS WFERROR工作流清理

2024年3月29日，EBS WFERROR工作流清理

故障描述： 
	EBS的部分环境工作流历史数据比较多，影响登录性能，希望可以清除一些无关紧要的数据（不影响业务，不需要业务确认），提升性能

原因分析：
	参考
	How to Purge WFERROR (System Error) Workflow Items (Doc ID 804622.1)
	How to Purge Workflow Notifications of OM Error (OMERROR) (Doc ID 2173917.1)

set lines 200 pages 50000;
select c.item_type child, decode(c.end_date,null,'OPEN','CLOSED') child_status,
c.parent_item_type parent, decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')) parent_status,
count(*)
from
wf_items p,
wf_items c
where
p.item_type(+) = c.parent_item_type
and p.item_key(+) = c.parent_item_key
and c.item_type='WFERROR'
group by c.item_type, decode(c.end_date,null,'OPEN','CLOSED'), c.parent_item_type ,
decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED'))
order by c.item_type , c.parent_item_type;

select item_type,count(*) from wf_items group by item_type;
select MESSAGE_TYPE,count(*) from wf_notifications wn where wn.recipient_role='SYSADMIN' group by message_type;

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

ITEM_TYP   COUNT(*)
-------- ----------
WFERROR     2755986
OMERROR        7251

21 rows selected.

SQL> select MESSAGE_TYPE,count(*) from wf_notifications wn where wn.recipient_role='SYSADMIN' group by message_type;

MESSAGE_   COUNT(*)
-------- ----------
GLBATCH         310
WFERROR     2756332
WFMAIL            1
OMERROR     2393783
POERROR           1

SQL> 

在问题环境WFERROR的数据占一半，OMERROR数据占另一半，一方面应分析产生WFERROR产生的原因，解决WFERROR（例如有设置错误），另外，如下直接终止掉7天以前没有父项的WFERROR，清理这部分数据是安全的

Declare
  counter number;
  Cursor abort_wf is
    select item_type, item_key
      from wf_items
     where item_type = 'WFERROR'
       and parent_item_type is null
       and end_date is null
       and begin_date < sysdate - 7;
	   
Begin
  counter := 1;
  For wf In abort_wf Loop
    Begin
      WF_ENGINE.AbortProcess('WFERROR', wf.item_key);
      counter := counter + 1;
      if counter > 1000 then
        counter := 1;
        commit;
      end if;
    End;
  End loop;
  commit;
End;
/

在测试环境abort 230W个WFERROR流程需要7.5小时，可以类似的编写下面脚本，以nohup方式运行

根据实际情况修改下面APPS口令
vi purge_wferror.sh
#!/bin/bash

sqlplus apps/<apps口令> <<EOF
Declare
  counter number;
  Cursor abort_wf is
    select item_type, item_key
      from wf_items
     where item_type = 'WFERROR'
       and parent_item_type is null
       and end_date is null
       and begin_date < sysdate - 7;
	   
Begin
  counter := 1;
  For wf In abort_wf Loop
    Begin
      WF_ENGINE.AbortProcess('WFERROR', wf.item_key);
      counter := counter + 1;
      if counter > 1000 then
        counter := 1;
        commit;
      end if;
    End;
  End loop;
  commit;
End;
/
quit
EOF

chmod u+x purge_wferror.sh
nohup ./purge_wferror.sh &

测试环境运行约xx小时

直接后台更新，关闭所有SYSADMIN的通知（并不是关闭通知后就可以PURGE掉整个工作流，只有终止的WFERROR流程才能被清理掉，但是关闭这些通知后也会大幅提升登录的性能）

create table applsys.wf_notifications_240329 as select * from applsys.wf_notifications where recipient_role='SYSADMIN'
and status='OPEN';
 
UPDATE /*+ parallel(wn ) */wf_notifications wn
set STATUS='CLOSED'
where recipient_role='SYSADMIN'
and status='OPEN';
commit;

alter index applsys.WF_NOTIFICATIONS_N1 rebuild parallel 8;
alter index applsys.WF_NOTIFICATIONS_N3 rebuild parallel 8;
alter index applsys.WF_NOTIFICATIONS_N5 rebuild parallel 8;
alter index applsys.WF_NOTIFICATIONS_N6 rebuild parallel 8;
alter index applsys.WF_NOTIFICATIONS_N7 rebuild parallel 8;
alter index applsys.WF_NOTIFICATIONS_N1 noparallel;
alter index applsys.WF_NOTIFICATIONS_N3 noparallel;
alter index applsys.WF_NOTIFICATIONS_N5 noparallel;
alter index applsys.WF_NOTIFICATIONS_N6 noparallel;
alter index applsys.WF_NOTIFICATIONS_N7 noparallel;

有条件，应收集下wf_notifications统计信息
exec fnd_stats.gather_table_stats('APPLSYS','WF_NOTIFICATIONS',100,degree=>64);

提交清理请求:
程序：清除过时的工作流运行时数据
参数
项目类型：系统：错误
项目关键字：空
账龄：0
持续时间类型：临时
仅限于核心工作流：是
提交频率：1000
已签名通知：否
其它缓存数据：否

解决措施：
	运行修复脚本，运行清理请求

处理结果：
	已解决