Database Reference
In-Depth Information
A closer look at how
DBMS_AQ.DEQUEUE
is implemented reveals that it is possible to wait one
or more seconds for a message to arrive. Database sessions that request a message do not keep
a CPU busy while they are waiting. Instead, the session is put to sleep on the wait event
Streams
AQ: waiting for messages in the queue
(in Oracle10
g
). The first parameter of this wait event
(
V$SESSION_EVENT.P1
) is the object identifier of the queue in the data dictionary (
DBA_OBJECTS.
OBJECT_ID
).
V$SESSION_EVENT.P3
holds the time (in seconds) which was used in the call to
DBMS_AQ.DEQUEUE
.
SQL> SELECT p1, p1text, p2, p2text, p3, p3text
FROM v$session_wait
WHERE event='Streams AQ: waiting for messages in the queue';
P1 P1TEXT P2 P2TEXT P3 P3TEXT
----- -------- ---------- -------- --- ---------
16580 queue id 1780813772 process# 120 wait time
SQL> SELECT object_name, object_type FROM dba_objects WHERE object_id=16580;
OBJECT_NAME OBJECT_TYPE
---------------------- -----------
CAUGHT_IN_SLOW_Q_AGAIN QUEUE
By the way, if you are merely trying to protect a shared resource from being used simulta-
neously by different processes, consider using
DBMS_LOCK
instead of implementing a sort of
mutual exclusion or semaphore mechanism with a table. I'm addressing this point because I
have seen postings on the Internet that suggest using
SELECT
FOR
UPDATE
SKIP
LOCKED
to imple-
ment a resource control table, i.e., a dedicated table that has one row per resource and a status
column. The value of the status column would indicate whether the resource was available or
not. Obviously, frequent concurrent accesses to such a table will incur waits for TX locks,
unless the undocumented
SKIP
LOCKED
clause is used.
DBMS_LOCK—A Digression
DBMS_LOCK
allows you to request and release locks in shared as well as exclusive mode. What's
more, locks can even be converted between modes. With regards to redo generation, calling
DBMS_LOCK
is also preferable over implementing a locking mechanism based on a table, which
needs to be updated in order to reflect the status of locks. The use of
DBMS_LOCK.REQUEST
/
RELEASE
to
obtain and release locks does not generate any redo.
DBMS_LOCK
is fully documented in the
PL/
SQL Packages and Types Reference
, but a small example for its use is in order.
Lock names or numbers must be agreed upon by all components of an application. To
make sure different applications do not interfere witch each other by accidentally using the
same lock number,
DBMS_LOCK
provides a way to convert a name for a lock to a lock handle,
which then replaces the rather meaningless lock number. All sessions wishing to use the same
lock (e.g.,
MYAPP_MUTEX1
in the example below) must call
DBMS_LOCK.ALLOCATE_UNIQUE
once, to
convert the lock name to a lock handle.
SQL> VARIABLE lockhandle VARCHAR2(128)
SQL> BEGIN
-- get a lock handle for the lockname that was agreed upon
-- make sure you choose a unique name, such that other vendors' applications
-- won't accidentally interfere with your locks
