Oracle SOA Suite: Want performance? Don't log so much and clean up your database

The Oracle SOA Suite infrastructure, especially composites, use the database intensively. Not only are the process definitions stored in the database, also a lot of audit information gets written there. The SOA infrastructure database, if not well managed, will grow and will eventually have detrimental effects on performance. In this blog post I will give some quick suggestions that will help you increase performance of your SOA Suite infrastructure on the database side by executing some simple scripts. These are some suggestions I have seen work at different customers. Not only do they help managing the SOA Suite data in the database, they will also lead to better SOA Suite performance.

Do not log too much!

Less data is faster. If you can limit database growth, management becomes easier.

• Make sure the auditlevel of your processes is set to production level in production environments.
• Think about the BPEL setting inMemoryOptimization. This can only be set for processes that do not contain any dehydration points such as receive, wait, onMessage and onAlarm activities. If set to true, the completionpersistpolicy can be used to tweak what to do after completion of the process. For example only save information about faulted instances in the dehydration store. In 12c this setting is part of the 'Oracle Integration Continuous Availability' feature and uses Coherence.

Start with a clean slate regularly

Especially for development environments it is healthy to regularly truncate all the major SOAINFRA tables. The script to do this is supplied by Oracle: MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/truncate/truncate_soa_oracle.sql

The effect of executing this script is that all instance data is gone. This includes all tasks, long running BPM processes, long running BPEL processes, recoverable errors. For short everything except the definitions. The performance gain from executing the script can be significant. You should consider for example to run the script at the end of every sprint to start with a clean slate.

Delete instances 

Oracle has provided scripts to remove old instances. These are scheduled by default in a clean installation of 12c. If you upgrades from 11g to 12c, this scheduling is not enabled by default. The auto-purge feature of 12c is described here.

What this feature does is execute the standard supplied purge scripts: MW_HOME/SOA_ORACLE_HOME//rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql

In a normal SOA Suite 12c installation you can also find the scripts in MW_HOME/SOA_ORACLE_HOME/common/sql/soainfra/sql/oracle

In 12c installations, the patched purge scripts for older versions are also supplied. I would use the newest version of the scripts since the patches sometimes fix logic which can cause data inconsistencies which can have consequences later, for example during migrations.


What the scripts do is nicely described here. These scripts only remove instances you should not miss. Running instances and instances which can be recovered, are not deleted. In the script you can specify for how long data should be retained.

You should schedule this and run it daily. The shorter the period you keep information, the more you can reduce your SOAINFRA space usage and the better the performance of the database will be.

An example of how to execute the script:

DECLARE
  MAX_CREATION_DATE TIMESTAMP;
  MIN_CREATION_DATE TIMESTAMP;
  BATCH_SIZE        INTEGER;
  MAX_RUNTIME       INTEGER;
  RETENTION_PERIOD  TIMESTAMP;
BEGIN
  MIN_CREATION_DATE := TO_TIMESTAMP(TO_CHAR(sysdate-2000, 'YYYY-MM-DD'),'YYYY-MM-DD');
  MAX_CREATION_DATE := TO_TIMESTAMP(TO_CHAR(sysdate-30, 'YYYY-MM-DD'),'YYYY-MM-DD');
  RETENTION_PERIOD  := TO_TIMESTAMP(TO_CHAR(sysdate-29, 'YYYY-MM-DD'),'YYYY-MM-DD');
  MAX_RUNTIME       := 180;
  BATCH_SIZE        := 250000;

  SOA.DELETE_INSTANCES(
    MIN_CREATION_DATE    => MIN_CREATION_DATE,
    MAX_CREATION_DATE    => MAX_CREATION_DATE,
    BATCH_SIZE           => BATCH_SIZE,
    MAX_RUNTIME          => MAX_RUNTIME,
    RETENTION_PERIOD     => RETENTION_PERIOD,
    PURGE_PARTITIONED_COMPONENT => FALSE);

END;
/


The script also has a variant which can be executed in parallel (which is faster) but that requires extra grants for the SOAINFRA database user.

