Managing, Purging and Cleanup of the BPEL Instances (1)

The customers dealing with high volume of BPEL messages can see a impact on the performance of the processes as the database (ORABPEL) grows in size. The reasons are obvious, time-consuming database lookups , contention issues and so on. In order to peform the scheduled cleanup of the ORABPEL schema, create a procedure in the database as under,

CREATE OR REPLACE PROCEDURE purge_instances (p_older_than TIMESTAMP)AS
BEGIN

--Before starting clean up of temp tables
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_cube_instance';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_invoke_message';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_dlv_message';

--Populate table with information about older instances
INSERT into temp_cube_instance

SELECT cikey
FROM cube_instance
WHERE state >= 3 AND modify_date < p_older_than;

INSERT into temp_invoke_message
SELECT message_guid
FROM invoke_message
WHERE state > 1 AND receive_date < p_older_than;

INSERT into temp_dlv_message
SELECT message_guid
FROM dlv_message
WHERE state > 1 AND receive_date < p_older_than;
COMMIT;

-- WHERE clause is to force CBO to use index of temp table and to avoid full scan of temp
table
-- COMMIT after every delete to reduce pressure on undo log in database

-- Delete all closed instances older than specified date

DELETE FROM native_correlation
WHERE conversation_id IN (SELECT /*+ ORDERED */ dlvs.conv_id FROM dlv_subscription dlvs,
temp_cube_instance tpic
WHERE dlvs.cikey = tpic.cikey );
COMMIT;

DELETE FROM cube_scope
WHERE cikey IN (SELECT /*+ ORDERED */ cs.cikey FROM cube_scope cs, temp_cube_instance tpic
WHERE cs.cikey = tpic.cikey);
COMMIT;

DELETE FROM work_item
WHERE cikey IN (SELECT /*+ ORDERED */ wi.cikey FROM work_item wi, temp_cube_instance tpic
WHERE wi.cikey = tpic.cikey);
COMMIT;

DELETE FROM wi_exception
WHERE cikey IN (SELECT /*+ ORDERED */ wie.cikey FROM wi_exception wie,
temp_cube_instance tpic
WHERE wie.cikey = tpic.cikey);
COMMIT;

DELETE FROM scope_activation
WHERE cikey IN (SELECT /*+ ORDERED */ sa.cikey FROM scope_activation sa,
temp_cube_instance tpic
WHERE sa.cikey = tpic.cikey);
COMMIT;

DELETE FROM dlv_subscription
WHERE cikey IN (SELECT /*+ ORDERED */ dlvs.cikey FROM dlv_subscription dlvs,
temp_cube_instance tpic
WHERE dlvs.cikey = tpic.cikey);
COMMIT;

DELETE FROM audit_trail
WHERE cikey IN (SELECT /*+ ORDERED */ at.cikey FROM audit_trail at, temp_cube_instance tpic
WHERE at.cikey = tpic.cikey);
COMMIT;

DELETE FROM audit_details
WHERE cikey IN (SELECT /*+ ORDERED */ ad.cikey FROM audit_details ad,
temp_cube_instance tpic
WHERE ad.cikey = tpic.cikey);
COMMIT;

DELETE FROM sync_trail
WHERE cikey IN (SELECT /*+ ORDERED */ st.cikey FROM sync_trail st, temp_cube_instance tpic
WHERE st.cikey = tpic.cikey);
COMMIT;

DELETE FROM sync_store
WHERE cikey IN (SELECT /*+ ORDERED */ ss.cikey FROM sync_store ss, temp_cube_instance tpic
WHERE ss.cikey = tpic.cikey);
COMMIT;

DELETE FROM xml_document
WHERE dockey IN (SELECT /*+ ORDERED */ doc_ref.dockey FROM document_ci_ref doc_ref,
temp_cube_instance tpic
WHERE doc_ref.cikey = tpic.cikey);
COMMIT;

DELETE FROM document_dlv_msg_ref
WHERE dockey IN (SELECT /*+ ORDERED */ doc_ref.dockey FROM document_ci_ref doc_ref,
temp_cube_instance tpic
WHERE doc_ref.cikey = tpic.cikey);
COMMIT;

DELETE FROM document_ci_ref
WHERE cikey IN (SELECT /*+ ORDERED */ dcr.cikey FROM document_ci_ref dcr,
temp_cube_instance tpic
WHERE dcr.cikey = tpic.cikey);
COMMIT;

