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

Last updated on MAY 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

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:

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

Cause

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