Assignment Manager Crash and SBL-GEN-03001: Error allocating DynArrCreate Organization Skill Item

Approximately year back we faced this issue where Assignment Manager was crashing with "SBL-GEN-03001: Error allocating DynArrCreate Organization Skill Items" as error.Upon more analysis I found that this is memory related issue caused by huge orphan records in "S_BU_SKILL_IT". In our environment we are creating Organization Assignment condition and skill items  almost on daily to support business requirement. Process we followed programmatically is delete  organization conditions and recreate organization assignment condition as per latest data.

To be very specific, orphan records are due to cascade delete was set to None on "Organization Skill/Organization Skill Item" instead of "Delete".
Since this happened in production we had following option to choose from.

• Short Term:
  Delete records in S_BU_SKILL_IT which does not have reference in S_BU_SKILL. There is vanilla configuration which does not delete records from Organization Skill Item (S_BU_SKILL_IT) when Organization Skill (S_BU_SKILL) is deleted. 
  
  • Stop Assignment Manager and Batch assignment manager.
  • Build pl/SQL script to delete records wherein there is no reference to s_bu_skill.
  • Once delete is done. Delete rule cache for assignment manager
  • Restart Assignment Manager and Batch Assignment Manager
• Long Term:
  Configuration change to set Cascade delete property to "Delete" for link "Organization Skill/Organization Skill Item"

Due to urgency we took short term approach and created pl/sql process to delete records from s_bu_skill_it and used below code to perform the operation and later on we pushed configuration change as well to resolve issue permanently.

declare
   cursor src is
              select row_id  from siebel.s_bu_skill_it a  where bu_skill_id  not in (select row_id from siebel.s_bu_skill b where b.ROW_ID = a.BU_SKILL_ID) ;
    v_cnt number :=0;
    v_recipients varchar2(200) := 'email_address';
    conn utl_smtp.connection;
    v_err_msg varchar2(200);
begin
     FOR cur1 IN src LOOP
        EXIT WHEN src  %NOTFOUND;
             delete from siebel.s_bu_skill_it where row_id = cur1.row_id;
             v_cnt := v_cnt +1 ;
             if v_cnt > 100000 then
                commit;
                v_cnt :=0;
             end if;
     end loop;
     commit;
commit;
exception
    when others then
        commit;
        v_err_msg :=substr(SQLERRM, 1, 200);
        DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;

Call stack during crash:

sslcshar +0x190dc = SSstring::operator=() +0xc
sslcshar +0x25235 = CCFMiscUtil::GetAssertOnErrorList() +0x25
sslcshar +0x37f61 = scfErrorBase::LogError() +0x91
sslcasgn +0x41a2c = GetWLObjName() +0x9d0c
sslcasgn +0x4492b = StartRules() +0xfab
sssaasgn +0x2245 = CSSAsgnBase::operator=() +0x1095
sssaasgn +0x2f5c = CreateSmiMThreadObj() +0x3bc
sssaasgn +0x57c7 = ReqCompCleanup() +0x27
siebmtsh +0x96ec = SmiCleanupDetTask() +0x118c
siebmtsh +0x273c6 = SmiRegisterThrdCleanUpFunc() +0x2546
kernel32 +0x2f23b = ProcessIdToSessionId() +0x209
Other References : Doc ID 1103948.1