RE: Is it just me
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Aug 2004 09:46:16 -0400
cool idea, but if the possibility of an insert bottleneck is the main topic
at this point, if initrans (or ASSM) fails to keep the audit log from being
a pacing object, I'm thinking either real partitioning or poor man's
partitioning would solve this. Probably who and table_name are decent
candidates for the partitioning (and of course you still gotta keep some
sort of date thingy or rotate/grandfather synonyms so you don't die death by
monolith.) If one user or table dominates the change stream so much that it
alone is a problem, you'd special case that bad boy so its audit write
bottleneck is no worse than the table or user itself.
Still your idea is intriguing in a cache capacity sort of way. Hmm, does
this imply that the flashback area itself will be the bottleneck? Not on the
retrieval part that you pointed out you could do asynchronously, but the
original write. I'm not sure, and flashback, although I think I get it
theoretically, still seems a bit like magic or perpetual motion to me. I
guess it is actually no worse than rollback segments or any implementation
of undo in transactional requirements, just more functional. Okay, I guess
it's not magic, excuse the stream of consciousness. Why the heck haven't we
had flashback all along since v6?
Now, for auditing, you would have to do the equivalent of the redo log
freeze on new transactions if your queues got far enough behind that you
were going to lose required old values from flashback you still needed for
queued auditing. I need more coffee before I try to think about how you
design the queue so you know at small cost whether certain flashback can be
released. Ugh. Does that have the same issues as snapshot too old?
It's still a cool idea, and my children's college bursars would thrill at
the idea of me participating in a funded project to implement it.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Carel-Jan Engel
Sent: Thursday, August 12, 2004 9:20 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Is it just me
Hi Lisa,
Although you said you can't update to 10g yet, I just got a brainfart
that _might_ be helpful in a 10g environment and can be adopted to 9i as
well. I have not tested this, so I cannot go into detail about all
consequences, but by chance I will elaborate on the idea some time in
the future. Of course, I encourage anyone to do this testing and share
the results.
When enabling flashback, you are able to peek into old and new values of
a column in the (near) past using flashback queries. The trigger for the
logging can just push a message in a queue, containg the user involved,
table, rowid and timestamp or SCN. The job handling the queue will
retrieve old and new values using flashback, and write the proper audit
trail info into the logtable. Of course one of the assumptions is that
the serialization isn't moved from the insert in the logtable to the
adding of the message to the queue. Furthermore, the savings on the
inserts in the logtable have to outweigh the cost of the extra I/O
involved with flashback, and the processing of the queue. The whole idea
is just making the logging asynchronous, without risking the loss of
information. AQ can be of great help doing that. I think that adding the
extra info of columns and old/new values to the message might make this
working for 9i as well. Of course I would only go this way when the
logging becomes a bottleneck. When the system works fine, don't change
it. I wouldn't encourage you to introduce more complexity, although I'm
a director of a (small) Oracle consultancy company as well ;-).
Just my $0.02.
Best regards,
Carel-Jan Engel
===
If you think education is expensive, try ignorance. (Derek Bok)
On Thu, 2004-08-12 at 14:45, Lisa Spory wrote:
> Actually, Tom's example is data audit logging, his table structure defines
who, table_name, column_name, and new and old values.
>
> > really don't like the idea of *extensive* logging into a single
> 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?
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
