My Oracle Support Banner

Dequeue Performance of Commit Time Queue Reduces with Large Message Volume (Doc ID 1363145.1)

Last updated on MARCH 11, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
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 = :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:

select object_name, object_type from user_objects where object_name like '%<QUEUE_TABLE>%' and object_type='TABLE';


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.