Oracle SOA Suite 12c ships with a new technology adapter, the Oracle CoherenceAdapter. This adapter allows easy integration with a Coherence Data Grid (a distributed cache). The excellent post by Antony Reynolds provides the information required to setup your Coherence cache and get started with the adapter. In this blog post I will describe a pattern which can be used to use Coherence Data Grid as a cache for a database table (or view, or…). Also I did some performance measures on the Oracle quickstart JDeveloper installation with Integrated Weblogic server and an XE database with surprising results!

Important tips from Antony’s post:

• Use one Coherence cache per object type. This allows clearing the entire cache and specifying specific behavior per object type.
• Make sure the Coherence configuration file is available to every server in the cluster (shared storage). This is similar to a DbAdapter configuration plan.
• Set the servicename in the outbound connectionFactory of the CoherenceAdapter to DistributedCache so it works in clusters and WLSExtendProxy to false unless you are using this feature.
• In 11c you can use Coherence from within a SOA Suite Spring component or by enabling the OSB result cache. Both have disadvantages though. 12c provides the CoherenceAdapter as alternative.

The example provided in this post is to illustrate a pattern and do some simple measures. Not to provide a complete implementation. The purpose was a small tryout of the CoherenceAdapter.

Implementation

The sample implementation I used consists of 3 services and some database code.

CREATE TABLE REF_DATA (
ID NUMBER,
NAME VARCHAR2(50 CHAR),
VALUE VARCHAR2(1024 CHAR),
LAST_MODIFIED_TS TIMESTAMP,
LAST_CACHE_UPDATE_TS TIMESTAMP
)
CREATE SEQUENCE ref_data_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER biu_ref_data
BEFORE INSERT OR UPDATE ON TESTUSER.ref_data
FOR EACH ROW
BEGIN
IF UPDATING THEN
IF NOT (:new.name = :old.name and :new.value = :old.value) THEN
:new.last_modified_ts := systimestamp;
END IF;
END IF;
IF INSERTING THEN
:new.last_modified_ts := systimestamp;
IF :new.id IS NULL THEN
SELECT ref_data_seq.nextval INTO :new.id FROM DUAL;
END IF;
END IF;
END;

Noteworthy here is that a comparison of the LAST_MODIFIED_TS and the LAST_CACHE_UPDATE_TS indicates which entries need refreshing in the cache. In a situation where the cache is completely up to date, the LAST_CACHE_UPDATE_TS should be more recent then the last_modified_ts. The ‘if’-statement comparing new and old values in the trigger during an update, is to make sure that the cache is only updated if relevant values change.

CoherenceDataService

Antony has provided a sample data-service to access Coherence. For this sample, the service can be used as it is provided in the blog post (here). For your own project, you might want to change some things like for example namespaces. The provided service has a schema, mappings and adapter configurations. It uses a Mediator to map from the operations to the adapter properties. Keep in mind when using your own implementation of a Coherence data service with for example BPEL, you will loose some of the performance benefits the Mediator provides. BPEL provides more features like for example logging and extensive error handling options though. If you do decide to take this path, you can look at some suggestions here and here to increase BPEL performance (they are for 11g though and not updated to 12c).

ReferenceService

The ReferenceService contains the logic for using Coherence or the database. This service first queries Coherence. If a result is found, it is returned. If no result is found, the database is queried. If the database has data, Coherence is updated and the result is returned. If the database has no result, an empty result is returned. In order to conduct a performance test, I included a switch to use or not to use Coherence. This is a small (quick & dirty) sample process and you should not look at it in any other way.

Below is a more schematic representation of this service;

SynchronizationService

The SynchronisationService is responsible for monitoring changes in the table. This can be implemented in various ways. A polling DbAdapter is too limited because a simple logical delete is not able to update the table as we want it to (set the LAST_CACHE_UPDATE_TS to the current time). Also it does not have the ability to select the correct records (check where the LAST_CACHE_UPDATE_TS is empty or less than LAST_MODIFIED_TS). AQ (Oracle Advanced Queues) can be used. Just put a message on the queue from a trigger on the table and use the AQAdapter to trigger a BPEL process when messages arrive. You can see a sample of that (including code) here. This allows a quick update of the cache and is quite stable. A new feature in 12c is the Enterprise Scheduler Service (ESS). This service allows users to schedule when to call for example a SynchronisationService. A SynchronisationService can than use all the logic available in the DbAdapter to select the correct records and update those. In our case all the records where the LAST_CACHE_UPDATE_TS is empty or before the LAST_MODIFIED_TS.
Scheduling every couple of seconds though is not recommended so you will probably get slower cache updates when implementing ESS for this. Of course during server start, you can decide to fill the entire cache beforehand or to cache only the values which are requested. The second is more memory efficient but causes the first request for a value to be a bit slower.

Performance

The purpose of this exercise is of course to look at performance. We want a very fast reference service. Below follow the results.

What happens under load?

I used the 12c developer quickstart installation and an XE database (which runs on a single CPU and 1Gb RAM). When using a loadtest in SOAP UI with more then a single thread, a lot of errors occurred.

I continued the tests with a single thread to avoid these.

First request no data found

First request for a value which is not in Coherence or in the database. Two requests are performed. One to the Coherence. Next it tries the database and then it returns with no result. In case the data was found in the database, one additional request would be performed; a request for updating Coherence with the Db value. This was harder to simulate so I skipped it.

The response-time was 39.05ms on average. When I disabled audit logging the average response-time was 33.53ms.

When I used only a single composite (no separate Coherence data service):

Response time was 33.07ms which was slightly faster (although probably not significant) than using a separate Coherence data service composite. This indicates a separate Coherence data service composite causes very little additional overhead!

Keep in mind, in this case two calls are made. Let’s call them DB + CH (and overhead).

When not using Coherence at all

This was the fastest with an average response time of 26.74ms. When I disabled audit logging this was reduced to: 23.53ms. When I moved the Coherence calls from the data service to the composite, there was no mentionable change in performance, which is to be expected since it was only supposed to use the database.

Here only the DB call was made (and overhead).

Cache hits

This was a lot slower then just a DB call: 33.01ms. Without audit logging this was reduced to: 29.22ms.

With Coherence calls directly present in the composite (no separate data service), response time was slightly less: 28.10ms. Here also, little overhead of the Coherence data service.

Here only the CH call was made (and overhead).

A little math

DB + CH + Overhead = 37ms
DB + Overhead = 24ms
CH + Overhead = 28ms

Thus: DB=9ms, CH=13ms, Overhead=15ms

Overhead are things like the SOA infrastructure.

Conclusion

In this blog post I have provided a pattern which can be used to create something which functions similar to an in memory table in some ways. My expectation was that this would provide an increase in performance since the database didn’t need to be accessed for every value.

I did several performance measures on the quickstart JDeveloper version with integrated Weblogic server and an XE database.

• with and without audit logging
• with a separate Coherence data service and with the CoherenceAdapter configuration combined with the logic in a single composite
• only DBAdapter calls, only CoherenceAdapter calls and both
• with a single thread load-test and with several threads sending requests at the same time

During these measures I was surprised to find the following:

• the database adapter fetching a single value from a table was faster then the CoherenceAdapter fetching this same value (related to transactions, threading or other internal adapter mechanics?)
• the overhead of the framework in this case was larger than the time spend on the individual calls

Of course, you will gain much from using Coherence when the database is very slow and Coherence is used as distributed cache. You should however not use Coherence without thinking about your usecase. A simple fast reference table appeared to be a poor usecase.