Shrink space

Tables 

Deleting instances will not free up space on the filesystem of the server. Nor does it make sure that the data is not fragmented over many tablespace segments. Oracle does not provide standard scripts for this but does tell you this is a good idea and explains why here (9.5.2). In addition you can rebuild indexes. You should also of course run a daily gather statistics on the schema.

For 11g you can use the below script to shrink space for tables and rebuild indexes. You should execute it under XX_SOAINFRA where XX if your schema prefix. 

alter table mediator_case_instance enable row movement;
alter table mediator_case_instance shrink space;
alter table mediator_case_instance disable row movement;
alter table mediator_audit_document enable row movement;
alter table mediator_audit_document shrink space;
alter table mediator_audit_document disable row movement;
alter table mediator_callback enable row movement;
alter table mediator_callback shrink space;
alter table mediator_callback disable row movement;
alter table mediator_group_status enable row movement;
alter table mediator_group_status shrink space;
alter table mediator_group_status disable row movement;
alter table mediator_payload enable row movement;
alter table mediator_payload shrink space;
alter table mediator_payload disable row movement;
alter table mediator_deferred_message enable row movement;
alter table mediator_deferred_message shrink space;
alter table mediator_deferred_message disable row movement;
alter table mediator_resequencer_message enable row movement;
alter table mediator_resequencer_message shrink space;
alter table mediator_resequencer_message disable row movement;
alter table mediator_case_detail enable row movement;
alter table mediator_case_detail shrink space;
alter table mediator_case_detail disable row movement;
alter table mediator_correlation enable row movement;
alter table mediator_correlation shrink space;
alter table mediator_correlation disable row movement;
alter table headers_properties enable row movement;
alter table headers_properties shrink space;
alter table headers_properties disable row movement;
alter table ag_instance enable row movement;
alter table ag_instance shrink space;
alter table ag_instance disable row movement;
alter table audit_counter enable row movement;
alter table audit_counter shrink space;
alter table audit_counter disable row movement;
alter table audit_trail enable row movement;
alter table audit_trail shrink space;
alter table audit_trail disable row movement;
alter table audit_details enable row movement;
alter table audit_details shrink space;
alter table audit_details disable row movement;
alter table ci_indexes enable row movement;
alter table ci_indexes shrink space;
alter table ci_indexes disable row movement;
alter table work_item enable row movement;
alter table work_item shrink space;
alter table work_item disable row movement;
alter table wi_fault enable row movement;
alter table wi_fault shrink space;
alter table wi_fault disable row movement;
alter table xml_document_ref enable row movement;
alter table xml_document_ref shrink space;
alter table xml_document_ref disable row movement;
alter table document_dlv_msg_ref enable row movement;
alter table document_dlv_msg_ref shrink space;
alter table document_dlv_msg_ref disable row movement;
alter table document_ci_ref enable row movement;
alter table document_ci_ref shrink space;
alter table document_ci_ref disable row movement;
alter table dlv_subscription enable row movement;
alter table dlv_subscription shrink space;
alter table dlv_subscription disable row movement;
alter table dlv_message enable row movement;
alter table dlv_message shrink space;
alter table dlv_message disable row movement;
alter table rejected_msg_native_payload enable row movement;
alter table rejected_msg_native_payload shrink space;
alter table rejected_msg_native_payload disable row movement;
alter table instance_payload enable row movement;
alter table instance_payload shrink space;
alter table instance_payload disable row movement;
alter table test_details enable row movement;
alter table test_details shrink space;
alter table test_details disable row movement;
alter table cube_scope enable row movement;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;
alter table cube_instance enable row movement;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;
alter table bpm_audit_query enable row movement;
alter table bpm_audit_query shrink space;
alter table bpm_audit_query disable row movement;
alter table bpm_measurement_actions enable row movement;
alter table bpm_measurement_actions shrink space;
alter table bpm_measurement_actions disable row movement;
alter table bpm_measurement_action_exceps enable row movement;
alter table bpm_measurement_action_exceps shrink space;
alter table bpm_measurement_action_exceps disable row movement;
alter table bpm_cube_auditinstance enable row movement;
alter table bpm_cube_auditinstance shrink space;
alter table bpm_cube_auditinstance disable row movement;
alter table bpm_cube_taskperformance enable row movement;
alter table bpm_cube_taskperformance shrink space;
alter table bpm_cube_taskperformance disable row movement;
alter table bpm_cube_processperformance enable row movement;
alter table bpm_cube_processperformance shrink space;
alter table bpm_cube_processperformance disable row movement;
alter table wftask_tl enable row movement;
alter table wftask_tl shrink space;
alter table wftask_tl disable row movement;
alter table wftaskhistory enable row movement;
alter table wftaskhistory shrink space;
alter table wftaskhistory disable row movement;
alter table wftaskhistory_tl enable row movement;
alter table wftaskhistory_tl shrink space;
alter table wftaskhistory_tl disable row movement;
alter table wfcomments enable row movement;
alter table wfcomments shrink space;
alter table wfcomments disable row movement;
alter table wfmessageattribute enable row movement;
alter table wfmessageattribute shrink space;
alter table wfmessageattribute disable row movement;
alter table wfattachment enable row movement;
alter table wfattachment shrink space;
alter table wfattachment disable row movement;
alter table wfassignee enable row movement;
alter table wfassignee shrink space;
alter table wfassignee disable row movement;
alter table wfreviewer enable row movement;
alter table wfreviewer shrink space;
alter table wfreviewer disable row movement;
alter table wfcollectiontarget enable row movement;
alter table wfcollectiontarget shrink space;
alter table wfcollectiontarget disable row movement;
alter table wfroutingslip enable row movement;
alter table wfroutingslip shrink space;
alter table wfroutingslip disable row movement;
alter table wfnotification enable row movement;
alter table wfnotification shrink space;
alter table wfnotification disable row movement;
alter table wftasktimer enable row movement;
alter table wftasktimer shrink space;
alter table wftasktimer disable row movement;
alter table wftaskerror enable row movement;
alter table wftaskerror shrink space;
alter table wftaskerror disable row movement;
alter table wfheaderprops enable row movement;
alter table wfheaderprops shrink space;
alter table wfheaderprops disable row movement;
alter table wfevidence enable row movement;
alter table wfevidence shrink space;
alter table wfevidence disable row movement;
alter table wftaskaggregation enable row movement;
alter table wftaskaggregation shrink space;
alter table wftaskaggregation disable row movement;
alter table wftask enable row movement;
alter table wftask shrink space;
alter table wftask disable row movement;
alter table composite_sensor_value enable row movement;
alter table composite_sensor_value shrink space;
alter table composite_sensor_value disable row movement;
alter table composite_instance_assoc enable row movement;
alter table composite_instance_assoc shrink space;
alter table composite_instance_assoc disable row movement;
alter table attachment enable row movement;
alter table attachment shrink space;
alter table attachment disable row movement;
alter table attachment_ref enable row movement;
alter table attachment_ref shrink space;
alter table attachment_ref disable row movement;
alter table component_instance enable row movement;
alter table component_instance shrink space;
alter table component_instance disable row movement;
alter table audit_details modify lob (bin) (shrink space);
alter table composite_instance_fault modify lob (error_message) (shrink space);
alter table composite_instance_fault modify lob (stack_trace) (shrink space);
alter table cube_scope modify lob (scope_bin) (shrink space);
alter table reference_instance modify lob (error_message) (shrink space);
alter table reference_instance modify lob (stack_trace) (shrink space);
alter table test_definitions modify lob (definition) (shrink space);
alter table wi_fault modify lob (message) (shrink space);
alter table xml_document modify lob (document) (shrink space); 
alter index ad_pk rebuild online;
alter index at_pk rebuild online;
alter index ci_creation_date rebuild online;
alter index ci_custom3 rebuild online;
alter index ci_ecid rebuild online;
alter index ci_name_rev_state rebuild online;
alter index ci_pk rebuild online;
alter index composite_instance_cidn rebuild online;
alter index composite_instance_co_id rebuild online;
alter index composite_instance_created rebuild online;
alter index composite_instance_ecid rebuild online;
alter index composite_instance_id rebuild online;
alter index composite_instance_state rebuild online;
alter index cs_pk rebuild online;
alter index dm_conversation rebuild online;
alter index doc_dlv_msg_guid_index rebuild online;
alter index doc_store_pk rebuild online;
alter index ds_conversation rebuild online;
alter index ds_conv_state rebuild online;
alter index ds_fk rebuild online;
alter index instance_payload_key rebuild online;
alter index reference_instance_cdn_state rebuild online;
alter index reference_instance_co_id rebuild online;
alter index reference_instance_ecid rebuild online;
alter index reference_instance_id rebuild online;
alter index reference_instance_state rebuild online;
alter index reference_instance_time_cdn rebuild online;
alter index wf_crdate_cikey rebuild online;
alter index wf_crdate_type rebuild online;
alter index wf_fk2 rebuild online;
alter index wi_expired rebuild online;

