SOA Performace Tuning Series (Part 1)

Deleting Large Numbers of Instances

The purge scripts for the SOA  suite can be found in $RCU_HOME 

/rcuHome/rcu/integration/soainfra/sql/purge

One All the scripts have been run and packages have been created. 

Create the instance filter to selectively delete instances. (RCU does create the instance_fileter type)

create or replace type instance_filter as object
(
composite_name varchar2(200),
composite_revision varchar2(20),
state integer,
min_created_date timestamp,
max_created_date timestamp,
);


composite_name: The name of the SOA composite application, such as OrderBooking.
composite_revision: The revision of the composite, such as 1.0.
state: The state of the instance. The following state values are possible:

STATE_RUNNING constant integer := 0;
STATE_RECOVERY_REQUIRED constant integer := 1;
STATE_COMPLETED_SUCCESSFULLY constant integer := 2;
STATE_FAULTED constant integer := 3;
STATE_TERMINATED_BY_USER constant integer := 4;
STATE_SUSPENDED constant integer := 5;
STATE_STALE constant integer := 6;
STATE_UNKNOWN constant integer := 32;
min_created_date: The minimum date range.
max_created_date: The maximum date range.

Create a stored procedure that will purge the instances

DECLARE
  FILTER QA_SOAINFRA.INSTANCE_FILTER := INSTANCE_FILTER();
  MAX_INSTANCES NUMBER;
  PURGE_PARTITIONED_DATA BOOLEAN;
  v_Return NUMBER;
BEGIN
 FILTER.COMPOSITE_PARTITION_NAME:='myPartition';
 FILTER.COMPOSITE_NAME := 'myComposite';
 FILTER.COMPOSITE_REVISION := '1.0';
 FILTER.STATE := fabric.STATE_FAULTED;

  MAX_INSTANCES := 10000000;
  PURGE_PARTITIONED_DATA := true;

  v_Return := FABRIC.DELETE_ALL(
    FILTER => FILTER,
    MAX_INSTANCES => MAX_INSTANCES,
    PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA
  );
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

For questions, comments and feedback,  please contact:
 Harvinder Singh Saluja