Sniffing redo logs to maintain cache consistency?

We have a Java application server that caches database records to reduce the number of JDBC calls over the network. The problem is we also use triggers and stored procedures that may update records that are currently cached in the Java app server. We currently use validation queries to check if a record is uptodate, which of course kills performance.

So, we're wondering if we can have Oracle notify us when a record has been inserted, udpated, or deleted. We don't need to know what changed, only which record changed and what the primary key was (although rowid would be ok too). Ideally, we want an invalidation message to be sent to our cache(s), for example:

    <table = "EMPLOYEES", pk = "10023", type = "delete">

It's also important that those invalidation events happen only for committed data. Otherwise, the cache could invalidate a record, then refresh it; but because the transaction hasn't been committed yet, it would still load the old data, causing cache inconsistency from then on.

Initially, we figured we could put triggers on all tables that call PL/SQL, which then sends out invalidation events. However, this would not work because triggers fire before the transaction commits, and we don't know when that is. Also, we don't want to use polling.

So, another idea is to read the redo log files to figure out what changed and send invalidation messages this way. This would perform much better than triggers, and would also be more correct, because we know when changes have been really committed. However, I can't find any documentation on the file format of redo log files. There are other companies that read redo log files, but it appears they all reverse engineered the format.

My questions are as follows:

(1) Is log sniffing a good idea to implement cache consistency with

    our app server?

(2) If that is the case, how difficult is it to figure out the format

    of redo log files (Oracle 8i and later)?

[I'm aware that there is an Oracle cache product, however this also has JDBC and RCP overhead for fetching rows from the database, even when they are cached.] Received on Fri Feb 21 2003 - 08:58:48 CST