Re: Is it just me

Lisa Spory apparently said,on my timestamp of 12/08/2004 10:45 PM:
> Actually, Tom's example is data audit logging, his table structure defines who, table_name, column_name, and new and old values.

I got the impression it was only for a few tables. The original post/example dates from 2000. That's very early 9i days, mostly 8ir3 back then. Some of the later examples date to later versions, but I don't think that is what Tom had in mind when he wrote the original example. I'd say he was trying to mimic in 8i what could be done later on with other features.

> table: there is a bottleneck-waiting-to-happen, if I ever heard of 1...
>
> I see your point here, however, can't this be addressed via freelists and initrans settings?

I don't think freelists + initrans is gonna help much once the load increases. If it is not that much of an active system, then it will probably be OK.

Put it this way: average application touches around 4 tables/transaction. You're generating that many writes into this audit table X number of transactions/sec on the system. If it is a high throughput system, I can see a problem with a single table receiving as many writes as generated to all other tables...
Can't you split by type of transaction or business function?

The other thing could be to consider AQ like Tom proposes? Make all that logging happen asynchronously: build up a message with the before value, the after value, the user, timestamp, etcetc, then send it off (via trigger) to be processed when possible. Then you wouldn't have to worry as much about contention. Multiple queues, one per business function.

 > will have to grab the user ID from the application context  > (presumably set by the middle tier - another problem altogether).

Yup, and potentially a nasty one... Not all do that.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------