Commit time of AQ enqueue

From: Buechi Martin <Martin.Buechi_at_avaloq.com>
Date: Fri, 25 Nov 2005 21:20:33 +0100
Message-Id: <20051125202056.3586D60652@gw1.avaloq.com>

All,

We have an application under Oracle 9.2.0.5 on AIX that uses Oracle's Advanced Queueing (AQ). An external application enqueues messages, which are handled by our application. When the external application calls dbms_aq.enqueue, the enq_time is set to sysdate. In some cases, the external application commits several seconds or even minutes later. We would like to know how long it takes between the commit - making the message visible to our application - and the dequeue (start of handlng) of the message. deq_time - enq_time doesn't provide the answer because enq_time is the enqueue rather than the commit time of the enqueueing transaction.

Any suggestions of how this could be done? I thought about a deferred constraint, but that is not allowed to call user-defined (PL/SQL) functions. A materialized view with on commit refresh and a trigger doesn't work either, because the on commit should really be called "immediate". I could add a foreign key constraint to a table with RLS. This would give me the access time, but not the message ID. Maybe auditing? Or going through the transaction ID/SCN? Since the system is productive, I cannot change the code of either application.

For those not familiar with AQ, here's the problem restated with plain tables:

Customer session:
insert into q(enq_time, msg_id, content) values(sysdate, .., ..)
-- do something that takes several seconds
commit;

My session:
select * from q;

I would like to know when the row with ID msg_id was committed and became visible to my session.

Regards,

Martin

A v a l o q - essential for banking
Avaloq Evolution AG
Allmendstrasse 140, 8027 Zürich
T +41 44 488 6888, F +41 44 488 6868, <http://www.avaloq.com/> Martin Büchi <mailto:martin.buechi_at_avaloq.com>

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 25 2005 - 14:23:03 CST