SOA DB Adapter Performance Degradation when Operating on Non-Indexed Tables (Doc ID 1448150.1)

Last updated on OCTOBER 18, 2016

Applies to:

Oracle SOA Platform - Version 11.1.1.6.0 and later
Information in this document applies to any platform.

Symptoms

One of the new features of 11.1.1.6 SOA release is the potential to use coherence cache for frequently used data.

In this case the following architecture is used:

db adapter -> eclipse link -> coherence cache -> eclipse link -> database

Using coherence cache can result in significant improvement of the performance of the database queries.

However, some of this performance benefit can be lost when performing operations on non-indexed tables.

From the DBAdapter MasterDetail test case, observed when cacheUsage is set to read-write and if outbound table has no PK index on the joint column,
coherence will perform really poor during write-behind.

For example, in case of the demo employee table coherence will run the following query

MERGE INTO SENDER_EMP t0 USING
(SELECT :1 EMPNO FROM DUAL) t1 ON (t0.EMPNO = t1.EMPNO)
WHEN MATCHED
THEN UPDATE SET t0.ENAME = :2 , t0.JOB = :3 , t0.MGR =
:4 , t0.HIREDATE = :5 , t0.SAL = :6 , t0.COMM = :7 , t0.DEPTNO = :8
WHEN NOT MATCHED
THEN INSERT (t0.EMPNO, t0.ENAME, t0.JOB, t0.MGR, t0.HIREDATE, t0.SAL,
t0.COMM, t0.DEPTNO) VALUES (:9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 )

when updating the underlying database table.

The results of the tests done by Oracle SOA development using Exalogic have shown that if there is no index on the EMPNO column of the emp table - there might be a performance degradation of more then a factor of 20.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms