Oracle SOA Suite 11g tuning tips for Oracle RAC database 11.2

During my project work I had to tune the SOA dehydration store on more than one occasion. Through this posting I would like to share the tuning tips collected during these exercises. It is not a step-by-step guide, because with tuning there never is a 'one-size-fits-all' in my humble opinion, but I provide in general a guidance that you can use as a reference for your own situation. I will also refer to related Oracle documentation where available.

The reference installation I have based my tips on is a 2 node SOA 11.1.1.7 clustered environment connected to a 2 node Oracle Database RAC 11.2.0.3.4 environment (non-Exadata).

Database settings

Database settings that worked that were proven to work best for my projects:

SETTING	VALUE	COMMENT
processes	1200
sessions	665
session_cached_cursors	100
parallel_max_servers	5
session_cached_cursors	300
LOG_BUFFER	1048576+
Db_cache_size *	0 or 1000m+
undo_retention	0
memory_target *	15g
memory_max_target *	15g
sga_target *	0	Automatic memory management
Sga_max_size *	0	Automatic memory management
Shared_servers	0 (use dedicated services)
DISTRIBUTED_LOCK_TIMEOUT	700 (must > JTA transaction time out)

Index partitioning for RAC

For tuning the SOA dehydration store for RAC I used the following Oracle document as a reference

http://www.oracle.com/technetwork/database/availability/maa-fmw-soa-racanalysis-427647.pdf

For reducing index contention in RAC database I partitioned the following indexes (the default partition settings where sufficient for my projects)

• WI_STRANDED / WORK_ITEM
• DLV_MESSAGE_CIKEY / DLV_MESSAGE
• DLV_MESSAGE_DATE_ECID / DLV_MESSAGE
• AT_PK / AUDIT_TRAIL
• DLV_MESSAGE_PK / DLV_MESSAGE
• CS_PK / CUBE_SCOPE
• STATE_TYPE_CIKEY / DLV_MESSAGE

Secure Files for optimized LOB storage

Oracle Fusion Middleware Service Oriented Architecture (SOA) Suite is a database intensive middleware system with multiple components that store many different types of data in the Database. During a single invocation of a composite, multiple inserts and updates of unstructured data like documents, messages, faults and payloads may take place. The amount of data in the Oracle SOA Suite database grows very quickly and this rapid growth is especially relevant for such unstructured data as it may affect not only the manageability of the database, but also its performance. Audit Trails, Business Decision Rules, Sensors, EDN and multiple other objects in the Oracle FW SOA Schemas make intensive use of unstructured data in lobs, clobs and blobs. SecureFiles is a feature introduced in Oracle Database 11g that is specifically engineered to deliver high performance for this type of unstructured data. (Source: http://www.oracle.com/technetwork/database/availability/oraclefmw-soa-11gr1-securefiles-1842740.pdf)

Beside other benefits Secure Files eliminates the infamous (HW) enqueue contention wait events with using Basic Files for LOB storage. In most cases for me the (HW) enqueue contention wait events were the main reason for performance issues with the SOA DB.

Additional settings:

DEDUPLICATION and COMPRESSION set to LOW (Note: requires additional DB Compression license)

PCTFREE to 20 for the following tables:

o    composite_instance

o    cube_instance

o    cube_scope

o    dlv_messages

o    dlv_subscriptions

o    xml_document

o    mediator_payload

o    mediator_case_detail

o    mediator_audit_document

o    audit_details

I did not experimented with table partitioning because without table partitioning performance requirements were already met on my projects. But if you want or have to get the most out of the configuration, I recommend to consider table partitioning based on the performance numbers provided in the SOA Secure Files white paper.

RAC patches on top of 11.2.0.3.4
I recommend to always use the latest patch bundle 4 on top of 11.2.0.3 (latest non-Exadata patchset for 11.2) and install the following one-off patches to solve specific Secure Files issues:

• 13787307
• 13775960
  
• 12614359

Tablespace separation 

As a best practice I also recommend to separate the indexes and LOB segments into separate table spaces. Especially on SOA 10g using Basic Files and RAC I have noticed a significant reduction of wait events when using separate table spaces for the indexes and LOB segments so I have reused this best practices when introduction secure files. Also from a space management perspective this gives benefits.

As a final note, I want to mention 2 things: 

1) I want to state again that the provided tuning guidelines in this posting serve as a reference not meaning that they will provide the most optimal performance in all situations...at least I can say that they probably won't harm.

2) Leverage AWR reports or the Oracle Enterprise Manager 12c to monitor & analyse DB performance during SOA performance testing. On many occasions they proved to be my best friend to find performance bottlenecks.