LOBs 

LOB columns are saved outside of the tables and can be shrunk separately. In the below script you should replace XX_SOAINFRA with your SOAINFRA schema. The script explicitly drops BRDECISIONINSTANCE_INDX5 since the table can become quite large in development environments and you cannot shrink it with the index still on it. The below script also might overlap with the script above for tables with LOB columns. It only shrinks for large tables where the LOB columns take more than 100Mb of space. 

DECLARE
  CURSOR c_tabs
  IS
    SELECT
      a.owner owner,
      a.table_name table_name,
      a.column_name column_name
    FROM
      all_tab_cols a
    WHERE
      a.owner LIKE 'XX_SOAINFRA'
    AND a.data_type LIKE '%LOB'
    AND EXISTS
      (
        SELECT
          1
        FROM
          all_tables b
        WHERE
          a.owner       =b.owner
        AND a.table_name=b.table_name
      )
  AND EXISTS
    (
      SELECT
        1
      FROM
        dba_lobs l ,
        dba_segments s
      WHERE
        s.segment_name      = l.segment_name
      AND s.owner           = l.owner
      AND s.bytes/1024/1024 > 100
      AND s.owner           =a.owner
      AND l.table_name      =a.table_name
      AND l.column_name     =a.column_name
    )
  ORDER BY
    owner,
    table_name,
    column_name;
  r_prevtab c_tabs%rowtype;
  l_countlobrecs NUMBER;
