Purging BPEL dehydration store

In some cases you need to purge the contents of the BPEL dehydration store (cleanup development environment, etc). The following script truncates the relevant tables and releases the space allocated to them:

truncate table orabpel.cube_instance;
truncate table orabpel.cube_scope;
truncate table orabpel.work_item;
truncate table orabpel.wi_exception;
truncate table orabpel.document_ci_ref;
truncate table orabpel.document_dlv_msg_ref;
truncate table orabpel.scope_activation;
truncate table orabpel.dlv_subscription;
truncate table orabpel.audit_trail;
truncate table orabpel.audit_details;
truncate table orabpel.sync_trail;
truncate table orabpel.sync_store;
truncate table orabpel.dlv_message;
truncate table orabpel.invoke_message;
truncate table orabpel.ci_indexes;
truncate table orabpel.native_correlation;
truncate table orabpel.xml_document;
 
alter table orabpel.cube_instance deallocate unused;
alter table orabpel.cube_scope deallocate unused;
alter table orabpel.work_item deallocate unused;
alter table orabpel.wi_exception deallocate unused;
alter table orabpel.document_ci_ref deallocate unused;
alter table orabpel.document_dlv_msg_ref deallocate unused;
alter table orabpel.scope_activation deallocate unused;
alter table orabpel.dlv_subscription deallocate unused;
alter table orabpel.audit_trail deallocate unused;
alter table orabpel.audit_details deallocate unused;
alter table orabpel.sync_trail deallocate unused;
alter table orabpel.sync_store deallocate unused;
alter table orabpel.dlv_message deallocate unused;
alter table orabpel.invoke_message deallocate unused;
alter table orabpel.ci_indexes deallocate unused;
alter table orabpel.native_correlation deallocate unused;
alter table orabpel.xml_document deallocate unused;
 
alter table orabpel.cube_instance enable row movement;
alter table orabpel.cube_instance shrink space compact;
alter table orabpel.cube_instance shrink space;
alter table orabpel.cube_instance disable row movement;
 
alter table orabpel.cube_scope enable row movement;
alter table orabpel.cube_scope shrink space compact;
alter table orabpel.cube_scope shrink space;
alter table orabpel.cube_scope disable row movement;
 
alter table orabpel.work_item enable row movement;
alter table orabpel.work_item shrink space compact;
alter table orabpel.work_item shrink space;
alter table orabpel.work_item disable row movement;
 
alter table orabpel.wi_exception enable row movement;
alter table orabpel.wi_exception shrink space compact;
alter table orabpel.wi_exception shrink space;
alter table orabpel.wi_exception disable row movement;
 
alter table orabpel.document_ci_ref enable row movement;
alter table orabpel.document_ci_ref shrink space compact;
alter table orabpel.document_ci_ref shrink space;
alter table orabpel.document_ci_ref disable row movement;
 
alter table orabpel.document_dlv_msg_ref enable row movement;
alter table orabpel.document_dlv_msg_ref shrink space compact;
alter table orabpel.document_dlv_msg_ref shrink space;
alter table orabpel.document_dlv_msg_ref disable row movement;
 
alter table orabpel.scope_activation enable row movement;
alter table orabpel.scope_activation shrink space compact;
alter table orabpel.scope_activation shrink space;
alter table orabpel.scope_activation disable row movement;
 
alter table orabpel.dlv_subscription enable row movement;
alter table orabpel.dlv_subscription shrink space compact;
alter table orabpel.dlv_subscription shrink space;
alter table orabpel.dlv_subscription disable row movement;
 
alter table orabpel.audit_trail enable row movement;
alter table orabpel.audit_trail shrink space compact;
alter table orabpel.audit_trail shrink space;
alter table orabpel.audit_trail disable row movement;
 
alter table orabpel.audit_details enable row movement;
alter table orabpel.audit_details shrink space compact;
alter table orabpel.audit_details shrink space;
alter table orabpel.audit_details disable row movement;
 
alter table orabpel.sync_trail enable row movement;
alter table orabpel.sync_trail shrink space compact;
alter table orabpel.sync_trail shrink space;
alter table orabpel.sync_trail disable row movement;
 
