Dequeue Performance of Commit Time Queue Reduces with Large Message Volume

Last updated on MAY 04, 2017

Applies to:

Symptoms

AQ commit time ordered queue.

There is a large number of messages in the queue and statistics have been generated on the schema where the queue resides. This has had the affect of changing the rate at which messages can be dequeued from the queue.

The dequeue related sql statement which is now accessing more blocks that it did is of the form:

select enq_tid from "<schema>"."AQ$_<queue table>_C" ctiot where enq_tid in (select msg_enq_tid from "<schema>"."AQ$_<queue_table>_I" qidx1 where qidx1.subscriber# = :1 and qidx1.name = :2 and qidx1.queue# = :3) and ctiot.cscn >= :4 a nd ctiot.cscn < :5 order by cscn, enq_tid

This statement may now also be visible in the AWR report under 'buffer gets' activity.

Changes

Statistics have been collected on queue table related objects .

The queue table has its statistics locked at the time it is created. However, other related objects necessary to support the operation of the queue and its operation do not have their stats locked at the time the queue is created.

aq$_<queue_table>_g
aq$_<queue_table>_t
aq$_<queue_table>_h
aq$_<queue_table>_i
aq$_<queue_table>_s
aq$_<queue_table>_c - Commit time IOT

Other objects which may be present are:

aq$_<queue_table>_d - buffered queue related ; spill IOT
aq$_<queue_table>_l  - Dequeue log, introduced in 11.2

NOTE: Please determine relevant related object from the following:

Cause