FUNCTION hasFunctionBasedIndex(
    p_owner      VARCHAR2,
    p_table_name VARCHAR2)
  RETURN VARCHAR2
IS
  l_indexcount NUMBER;
BEGIN
  SELECT
    COUNT(*)
  INTO
    l_indexcount
  FROM
    all_indexes c
  WHERE
    c.table_owner =p_owner
  AND c.table_name=p_table_name
  AND c.index_type LIKE 'FUN%';
  IF l_indexcount>0 THEN
    RETURN 'Y';
  ELSE
    RETURN 'N';
  END IF;
END;
BEGIN
  begin
  execute immediate 'DROP INDEX XX_SOAINFRA.BRDECISIONINSTANCE_INDX5';
  dbms_output.put_line('Index created: XX_SOAINFRA.BRDECISIONINSTANCE_INDX5');
  exception
  when others then
  null;
  end; 
  r_prevtab.owner := NULL;
  FOR r_tabs IN c_tabs
  LOOP
    IF (r_prevtab.owner IS NOT NULL AND
      (
        r_prevtab.owner != r_tabs.owner OR r_prevtab.table_name !=
        r_tabs.table_name
      )
      ) OR r_prevtab.owner IS NULL THEN
      dbms_output.put_line('Processing table: "'||r_tabs.owner||'"."'||
      r_tabs.table_name||'"');
      IF hasFunctionBasedIndex(r_tabs.owner,r_tabs.table_name) = 'N' THEN
        EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
        r_tabs.table_name||'" deallocate unused';
        EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
        r_tabs.table_name||'" enable row movement';
        EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
        r_tabs.table_name||'" shrink space compact';
        BEGIN
          --below causes lock and sets high water mark
          EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
          r_tabs.table_name||'" shrink space';
        EXCEPTION
          --when a lock is present: skip
        WHEN OTHERS THEN
          dbms_output.put_line('Skipping shrink space due to: '||SQLERRM);
        END;
        EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
        r_tabs.table_name||'" disable row movement';
      END IF;
      r_prevtab := r_tabs;
    ELSE
      dbms_output.put_line(
      'Table has function based index and cannot be shrinked: "'|| r_tabs.owner
      ||'"."'||r_tabs.table_name||'"');
    END IF;
    dbms_output.put_line('Processing column: "'||r_tabs.owner||'"."'||
    r_tabs.table_name||'"."'||r_tabs.column_name||'"');
    EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||r_tabs.table_name
    || '" modify lob("'||r_tabs.column_name||'") (deallocate unused)';
    --below causes lock
    BEGIN
      EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
      r_tabs.table_name ||'" modify lob("'||r_tabs.column_name||
      '") (freepools 1)';
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Skipping freepools: '||SQLERRM);
    END;
    BEGIN
      EXECUTE immediate 'alter table "'||r_tabs.owner||'"."'||
      r_tabs.table_name ||'" modify lob("'||r_tabs.column_name||
      '") (shrink space)';
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Skipping shrink space: '||SQLERRM);
    END;
  END LOOP;
    begin
  execute immediate 'CREATE INDEX XX_SOAINFRA.BRDECISIONINSTANCE_INDX5 ON XX_SOAINFRA.BRDECISIONINSTANCE (ECID, "CREATION_TIME" DESC) LOGGING TABLESPACE XX_SOAINFRA NOPARALLEL';
  dbms_output.put_line('Index created: XX_SOAINFRA.BRDECISIONINSTANCE_INDX5');
  exception
  when others then
  null;
  end;
