Re: Sniffing redo logs to maintain cache consistency?

This is a joke - fire the person who designed this ...

"Andrej Gabara" <andrej_at_kintana.com> wrote in message news:11a3a163.0302210658.6c79d59_at_posting.google.com...
> 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 Sat Feb 22 2003 - 13:56:56 CST