DELETE FROM attachment
WHERE key IN (SELECT /*+ ORDERED */ attach_ref.key FROM attachment_ref attach_ref,
temp_cube_instance tpic
WHERE attach_ref.cikey = tpic.cikey);
COMMIT;

DELETE FROM attachment_ref
WHERE cikey IN (SELECT /*+ ORDERED */ ar.cikey FROM attachment_ref ar,
temp_cube_instance tpic
WHERE ar.cikey = tpic.cikey);
COMMIT;

DELETE FROM ci_indexes
WHERE cikey IN (SELECT /*+ ORDERED */ cin.cikey FROM ci_indexes cin,
temp_cube_instance tpic
WHERE cin.cikey = tpic.cikey);
COMMIT;

-- DELETE FROM wi_fault
-- WHERE cikey IN (SELECT /*+ ORDERED */ wf.cikey FROM wi_fault wf,
-- temp_cube_instance tpic
-- WHERE wf.cikey = tpic.cikey);
-- COMMIT;

DELETE FROM cube_instance
WHERE cikey IN (SELECT /*+ ORDERED */ ci.cikey FROM cube_instance ci,
temp_cube_instance tpic
WHERE ci.cikey = tpic.cikey);
COMMIT;

-- Purge all handled invoke_messages older than specified date --
DELETE FROM xml_document
WHERE dockey IN (SELECT /*+ ORDERED */ dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref,
temp_invoke_message tpiim
WHERE dlv_ref.message_guid = tpiim.message_guid);
COMMIT;

DELETE FROM document_ci_ref
WHERE dockey IN (SELECT /*+ ORDERED */ dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref,
temp_invoke_message tpiim
WHERE dlv_ref.message_guid = tpiim.message_guid);
COMMIT;

DELETE FROM document_dlv_msg_ref
WHERE message_guid IN (SELECT /*+ ORDERED */ dlv_ref.message_guid FROM document_dlv_msg_ref dlv_ref,
temp_invoke_message tpiim
WHERE dlv_ref.message_guid = tpiim.message_guid);
COMMIT;

DELETE FROM invoke_message
WHERE message_guid IN (SELECT /*+ ORDERED */ im.message_guid FROM invoke_message im,
temp_invoke_message tpiim
WHERE im.message_guid = tpiim.message_guid);
COMMIT;

-- Purge all handled callback messages older than specified date --
DELETE FROM xml_document
WHERE dockey IN (SELECT /*+ ORDERED */ dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref,
temp_dlv_message tpidm
WHERE dlv_ref.message_guid = tpidm.message_guid);
COMMIT;

DELETE FROM document_ci_ref
WHERE dockey IN (SELECT /*+ ORDERED */ dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref,
temp_dlv_message tpidm
WHERE dlv_ref.message_guid = tpidm.message_guid);
COMMIT;

DELETE FROM document_dlv_msg_ref
WHERE message_guid IN (SELECT /*+ ORDERED */ ddmr.message_guid FROM document_dlv_msg_ref ddmr,
temp_dlv_message tpidm
WHERE ddmr.message_guid = tpidm.message_guid);
COMMIT;

DELETE FROM dlv_message
WHERE message_guid IN (SELECT /*+ ORDERED */ dm.message_guid FROM dlv_message dm,
temp_dlv_message tpidm
WHERE dm.message_guid = tpidm.message_guid);
COMMIT;

-- delete all unreferenced xml_documents rows from xml_document table
DELETE FROM xml_document xd
WHERE NOT EXISTS (SELECT ddmr.dockey FROM document_dlv_msg_ref ddmr
WHERE xd.dockey = ddmr.dockey) AND NOT EXISTS (SELECT dir.dockey FROM document_ci_ref dir WHERE xd.dockey = dir.dockey);
COMMIT;

-- IF conversation_id is not present in dlv_subscription, we can delete it from native_correlation
DELETE FROM native_correlation nc
WHERE NOT EXISTS (SELECT dlvs.conv_id from dlv_subscription dlvs
WHERE dlvs.conv_id = nc.conversation_id);
COMMIT;

DELETE FROM process_log
WHERE event_date < p_older_than;

EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_cube_instance';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_invoke_message';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_dlv_message';
COMMIT;

END purge_instances;

The above procedure shall truncate all the BPEL instance tables, but in order to reclaim the dataspace and refresh the indexes and you should run the another script (bpel_dehyd_manage.sql). I will publish the script in the next post.

You can run the above procedure as, call purge_instances(sysdate - ).