END;

Other database suggestions

Redo log size

Not directly related to cleaning, but related to SOAINFRA space management. The Oracle database uses so-called redo-log files to store all changes to the database. In case of a database instance failure, the database can use these redo-log files to recover. Usually there are two or more redo-logfiles. These files are rotated: if one is full, it goes to the next. When the last one is filled, it goes back to the first one overriding old data. Read more about redo-logs here. Rotating a redo-log file takes some time. When the redo-log files are small, they are rotated a lot. The following provides some suggestions in analyzing if increasing the size will help you. I've seen default values of 3 redo-log files of 100Mb. Oracle recommends having 3 groups of 2Gb each here.

Clean up long running and faulted instances!

The regular cleaning scripts which you might run on production do not clean instances which have an ECID which is the same as an instance which cannot be cleaned because it is for example still running or recoverable. If you have many processes running, you might be able to win a lot by for example restarting the running processes with a new ECID. You do have to build that functionality for yourself though. Also you should think about keeping track of time for tasks. If a certain task is supposed to only be open for a month, let it expire after a month. If you do not check this, you might encounter large numbers of tasks which remain open. This mains the instance which has created the task will remain open. This means you cannot undeploy the version of the process which has this task running. Life-cycle management is a thing!

Finally

SOAINFRA is part of the infrastructure

Oracle SOA Suite logs a lot of audit information in the SOAINFRA database. You might be tempted to join that information to other business data directly on database level. This is not a smart thing to do.

If the information in the SOAINFRA database is used to for example query BPM processes or tasks, especially when this information is being joined over a database link to another database with additional business data, you have introduced a timebomb. The performance will be directly linked to the amount of data in the SOAINFRA database and especially with long running processes and tasks. You have now not only introduced a potential performance bottleneck for all your SOA composites but also for other parts of your application.

It is not a system of records

Secondly, the business might demand you keep the information for a certain period. Eventually they might even want to keep the data forever and use it for audits of historic records. This greatly interferes with purging strategies, which are required if you want to keep your environment performant. If the business considers certain information important to keep, create a table and store the relevant information there.