How to find purgeable instances in SOA/BPM 12c

December 30, 2015 | 3 minute read
Derek Kam
Consulting Solutions Architect
Text Size 100%:

If you are familiar with SOA/BPM 11g purging, after you have upgraded/implemented SOA/BPM 12c, you will not be able to use most of the SQL for 11g to determine the purgeable instances.  This is because SOA/BPM 12c is no longer using a composite_instance table for composite instance tracking.

In SOA/BPM 12c, a common component is used to track the state associated with the business flow and report audit information.  This design will reduce the instance tracking data generated and stored in the database, and improve purge performance by minimizing the number of tables that need to be accessed.  Component instance state will no longer be stored in an individual table, for instance, tracking purpose, the overall flow state will be stored in SCA_FLOW_INSTANCE table.

In SCA_FLOW_INSTANCE table, the “active_component_instances” column keeps track of how many component instances are still in a running/active state. These are the instances in one of the following states:

  • RUNNING

  • SUSPENDED

  • MIGRATING

  • WAITING_ON_HUMAN_INTERVENTION

When the “active_component_instances” value reaches 0, this indicates that the Flow is no longer executing. There is another column called “recoverable_faults”, this column keeps track of how many faults can be recovered. This information together with the “active_component_instances” is used to determine whether the Flow can be purged or not.

The SCA_FLOW_ASSOC table is used to record the association between the original Flow that creates the BPEL component instance and the correlated Flow. The SCA_FLOW_ASSOC table is used by the purge logic to ensure that all correlated Flows are purged together when none of the flow is in an active state.

Another important thing to take note: if you create a SOAINFRA schema with a LARGE database profile, all transactional tables will be created with range-partition. If you decide to run the SOA purging with the purge script either manually by running the stored procedure or by using auto-purge function which can be configured in Oracle Enterprise Manager Fusion Middleware Control, you will need to set the purge_partitioned_component => true (default is false), otherwise the purge logic will skip all partitioned tables when the purge script run and no flow instance will be purged.  You will be able to find all the partition tables in your SOAINFRA schema by using the following SQL:

select table_name from user_tables where partitioned = 'YES';

You can use the following sample PL/SQL to determine whether the SCA_FLOW_INSTANCE has been partitioned and the number of purgeable flow instances in your SOAINFRA schema.

set serveroutput on;
DECLARE MAX_CREATION_DATE TIMESTAMP;
MIN_CREATION_DATE TIMESTAMP;
batch_size INTEGER;
retention_period TIMESTAMP;
purgeable_instance INTEGER;
table_partitioned INTEGER;
BEGIN MAX_CREATION_DATE: = to_timestamp('2015-12-27', 'YYYY-MM-DD');
MIN_CREATION_DATE: = to_timestamp('2015-12-01', 'YYYY-MM-DD');
retention_period: = to_timestamp('2015-12-27', 'YYYY-MM-DD');
batch_size: = 100000;

if retention_period < max_creation_date 
 then retention_period: = max_creation_date;
end if;

select count(table_name) into table_partitioned from user_tables where partitioned = 'YES'
and table_name = 'SCA_FLOW_INSTANCE';

if table_partitioned > 0 
 then DBMS_OUTPUT.PUT_LINE('SCA_FLOW_INSTANCE is partitioned ');
else DBMS_OUTPUT.PUT_LINE('SCA_FLOW_INSTANCE is not partitioned ');
end if;

SELECT Count(s.flow_id) into purgeable_instance FROM sca_flow_instance s 
WHERE s.created_time >= MIN_CREATION_DATE 
AND s.created_time <= MAX_CREATION_DATE 
AND s.updated_time <= retention_period 
AND s.active_component_instances = 0 
AND s.flow_id NOT IN(
  SELECT r.flow_id FROM temp_prune_running_insts r) 
AND s.flow_id IN(
    SELECT c.flow_id FROM sca_flow_to_cpst c, sca_entity e, sca_partition p 
    WHERE c.composite_sca_entity_id = e.id and e.sca_partition_id = p.id) 
AND rownum <= batch_size;

DBMS_OUTPUT.PUT_LINE('Total purgeable flow instance: ' || purgeable_instance);

END;
/

Derek Kam

Consulting Solutions Architect

Derek Kam is a Consulting Solutions Architect in the A-Team at Oracle Corporation. He works closely with customers and partners, worldwide, providing guidance on architecture, best practices, troubleshooting and how best to use Oracle Cloud Services and products to solve customer business needs. Derek is a multi-skilled IT professional with more than 26 years of experience, possessing a wide range of experience and expertise in the Oracle Fusion Middleware and Oracle Clouds (PaaS and IaaS) technical and architecture design, development, software testing and quality assurance, and project management. Prior to joining Oracle in 2012, Derek has worked in consulting, financial and retail industries.


Previous Post

Oracle Analytics Cloud (OAC) - Using Pipelined Table Functions in Answers (Analysis) Dashboards - SOAP/REST API's

Jay Pearson | 5 min read

Next Post


Integrating Oracle Sales Cloud with Oracle Analytics Cloud (OAC)

Jay Pearson | 12 min read