Re: Sniffing redo logs to maintain cache consistency?

Following up on Andrej Gabara, 22 Feb 2003:

Crap! This is PRECISELY why it's a bad idea to have Java people in charge of design!!!
Sorry if I sound blunt, but...

> We have a Java application server that caches database records
> to reduce the number of JDBC calls over the network. The problem

Very, very bad idea to design your application this way when you're using a database. Fine if all you're doing is use flat files.

With a database, let it cache data itself: it's many, many times faster than ANYTHING you may EVER write in Java.

> is we also use triggers and stored procedures that may update
> records that are currently cached in the Java app server. We

Another bad idea. You do NOT want to use triggers when you're using a Java cache the way you are.

> currently use validation queries to check if a record is uptodate,
> which of course kills performance.

and it will no matter what database you put there and whatever else you might do. Fix the design.

> 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.

It's amazing how Java people will gladly squander money and time reinventing the wheel. All this sort of stuff has been worked out AGES ago in databases. Use the available features instead of reinventing boiled water.

>
> So, another idea is to read the redo log files to figure out
> what changed and send invalidation messages this way. This would

And you think this would give you a speed advantage? I mean, you're re-creating a redundant cache algorithm and now you want to make it dependent on disk access to redo logs? Way to go!...

> My questions are as follows:
>
> (1) Is log sniffing a good idea to implement cache consistency with
> our app server?

No. Database cache in your app server is a VERY BAD idea. That's what you got to fix.

>
> (2) If that is the case, how difficult is it to figure out the format
> of redo log files (Oracle 8i and later)?

Just about impossible unless you have access to Oracle internals. And it would most likely change with the next release.

> [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.]
>

Again, the completely wrong idea and concept of what a database cache is for.

If you want to reduce JDBC overhead, what you have to use is array interface, object parameters and make design changes to your application code and how it uses the database. You're fixing a problem that was never there and in turn causing a huge one to bite you back.

The cache on the Oracle app server is shared with the database cache and has a much more efficient access mechanism than RCP (?!) and JDBC. You'd be much better off using that than trying to re-invent it...

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam