More HW Contention tuning

There are three other lob columns that need to be moved to non-assm tablespaces to eliminate hw contention issues. We talked about cube_scope before, now we need to recreate tables audit_details, sync_store and xml_document with non-assm blob storage as well. Here is the sql script to do this:

DROP TABLE orabpel.AUDIT_DETAILS;
DROP TABLE orabpel.SYNC_STORE;
DROP TABLE orabpel.XML_DOCUMENT;

CREATE TABLESPACE AUDIT_DETAIL_LOB DATAFILE
'+DATA/soa/datafile/adlob01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE 5120M,
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE TABLESPACE SYNC_STORE_LOB DATAFILE
'+DATA/soa/datafile/stlob01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE 5120M,
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE TABLESPACE XML_DOC_LOB DATAFILE
'+DATA/soa/datafile/xdlob01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE 5120M,
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE TABLE orabpel.AUDIT_DETAILS
(
CIKEY INTEGER,
DOMAIN_REF INTEGER,
DETAIL_ID INTEGER,
BIN_CSIZE INTEGER,
BIN_USIZE INTEGER,
DOC_REF VARCHAR2(300 BYTE),
BIN BLOB
)
TABLESPACE ORABPEL
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BIN) STORE AS
( TABLESPACE AUDIT_DETAIL_LOB
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 6
FREELIST GROUPS 5
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX orabpel.AD_PK ON AUDIT_DETAILS
(CIKEY, DETAIL_ID)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE orabpel.AUDIT_DETAILS ADD (
CONSTRAINT AD_PK
PRIMARY KEY
(CIKEY, DETAIL_ID)
USING INDEX
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));


CREATE TABLE orabpel.SYNC_STORE
(
CIKEY INTEGER,
DOMAIN_REF INTEGER,
BIN_CSIZE INTEGER,
BIN_USIZE INTEGER,
BIN BLOB
)
TABLESPACE ORABPEL
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BIN) STORE AS
( TABLESPACE SYNC_STORE_LOB
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 6
FREELIST GROUPS 5
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX orabpel.SS_FK ON orabpel.SYNC_STORE
(CIKEY)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE TABLE orabpel.XML_DOCUMENT
(
DOCKEY VARCHAR2(200 BYTE),
DOMAIN_REF INTEGER,
BIN_CSIZE INTEGER,
BIN_USIZE INTEGER,
BIN BLOB,
MODIFY_DATE TIMESTAMP(6),
BIN_FORMAT INTEGER
)
TABLESPACE BPELDOC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BIN) STORE AS
( TABLESPACE xml_doc_lob
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
CACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 6
FREELIST GROUPS 5
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX orabpel.XML_DOC_PK ON XML_DOCUMENT
(DOCKEY)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
REVERSE;


ALTER TABLE orabpel.XML_DOCUMENT ADD (
CONSTRAINT XML_DOC_PK
PRIMARY KEY
(DOCKEY)
USING INDEX
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

B2B Patching Sequence

Current patching sequence for B2B (10.1.2.0.2):

4505133 - 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
4869010 - OC4J FAILS TO GUARANTEE VALID CONNECTIONS IN POOL TO RAC NODES
6083645 - MLR ON TOP OF 10.1.2.0.2/10.1.2.1 FOR CPUJUL2007

6488268 - B2B Patchset 5

SOA Best Practices Guide (10.1.3.x)

You would think this would be easier to find. I'm linking it here so that I don't have to go digging for it again.

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.