Commit time of AQ enqueue
- From: "Buechi Martin" <Martin.Buechi@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Nov 2005 21:20:33 +0100
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@xxxxxxxxxx>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Commit time of AQ enqueue