alter table orabpel.sync_store enable row movement;
alter table orabpel.sync_store shrink space compact;
alter table orabpel.sync_store shrink space;
alter table orabpel.sync_store disable row movement;
 
alter table orabpel.dlv_message enable row movement;
alter table orabpel.dlv_message shrink space compact;
alter table orabpel.dlv_message shrink space;
alter table orabpel.dlv_message disable row movement;
 
alter table orabpel.invoke_message enable row movement;
alter table orabpel.invoke_message shrink space compact;
alter table orabpel.invoke_message shrink space;
alter table orabpel.invoke_message disable row movement;
 
alter table orabpel.ci_indexes enable row movement;
alter table orabpel.ci_indexes shrink space compact;
alter table orabpel.ci_indexes shrink space;
alter table orabpel.ci_indexes disable row movement;
 
alter table orabpel.native_correlation enable row movement;
alter table orabpel.native_correlation shrink space compact;
alter table orabpel.native_correlation shrink space;
alter table orabpel.native_correlation disable row movement;

alter table orabpel.xml_document enable row movement;
alter table orabpel.xml_document shrink space compact;
alter table orabpel.xml_document shrink space;
alter table orabpel.xml_document disable row movement;

Optimizing RAC performance for SOA - Part 2

As promised in part 1, here is the ddl for using non-assm blob storage with cube_scope. There are assumptions here about the use of ASM for data storage and the existence of some pre-created tablespaces for indexes, etc. so your mileage may vary. The purpose is to illustrate how a manually-managed tablespace is created and used.

I should have similar ddl for the other tables with similar issues in a future post.

CREATE TABLESPACE "CUBE_SCOPE_LOB"
  DATAFILE '+DATA/soa/datafile/cslob.dbf'
  SIZE 2560M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE 5120M
  NOLOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32768K
  SEGMENT SPACE MANAGEMENT MANUAL;

CREATE TABLE orabpel.CUBE_SCOPE
(
  CIKEY INTEGER,
  DOMAIN_REF INTEGER,
  MODIFY_DATE TIMESTAMP(6),
  SCOPE_BIN BLOB
)
TABLESPACE ORABPEL
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
  INITIAL 32M
  PCTINCREASE 0
  FREELISTS 6
  FREELIST GROUPS 5
  BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (SCOPE_BIN) STORE AS
(
  TABLESPACE CUBE_SCOPE_LOB
  DISABLE STORAGE IN ROW
  CHUNK 8192
  PCTVERSION 0
  CACHE
  STORAGE (
    INITIAL 32M
    NEXT 32M
    PCTINCREASE 0
    freelists 6
    freelist group 5
    BUFFER_POOL DEFAULT
  )
)
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX CS_PK ON CUBE_SCOPE(CIKEY)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
  INITIAL 64K
  PCTINCREASE 0
  BUFFER_POOL DEFAULT
)
NOPARALLEL;

Hardware-based java processing

Interesting product for high-performance java processing:

http://www.azulsystems.com/products/compute_appliance.htm

Optimizing RAC database performance for SOA dehydration - Part 1

Dehydration store performance has turned out to be one of the biggest challenges in performance-tuning the Oracle SOA Suite for production deployment.  This has been an ongoing exercise over the past year or so, and as such is appropriate as my first post to this blog.  I expect that you will see commentary about RAC performance tuning as an ongoing subject.

The main areas of concern for us have been in High Watermark contention (aka HW contention) and global cache contention, particularly as it relates to index insertions.  Under the default database schema for the BPEL Process Manager, we originally saw a significant portion (on the order of 70-80%) of database wait time consumed by HW contention alone.

We discovered through some investigation that this problem was caused by the use of ASSM (automatic segment storage management), in particular on the cube_scope table's blob data.  By moving the blob storage for this table to a non-assm tablespace we were able to remove an enormous bottleneck in database performance.

Originally thought to be a RAC-specific issue, we have also demonstrated that it can occur in a stand-alone database (our development environment in particular) under conditions of heavy concurrency (lots of bpel processes running at once).

In the near future I will post the revised ddl for the cube_scope table as well as three other tables which can also benefit from this optimization.