Dequeue Performance of Commit Time Queue Reduces with Large Message Volume
Last updated on MAY 04, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 11.1 to 11.2]
Information in this document applies to any platform.
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.
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>_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:
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms