Thursday, 5 November 2009

Oracle Coherence - read-write-backing-map-scheme

I spent the last 2 weeks in Boston working with the oracle coherence team and how that product works and I am impressed with it. One thing which I was keen on testing out / learning in depth was the read-write-backing-map-scheme. I specifically wanted to learn write-behind which when enabled the cache will delay writes to the back end cache store, which in my example would be an Oracle 11g database. To me there is really only 2 options for this to be as quick as possible and of course use connection pooling which UCP (Oracle Universal Connection Pool) is perfect for.

1. JDBC Batching
2. PLSQL bulk binds

Whats important though is that we only commit after X amount of records, and only go to the database when we are ready to insert those X amount of records in a single round trip. To me PLSQL bulk binds make sense here, as all it takes is to convert those cache entries into an SQL object which the database can interpret from PLSQL. Once that's done your PLSQL is as simple as this really.


FORALL i IN mid.FIRST..mid.LAST
insert into messages (message_id, message_type, message)
values (mid(i), mt(i), m(i));

COMMIT;

I will post a full example on this shortly which also shows how to read the data back into the cache.

More about this can be found here on what I want to setup. My initial testing showed 1,000,000 records inserted without too much issues BUT there are a couple of things to be aware from coherence and oracle itself.

http://coherence.oracle.com/display/COH35UG/Read-Through%2C+Write-Through%2C+Write-Behind+and+Refresh-Ahead+Caching

4 comments:

Anonymous said...

Could you pelase post the example which you mentioned. Thanks

Pas Apicella said...

Took me a while to get this going but if you have access My Oracle Support you can download / run the demo as follows

Write-Behind Caching Using An Oracle Database and PLSQL Bulk Binds (Doc ID 970104.1)

PLSQL Bulk binds through JDBC involves a little more code but worth the effort if your inserting large/frequent amounts of records through a cache store.

It's all documented in the note above.

Anonymous said...

Hi, is the above document still available? any example code would be much appreciated

Pas Apicella said...

You have to log into oracle support portal for this code.