Saturday, July 09, 2011

SOA 11g PS3/PS4: Significant Purging Performance Improvement

Create the following index in the *_soainfra schema to improve the purging significantly. And with significant I mean SIGNIFICANT in upper-case :-). We have loads of instances too purge, +100K. The purging first took more than one hour. After applying the index it took minutes...

CREATE INDEX DLV_MESSAGE_CIKEY_IDX1 ON DLV_MESSAGE
(CIKEY)
LOGGING
TABLESPACE SOAINFRA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Friday, July 08, 2011

SOA Suite 11g: AQ a tuning tip

When you use AQ to dequeue message to the SOA Suite, keep in mind the maximum number of connections to your database.

A 4 node OSB cluster the does the dequeue and sends the messages to a 4 node SOA11g Cluster could lead that the SOA11g server will get stucked.

The server can get overloaded in the following situation:
  • When there are a lot of messages in the queue, for example 8000+ messages.
  • You stopped the SOA11g for maintaince 
  • You disabled the AQ-Proxy service in OSB.
When you start the SOA11g environment, you will notice that alle servers and their services/processes are running normally. But...

The issue is when you want to enable the AQ-Proxy service in OSB. This will lead that all OSB servers in the cluster will dequeue the messages and sends them to SOA11g.  SOA11g is trying to process all the services, but will fail at the end.

This is because it reach the maximum number of session in the database. This is not shown in the log files. The SOA11g log file says that the database "SOALocalTxDataSource" is suspended. This is not the case if you look in the Weblogic console (!). While the database alert log file says it reach the maximum session to the database.

This overloading can be solved by setting the maximum connections to the database for the datasources "SOALocalTxDataSource" and "SOADataSource".

In our case we have a 4-node cluster. This results in:
SUM(
    ("SOALocalTxDataSource" -> Maximum Capacity * 4)
   +
    ("SOADataSource" -> Maximum Capacity * 4)
)
If max capacity is 40, the total connections reach 320 sessions (!), when you enable the proxy service on OSB.

Make sure you can create enough sessions to your database.

Friday, July 01, 2011

Purging SOA Suite 11g, the Extreme Edition

Did you ever wondered how purging is working in SOA 11g? I tried to look into the scripts from PS1 until PS4. From PS3 they redesigned the concept and focussed on scheduling and parallel execution. My experiences are mixed; parallel execution is nice, but adding some indexes solves our performance issue with purging of 400K+ instances. A document is made to describe what the best purging approach is.

It does not describe my approach :-). I think the PL/SQL packages are to complicated. This is due to the fact that the data model is not documented and does not contain (optional) foreign-keys. This makes the data model not efficient to understand if you have issues with it.

We discovered, in our production environment, that not all instances where purged. It was not possible to force the purge script to delete all the instances. Discussing with Oracle, results that purging can be done, via Enterprise Manager (EM). With EM you can delete them..., only one-by-one. If you have 40K+ of instances, this is not the way to go.

So, I dived into the various purging scripts from PS4 and try to understand them. I want a script that deletes ALL the instances older than X days. And yes, deleting all the instances, I could loose messages.

So this is my current result; an SQLPlus script that can be executed at any time (scheduled with crontab or windows scheduler).

spool soa11g_purge_script.log

set echo on
set verify on
set timing on

-- delete all instances older then 3 days
define days=3

alter session set current_schema=kim1_soainfra;
alter session set nls_date_format='yyyymmdd hh24mi';

variable cur_datetime varchar2(13)
exec select (sysdate - &days) into :cur_datetime from dual;

prompt Purging data until:
print cur_datetime
--
-- Purge the MEDIATOR data
--       
delete from mediator_case_instance  a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from mediator_audit_document a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from mediator_callback       a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from mediator_group_status   a where exists (select b.id from mediator_instance b where b.group_id = a.group_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));

delete from mediator_payload             where modify_date   < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from mediator_deferred_message    where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from mediator_resequencer_message where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from mediator_case_detail         where created_time  < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from mediator_correlation         where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from mediator_instance            where created_time  < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
commit;

--
-- Purge the BPEL data
--       
delete from headers_properties          where modify_date   < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from ag_instance                 where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from audit_counter               where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from audit_trail                 where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from audit_details               where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from ci_indexes                  where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from work_item                   where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from wi_fault                    where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from xml_document_ref a          where exists (select b.document_id from xml_document b where b.document_id = a.document_id and b.doc_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi')); 
delete from xml_document                where doc_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from document_dlv_msg_ref        where dlv_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from document_ci_ref             where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from dlv_subscription            where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from dlv_message                 where receive_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from rejected_msg_native_payload where rm_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from instance_payload            where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from test_details a              where exists (select b.cikey from cube_instance b where b.cikey = a.cikey and b.creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi')); 
delete from cube_scope                  where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from cube_instance               where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
commit;

--
-- Purge the BPM data
--       
delete from bpm_audit_query               where create_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from bpm_measurement_actions       where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from bpm_measurement_action_exceps where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from bpm_cube_auditinstance        where cipartitiondate < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from bpm_cube_taskperformance      where creationdate < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from bpm_cube_processperformance   where creationdate < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
commit;

--
-- Purge the WORKFLOW data
--       
delete from wftask_tl                 a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wftaskhistory             a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wftaskhistory_tl          a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfcomments                a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfmessageattribute        a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfattachment              a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfassignee                a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfreviewer                a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfcollectiontarget        a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfroutingslip             a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfnotification            a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wftasktimer               a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wftaskerror               a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfheaderprops             a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wfevidence                a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wftaskassignmentstatistic a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from wftaskaggregation           where taskcreateddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from wftask                      where createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
commit;

--
-- Purge the COMPOSITE data
--       
delete from composite_sensor_value   where date_value    < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from composite_instance_assoc where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from attachment c             where exists (select a.key from attachment_ref a where a.key = c.key and exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi')));
delete from attachment_ref a         where exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
delete from composite_instance_fault where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from reference_instance       where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from component_instance       where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 
delete from composite_instance       where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'); 

commit;

--
-- Reclaim disk space
--
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 mediator_instance enable row movement;
alter table mediator_instance shrink space;
alter table mediator_instance 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 wftaskassignmentstatistic enable row movement;
alter table wftaskassignmentstatistic shrink space;
alter table wftaskassignmentstatistic 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 composite_instance_fault enable row movement;
alter table composite_instance_fault shrink space;
alter table composite_instance_fault disable row movement;
alter table reference_instance enable row movement;
alter table reference_instance shrink space;
alter table reference_instance 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 composite_instance enable row movement;
alter table composite_instance shrink space;
alter table composite_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 dm_pk 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 ds_pk rebuild online;
alter index header_properties_pk 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 state_type_date rebuild online;
alter index wf_crdate_cikey rebuild online;
alter index wf_crdate_type rebuild online;
alter index wf_fk2 rebuild online;
alter index wifault_pk rebuild online;
alter index wi_expired rebuild online;
alter index wi_key_crdate_state rebuild online;
alter index wi_pk rebuild online;
alter index wi_stranded rebuild online;
alter index xml_doc_reference_pk rebuild online

spool off