正德厚生,臻于至善

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
已签名通知:否
其它缓存数据:否

解决措施:
	运行修复脚本,运行清理请求

处理结果:
	已解决
赞(0) 打赏
未经允许不得转载:徐万新之路 » EBS WFERROR工作流清理

评论 抢沙发

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫

微信扫一扫

登录

找回密码

注册