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 18.104.22.168 to 22.214.171.124 [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 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:
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