Transcript Understanding Advanced Queuing

Advanced Queuing
Internals
Julian Dyke
Independent Consultant
Web Version - November 2008
1
© 2008 Julian Dyke
juliandyke.com
Agenda







2
© 2008 Julian Dyke
Introduction
Single Consumer Queues
Multiple Consumer Queues
 Recipients
 Subscribers
Exception Queues
Array Payloads
Buffered Messages
 Spilled Messages
Performance
juliandyke.com
Introduction
Advanced Queuing
3

Advanced Queuing
 Introduced in Oracle 8.0
 Extended and enhanced in most subsequent versions
 Supports Oracle Streams in Oracle 9.2 and above
 Supports buffered messages in Oracle 10.2 and above

Allows messages to be enqueued and dequeued from queues that are
managed by the database

Each queue is associated with a queue table
 Properties of queue table specify behaviour of associated queues

Each queue has a payload which can be:
 RAW - only messages of type RAW can be enqueued
 Object type- only messages of the specified type can be enqueued
 ANYDATA - messages with any object type can be enqueued
© 2008 Julian Dyke
juliandyke.com
Introduction
Advanced Queuing

By default messages are dequeued in the order they are enqueued
 Default behaviour can be overridden in several ways

Messages can be persistent or buffered
 Persistent messages
 Stored in queue table
 Survive an instance restart


4
Buffered messages
 Stored in SGA
 Can be spilled to queue table
 Lost during instance restart
Messages can be immediate or on-commit
 Immediate messages are committed immediately when they are
enqueued/dequeued
 On-commit messages are committed with the enqueuing transaction
 Buffered messages can only be immediate.
© 2008 Julian Dyke
juliandyke.com
Introduction
Advanced Queuing
5

Queue tables can be created for single or multiple consumers
 Messages in single consumer queue tables can only be dequeued once
 Messages in multiple consumer queue tables can be dequeued multiple
times by multiple consumers

Multiple consumer queue tables can be associated with
 Multiple recipients
 Multiple subcribers

Subscribers can:
 Specify rules to control which messages they dequene
 Specify transformations to be performed against dequeued data
© 2008 Julian Dyke
juliandyke.com
Advanced Queuing
Introduction
6

Sessions can listen for messages on multiple queues
 Session is notified when a message arrives on any of the target queues

Queue messages can be propagated from one queue to another
 In the same database
 In different databases
 Propagation can be immediate or at specified intervals

Transformation converts payload from one object type to another
 Queue data can be transformed when messages are:
 Enqueued
 Propagated
 Dequeued
 Messages must be transformed using DBMS_TRANSFORM API
© 2008 Julian Dyke
juliandyke.com
Introduction
Payloads

Queue payloads can be
 RAW
 Abstract data types
 ANYDATA

Abstract data types
 Maximum number of attributes is limited to 900
 For example:
CREATE TYPE type1 AS OBJECT
(
c1 NUMBER,
c2 NUMBER,
c3 NUMBER
);
/
7
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Queue Tables





Every queue must be associated with a queue table
Queue table defines properties of queue
Reported in DBA_QUEUE_TABLES
Managed using DBMS_AQADM
For example:
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','RAW');
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','TYPE1');


8
By default single consumer queues will be created
Each queue table can contain multiple queues
 Queues inherit properties of queue table
 Each queue table block contains blocks for one queue
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Queue Table Columns (RAW Payload)
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','RAW');

Queue table QT1 contains the following columns:
Col# Intcol# Column Name
9
Data Type
Col# Intcol# Column Name
Data Type
1
1 Q_NAME
VARCHAR2(30)
16
16 DEQ_TIME
TIMESTAMP(6)
2
2 MSG_ID
RAW(16)
17
17 DEQ_UID
VARCHAR2(30)
3
3 CORRID
VARCHAR2(128)
18
18 DEQ_TID
VARCHAR2(30)
4
4 PRIORITY
NUMBER
19
19 RETRY_COUNT
NUMBER
5
5 STATE
NUMBER
20
20 EXCEPTION_QSCHEMA VARCHAR2(30)
6
6 DELAY
TIMESTAMP(6)
21
21 EXCEPTION_QUEUE
VARCHAR2(30)
7
7 EXPIRATION
NUMBER
22
22 STEP_NO
NUMBER
8
8 TIME_MANAGER_INFO TIMESTAMP(6)
23
23 RECIPIENT_KEY
NUMBER
9
9 LOCAL_ORDER_NO
NUMBER
24
24 DEQUEUE_MSG_ID
RAW(16)
10
10 CHAIN_NO
NUMBER
25
25 SENDER_NAME
VARCHAR2(30)
11
11 CSCN
NUMBER
26
26 SENDER_ADDRESS
VARCHAR2(1024)
12
12 DSCN
NUMBER
27
27 SENDER_PROTOCOL
NUMBER
13
13 ENQ_TIME
TIMESTAMP(6)
28
28 USER_DATA
BLOB
14
14 ENQ_UID
VARCHAR2(30)
29
29 USER_PROP
SYS.ANYDATA
15
15 ENQ_TID
VARCHAR2(30)
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Queue Table Columns (Object Payload)
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','TYPE1');

Queue table QT1 contains the following columns:
Col# Intcol# Column Name
10
Data Type
Col# Intcol# Column Name
Data Type
1
1 Q_NAME
VARCHAR2(30)
17
17 DEQ_UID
VARCHAR2(30)
2
2 MSG_ID
RAW(16)
18
18 DEQ_TID
VARCHAR2(30)
3
3 CORRID
VARCHAR2(128)
19
19 RETRY_COUNT
NUMBER
4
4 PRIORITY
NUMBER
20
20 EXCEPTION_QSCHEMA VARCHAR2(30)
5
5 STATE
NUMBER
21
21 EXCEPTION_QUEUE
VARCHAR2(30)
6
6 DELAY
TIMESTAMP(6)
22
22 STEP_NO
NUMBER
7
7 EXPIRATION
NUMBER
23
23 RECIPIENT_KEY
NUMBER
8
8 TIME_MANAGER_INFO TIMESTAMP(6)
24
24 DEQUEUE_MSG_ID
RAW(16)
9
9 LOCAL_ORDER_NO
NUMBER
25
25 SENDER_NAME
VARCHAR2(30)
10
10 CHAIN_NO
NUMBER
26
26 SENDER_ADDRESS
VARCHAR2(1024)
11
11 CSCN
NUMBER
27
27 SENDER_PROTOCOL
NUMBER
12
12 DSCN
NUMBER
28
28 USER_DATA
TYPE1
13
13 ENQ_TIME
TIMESTAMP(6)
28
29 SYS_NC00029$
NUMBER
14
14 ENQ_UID
VARCHAR2(30)
28
30 SYS_NC00030$
NUMBER
15
15 ENQ_TID
VARCHAR2(30)
28
31 SYS_NC00031$
NUMBER
16
16 DEQ_TIME
TIMESTAMP(6)
29
32 USER_PROP
SYS.ANYDATA
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Database Objects (RAW payload)
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','RAW');


11
The following objects will be created (object IDs and constraint IDs will vary):
Object ID
Object Name
Object Type
70581
QT1
TABLE
70582
SYS_LOB0000070581C00028$$
LOB
70583
SYS_IL0000070581C00028$$
LOB INDEX
70584
SYS_LOB0000070581C00029$$
LOB
70585
SYS_IL0000070581C00029$$
LOB INDEX
70586
SYS_C009433
INDEX
70587
AQ$_QT1_T
INDEX
70588
AQ$_QT1_I
INDEX
70589
QT70581_BUFFER
VIEW
70590
AQ$QT1
VIEW
70591
AQ$_QT1_F
VIEW
70592
AQ$_QT1_E
QUEUE
LOB columns are used for USER_DATA and USER_PROP columns
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Database Objects (Object Payload)
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','TYPE1');


12
The following objects will be created (object IDs and constraint IDs will vary):
Object ID
Object Name
Object Type
70581
QT1
TABLE
70582
SYS_LOB0000070581C00032$$
LOB
70583
SYS_IL0000070581C00032$$
LOB INDEX
70584
SYS_C009433
INDEX
70585
AQ$_QT1_T
INDEX
70586
AQ$_QT1_I
INDEX
70587
QT70581_BUFFER
VIEW
70589
AQ$QT1
VIEW
70590
AQ$_QT1_F
VIEW
70591
AQ$_QT1_E
QUEUE
LOB column is used for USER_PROP column
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Index Columns
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT1','TYPE1');

The following indexes will be created by default (constraint IDs will vary):
Index Name
Column #
Column Name
SYS_C009436
1
MSGID
AQ$_QT1_I
1
Q_NAME
2
STATE
3
ENQ_TIME
4
STEP_NO
5
CHAIN_NO
6
LOCAL_ORDER_NO
1
TIME_MANAGER_INFO
AQ$_QT1_T
13
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Sort Lists




The columns indexed by AQ$_QTI are determined by the SORT_LIST
parameter.
Possible values are
 enq_time (default)
DBMS_AQADM.CREATE_QUEUE_TABLE
 priority
('QT1','TYPE1',SORT_LIST=>"priority,enq_time");
 priority,enq_time
 enq_time,priority
Must be defined when queue table is created
Cannot be subsequently altered
Column #
enq_time
priority
priority,enq_time
enq_time,priority
1
Q_NAME
Q_NAME
Q_NAME
Q_NAME
2
STATE
STATE
STATE
STATE
3
ENQ_TIME
PRIORITY
PRIORITY
ENQ_TIME
4
STEP_NO
CHAIN_NO
ENQ_TIME
STEP_NO
5
CHAIN_NO
LOCAL_ORDER_NO
STEP_NO
PRIORITY
6
LOCAL_ORDER_NO
CHAIN_NO
CHAIN_NO
LOCAL_ORDER_NO
LOCAL_ORDER_NO
7
14
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Views
15

Two views are created for each queue table

For example for QT1 (object ID =70581):

QT<object_id>_BUFFER
 e.g. QT70581_BUFFER
 based on X$BUFFER2

AQ$_<queue_table_name>_F
 e,g. AQ$_QT1_F
 based on QT1 and ALL_DEQUEUE_QUEUES
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Views

Two views are created for each queue table

For example for QT1 (object ID =70581)
 QT70581_BUFFER
 AQ$_QT1_F

QT70581_BUFFER is defined as follows:
SELECT
"ADDR", "INDX", "INST_ID", "OBJNO", "QUEUE_ID", "MSGID", "CORRID",
"SEQUENCE_NUM", "MSG_NUM", "STATE", "PRIORITY", "EXPIRATION",
"ENQ_TIME", "ENQ_UID", "ENQ_USER_NAME", "RETRY_COUNT",
"SENDER_NAME", "SENDER_ADDRESS", "SENDER_PROTOCOL",
"DEQUEUE_MSGID", "SRCSEQUENCE_NUM", "SUBSCRIBER_ID",
"EXCEPTIONQ_SCHEMA", "EXCEPTIONQ_NAME"
FROM X$BUFFER2
WHERE objno = 70581;
16
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Views

AQ$_QT1_F is defined as follows:
SELECT
qt.q_name Q_NAME, qt.rowid ROW_ID, qt.msgid MSGID, qt.corrid CORRID,
qt.priority PRIORITY, qt.state STATE, qt.delay DELAY, qt.expiration EXPIRATION,
qt.enq_time ENQ_TIME, qt.enq_uid ENQ_UID, qt.enq_tid ENQ_TID,
qt.deq_time DEQ_TIME, qt.deq_uid DEQ_UID, qt.deq_tid DEQ_TID,
qt.retry_count RETRY_COUNT, qt.exception_qschema EXCEPTION_QSCHEMA,
qt.exception_queue EXCEPTION_QUEUE, qt.cscn CSCN, qt.dscn DSCN,
qt.chain_no CHAIN_NO, qt.local_order_no LOCAL_ORDER_NO,
qt.time_manager_info TIME_MANAGER_INFO, qt.step_no STEP_NO,
qt.user_data USER_DATA , qt.sender_name SENDER_NAME,
qt.sender_address SENDER_ADDRESS, qt.sender_protocol SENDER_PROTOCOL,
qt.dequeue_msgid DEQUEUE_MSGID, 'PERSISTENT' DELIVERY_MODE,
0 SEQUENCE_NUM, 0 MSG_NUM, qo.qid QUEUE_ID,
qt.user_prop USER_PROP
FROM
"QT1" qt,
ALL_DEQUEUE_QUEUES qo
WHERE qt.q_name = qo.name
AND qo.owner = 'US01'
WITH READ ONLY;
17
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Queues
Every queue must be associated with a queue table
 Queue table must exist before queue can be created

DBMS_AQADM.CREATE_QUEUE
(queue_name => 'Q1',queue_table => 'QT1');

18
This statement might create the following object:
Object ID
Object Name
Object Type
70793
Q1
QUEUE

Every queue has a type which can be:
 NORMAL (default)
 EXCEPTION
 NON PERSISTENT

Non persistent queues are deprecated in Oracle 10.2
 Use buffered messages instead
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Enqueue

The following code enqueues a message of TYPE1 on a single consumer
queue:
DECLARE
message TYPE1;
msgprop dbms_aq.message_properties_t;
enqopt dbms_aq.enqueue_options_t;
enq_msgid RAW(16);
BEGIN
message := new TYPE1 (10001,20001,30001);
msgprop.expiration :=DBMS_AQ.NEVER
dbms_aq.enqueue
(
queue_name => 'Q1',
enqueue_options => enqopt,
message_properties => msgprop,
payload => message,
msgid => enq_msgid
);
END;
19
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Enqueue

The enqueue processes executes the following recursive statement:
insert into "US01"."QT1" (q_name, msgid, corrid, priority, state, delay, expiration,
time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_uid, enq_tid,
retry_count, exception_qschema, exception_queue, recipient_key, dequeue_msgid,
user_data, sender_name, sender_address, sender_protocol, user_prop, cscn, dscn)
values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, 0, :15,:16, :17, :18, :19, :20, :21,
:22, :23, :24, :25)

In Oracle 11.1 this statement uses the LOAD TABLE CONVENTIONAL
operation
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=5 pw=5
time=0 us)')

20
For this statement the following objects are modified
© 2008 Julian Dyke
Object ID
Object Name
Object Type
70581
QT1
TABLE
70586
SYS_C009433
INDEX
70588
AQ$_QT1_1
INDEX
juliandyke.com
Single Consumer Queues
Dequeue

The following code dequeues a message of TYPE1 from a single consumer
queue:
DECLARE
message TYPE1;
msgprop dbms_aq.message_properties_t;
deqopt dbms_aq.dequeue_options_t;
deq_msgid RAW(16);
BEGIN
dbms_aq.dequeue
(
queue_name => 'Q1',
dequeue_options => deqopt,
message_properties => msgprop,
payload => message,
msgid => deq_msgid
);
END;
21
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Dequeue

The dequeue processes executes the following recursive statement:
select /*+ FIRST_ROWS(1) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay,
tab.expiration, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no,
tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no,
tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid,
tab.user_prop, tab.user_data
from "US01"."QT1" tab
where q_name = :1 and (state = :2 )
order by q_name, state, enq_time, step_no, chain_no, local_order_no
for update skip locked




22
The statement selects all rows in the queue specified by :1 with a state of :2
The FIRST_ROWS(1) hint is used to optimize the plan
The statement locks any rows to be deleted
 This will generate undo/redo
The statement uses the FOR UPDATE SKIP LOCKED clause to skip any rows
still locked by ongoing transactions
© 2008 Julian Dyke
juliandyke.com
Single Consumer Queues
Dequeue

Execution plan for SELECT FOR UPDATE statement is:
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=7 pr=2 pw=2 time=0 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us
cost=4 size=2759 card=1)'
STAT #3 id=3 cnt=1 pid=2 pos=1 obj=70581 op='TABLE ACCESS FULL QT1 (cr=7 pr=0 pw=0
time=0 us cost=3 size=2759 card=1)'

As queue grows, object statistics must be gathered to
 ensure AQ$_QT1_I index is used
 prevent full table scans on QT1

Rows identified by SELECT FOR UPDATE are deleted using:
delete /*+ CACHE_CB("QT9") */ from "US01"."QT1" where rowid = :1

Execution plan for DELETE statement is:
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE QT1 (cr=1 pr=2 pw=2 time=0 us)'
STAT #7 id=2 cnt=1 pid=1 pos=1 obj=70581 op='TABLE ACCESS BY USER ROWID QT1
(cr=1 pr=0)'
23
© 2008 Julian Dyke
juliandyke.com
Queues
Exception Queues

A default exception queue is created for each queue table
 Exception messages will be moved to default queue unless a userdefined exception queue has been specified when the message is
enqueued

For example to create a user-defined exception queue
DBMS_AQADM.CREATE_QUEUE
(queue_name => 'Q1',queue_table => 'QT1');
DBMS_AQADM.CREATE_QUEUE
(queue_name => 'Q1E',queue_table => 'QT1'
queue_type => DBMS_AQADM.EXCEPTION_QUEUE);

Object ID
Object Name
Object Type
Queue Type
70793
Q1
QUEUE
NORMAL
70794
Q1E
QUEUE
EXCEPTION
To check number of rows in each queue:
SELECT q_name, COUNT(*)
FROM qt1
GROUP BY q_name;
24
© 2008 Julian Dyke
juliandyke.com
Queues
Exception Queues

Exceptions will be written to user-defined exception queue if it is specified
during enqueue operation
DECLARE
l_payload TYPE1;
l_msgprop dbms_aq.message_properties_t;
l_enqopt dbms_aq.enqueue_options_t;
l_enq_msgid RAW(16);
BEGIN
l_payload := new TYPE1 (10001,20001,30001);
l_msgprop.expiration := 60;
l_msgprop.exception_queue := 'Q1E';
dbms_aq.enqueue
(
queue_name => 'Q1',
enqueue_options => l_enqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_enq_msgid
);
END;

25
Message will expire after 60 seconds
 Expired message will be move to exception queue Q1E by queue monitor
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Introduction

There are two ways to use multiple consumer queues
 Multiple Recipients
 Multiple Subscribers

The same queue definitions are used for both examples:
BEGIN
dbms_aqadm.create_queue_table
('QT3','TYPE1',multiple_consumers=>TRUE)
dbms_aqadm.create_queue ('Q3','QT3');
dbms_aqadm.start_queue ('Q3');
END;
/
26
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Database Objects
DBMS_AQADM.CREATE_QUEUE_TABLE
('QT1','TYPE1',MULTIPLE_CONSUMERS=>TRUE);

27
The following objects will be created (object IDs and constraint IDs will vary):
Object ID
Object Name
Object Type
Object ID
Object Name
Object Type
70756
QT1
TABLE
70767
AQ$_QT1_H
TABLE
70757
SYS_LOB0000070581C00032$$
LOB
70768
SYS_IOT_TOP_70767
INDEX
70758
SYS_IL0000070581C00032$$
LOB INDEX
70769
AQ$_QT1_G
TABLE
70759
SYS_C009457
INDEX
70770
SYS_IOT_OVER_70769
TABLE
70760
AQ$_QT1_S
TABLE
70771
SYS_IOT_TOP_70769
INDEX
70761
SYS_C009460
INDEX
70772
AQ$_QT1_I
TABLE
70762
AQ$_QT1_N
SEQUENCE
70773
SYS_IOT_TOP_70772
INDEX
70763
AQ$QT1_S
VIEW
70774
QT70756_BUFFER
VIEW
70764
AQ$_QT1_V
EVAL CTXT
70775
AQ$QT1
VIEW
70765
AQ$_QT1_T
TABLE
70776
AQ$_QT1_F
VIEW
70766
SYS_IOT_TOP_70765
INDEX
70777
AQ$_QT1_E
QUEUE
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Tables
28

AQ$_<queue_table_name>_T e.g AQ$_QT3_T
 IOT used queue monitor to manage timed operations
 Single consumer queues use TIME_MANAGER_INFO column only

AQ$_<queue_table_name>_I
 IOT that maintains state for dequeue operations
 One row per message per recipient/subscriber

AQ$_<queue_table_name>_S
 Heap table containing information about subscribers

AQ$_<queue_table_name>_H
 IOT used to store dequeue history
 One row per message per recipient/subscriber

AQ$_<queue_table_name>_G
 IOT correlating messages to subscriber signatures
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Tables

29
AQ$_<queue_table_name>_T
 IOT used queue monitor to manage timed operations
 e.g. AQ$_QT3_T
Column Name
Data Type
NEXT_DATE
TIMESTAMP
TXN_ID
VARCHAR2(30)
MSGID
RAW(16)
ACTION
NUMBER

First 3 columns form primary key

Values for the ACTION column include:
 0 - delay
 1 - expiration
 2 - delay

Single consumer queues use TIME_MANAGER_INFO column only
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Tables



30
AQ$_<queue_table_name>_I
 IOT that maintains state for dequeue operations
Column Name
Data Type
SUBSCRIBER
NUMBER
NAME
VARCHAR2(30)
QUEUE#
NUMBER
MSG_ENQ_TIME
TIMESTAMP
MSG_STEP_NO
NUMBER
MSG_CHAIN_NO
NUMBER
MSG_LOCAL_ORDER_NO
NUMBER
MSG_ID
RAW(16)
HINT
ROWID
SPARE
RAW(16)
First eight columns form primary key
HINT and SPARE columns are stored in IOT overflow segment
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Tables

31
AQ$_<queue_table_name>_S
 Heap table containing information about subscribers
© 2008 Julian Dyke
Column Name
Data Type
SUBSCRIBER_ID
NUMBER
QUEUE_NAME
VARCHAR2(30)
NAME
VARCHAR2(30)
ADDRESS
VARCHAR2(1024)
PROTOCOL
NUMBER
SUBSCRIBER_TYPE
NUMBER
RULE_NAME
VARCHAR2(30)
TRANS_NAME
VARCHAR2(65)
RULESET_NAME
VARCHAR2(65)
NEGATIVE_RULESET_NAME
VARCHAR2(65)
CREATION_TIME
TIMESTAMP(6)
MODIFICATION_TIME
TIMESTAMP(6)
DELETION_TIME
TIMESTAMP(6)
SCN_AT_REMOVE
NUMBER
juliandyke.com
Multiple Consumer Queues
Tables



32
AQ$_<queue_table_name>_H
 IOT used to store dequeue history
Column Name
Data Type
MSGID
RAW(16)
SUBSCRIBER#
NUMBER
NAME
VARCHAR2(30)
ADDRESS#
NUMBER
DEQUEUE_TIME
TIMESTAMP
TRANSACTION_ID
VARCHAR2(30)
DEQUEUE_USER
VARCHAR2(30)
PROPAGATED_MSGID
RAW(16)
RETRY_COUNT
NUMBER
HINT
ROWID
SPARE
RAW(16)
First four columns form primary key
No IOT overflow segment
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Tables


33
AQ$_<queue_table_name>_G
 IOT correlating messages to subscriber signatures
Column Name
Data Type
NAME
VARCHAR2(30)
ADDRESS#
NUMBER
SIGN
SYS.AQ$_SIG_PROP
DBS_SIGN
SYS.AQ$_SIG_PROP
All columns form primary key
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Indexes



34
By default six indexes are created for each queue table. For example:
Index Name
Index Type
# Columns
Table Name
SYS_C009457
NORMAL
1
QT3
SYS_C009460
NORMAL
1
AQ$_QT3_S
SYS_IOT_TOP_70765
IOT
3
AQ$_QT3_T
SYS_IOT_TOP_70767
IOT
4
AQ$_QT3_H
SYS_IOT_TOP_70769
IOT
4
AQ$_QT3_G
SYS_IOT_TOP_70772
IOT
8
AQ$_QT3_I
Index columns for NORMAL indexes are:
Index Name
Column #
Column Name
SYS_C009457
1
MSGID
SYS_C009460
1
SUBSCRIBER_ID
Index columns for IOT indexes are shown on previous slides
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Views


35
Three views are created for each queue table
For example for QT3 (object ID = 70756)
 QT70756_BUFFER
 AQ$_QT3_F
 AQ$QT3

<queue_object_Id>_BUFFER e.g QT70756_BUFFER
 Similar for single and multiple consumers

AQ$_<queue_table_name>_F e.g AQ$_QT3_F
 Similar for single and multiple consumers

AQ$<queue_table_name> views e.g. AQ$QT3
 Based on:
 Queue table (QT3)
 History IOT (AQ$_QT3_H)
 Subscriber table (AQ$_QT3_S)
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Views

AQ$<queue_table_name> views (AQ$QT3) are based on:
 Queue table (QT3)
 History IOT (AQ$_QT3_H)
 Subscriber table (AQ$_QT3_S)

Abbreviated definition is as follows:
SELECT
<column_list>
FROM
"QT8" qt,
"AQ$_QT8_H" h,
"AQ$_QT8_S" s
WHERE qt.msgid = h.msgid
AND ((h.subscriber# != 0 AND h.subscriber# = s.subscriber_id)
OR (h.subscriber# = 0 AND h.address# = s.subscriber_id))
AND (qt.state != 7 OR qt.state != 9)
WITH READ ONLY;

36
Best view to understand current state of queue for all subscribers
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Views

AQ$QT3 contains the following columns
Column Name
Data Type
Column Name
Data Type
QUEUE
VARCHAR2(30)
RETRY_COUNT
NUMBER
MSG_ID
RAW(16)
EXCEPTION_QUEUE_OWNER
VARCHAR2(30)
CORR_ID
VARCHAR2(128)
EXCEPTION_QUEUE
VARCHAR2(30)
MSG_PRIORITY
NUMBER
USER_DATA
TYPE1
MSG_STATE
VARCHAR2(16)
PROPAGATED_MSGID
RAW(16)
DELAY
DATE
SENDER_NAME
VARCHAR2(30)
DELAY_TIMESTAMP
TIMESTAMP(6)
SENDER_ADDRESS
VARCHAR2(1024)
EXPIRATION
NUMBER
SENDER_PROTOCOL
NUMBER
ENQ_TIME
DATE
ORIGINAL_MSGID
RAW(16)
ENQ_TIMESTAMP
TIMESTAMP(6)
ORIGINAL_QUEUE_NAME
VARCHAR2(30)
ENQ_USER_ID
VARCHAR2(30)
ORIGINAL_QUEUE_OWNER
VARCHAR2(30)
ENQ_TXN_ID
VARCHAR2(30)
EXPIRATION_REASON
VARCHAR2(31)
DEQ_TIME
DATE
CONSUMER_NAME
VARCHAR2(30)
DEQ_TIMESTAMP
TIMESTAMP(6)
ADDRESS
VARCHAR2(1024)
DEQ_USER_ID
VARCHAR2(30)
PROTOCOL
NUMBER
DEQ_TXN_ID
VARCHAR2(30)
37
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Recipients

The following code enqueues a message for three named recipients
DECLARE
l_payload type1;
l_msgprop dbms_aq.message_properties_t;
l_enqopt dbms_aq.enqueue_options_t;
l_enq_msgid RAW(16);
l_recipient_list dbms_aq.aq$_recipient_list_t;
BEGIN
l_recipient_list(1) := sys.aq$_agent ('CONSUMER1',NULL,NULL);
l_recipient_list(2) := sys.aq$_agent ('CONSUMER2',NULL,NULL);
l_recipient_list(3) := sys.aq$_agent ('CONSUMER3',NULL,NULL);
l_msgprop.recipient_list := l_recipient_list;
l_msgprop.expiration := DBMS_AQ.NEVER;
l_payload := new TYPE1 (10001,20001,30001);
dbms_aq.enqueue
(
queue_name => 'Q3',
enqueue_options => l_enqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_enq_msgid
);
END;
38
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Recipients
39

A recipient list is constructed using AQ$_AGENT objects
 In the example all recipients are in the local database

The enqueue operation performs the following actions:
 Inserts one row in the queue table (QT3)
 Inserts three rows in the queue status table (AQ$_QT3_I)
 Inserts three rows in the queue history table (AQ$_QT3_H)
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Recipients

The following code dequeues a message for one of the named recipients
DECLARE
l_payload TYPE1;
l_msgprop dbms_aq.message_properties_t;
l_deqopt dbms_aq.dequeue_options_t;
l_deq_msgid RAW(16);
BEGIN
l_deqopt.consumer_name := 'CONSUMER2';
dbms_aq.dequeue
(
queue_name => 'Q3',
dequeue_options => l_deqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_deq_msgid
);
END;

40
Notes
 A consumer name MUST be specified
 The message must have been enqueued specifically for that consumer
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Recipients
41

The dequeue operation performs the following actions
 Deletes one row from the queue status IOT (AQ$_QT3_I)
 Updates the following columns in one row of the queue history IOT
(AQ$_QT3_H)
 DEQUEUE_TIME
 TRANSACTION_ID
 DEQUEUE_USER
 Inserts one row into the queue timer table (AQ$_QT3_T)

The queue monitor (QMNC) process asynchronously checks the timer table
(AQ$_QT3_T) for actions
 If any actions are found these are sent to the queue monitor slaves (Q001,
Q002 etc)

When last recipient has dequeued message, queue monitor slaves perform
the following actions
 Delete all rows for message in queue history table (AQ$_QT3_H)
 Delete row in queue table (QT3) for message
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Subscribers


Subscribers must exist for the queue before messages can be enqueued
The following code creates two subscribers for queue Q3
DECLARE
l_subscriber sys.aq$_agent;
BEGIN
l_subscriber := sys.aq$_agent ('SUBSCRIBER1',NULL,NULL);
DBMS_AQADM.ADD_SUBSCRIBER
(
queue_name => 'Q3',
subscriber => l_subscriber
);
l_subscriber := sys.aq$_agent ('SUBSCRIBER2',NULL,NULL);
DBMS_AQADM.ADD_SUBSCRIBER
(
queue_name => 'Q3',
subscriber => l_subscriber
);
END;

42
Creating a subscriber inserts one row in the AQ$_QT3_S table
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Subscribers

The following code enqueues ten messages on Q3
DECLARE
l_payload TYPE1;
l_msgprop dbms_aq.message_properties_t;
l_enqopt dbms_aq.enqueue_options_t;
l_enq_msgid RAW(16);
BEGIN
FOR f IN 1..10
LOOP
l_payload := new TYPE1 (10000 + f,20000 + f,30000 + f);
l_msgprop.expiration := DBMS_AQ.NEVER;
dbms_aq.enqueue
(
queue_name => 'Q3',
enqueue_options => l_enqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_enq_msgid
);
END LOOP;
END;
43
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Subscribers

The following code dequeues a message from Q3 for SUBSCRIBER1
SET SERVEROUTPUT ON
DECLARE
l_payload TYPE1;
l_msgprop dbms_aq.message_properties_t;
l_deqopt dbms_aq.dequeue_options_t;
l_deq_msgid RAW(16);
BEGIN
l_deqopt.consumer_name := 'SUBSCRIBER1';
dbms_aq.dequeue
(
queue_name => 'Q3',
dequeue_options => l_deqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_deq_msgid
);
DBMS_OUTPUT.PUT_LINE ('C1 = '||TO_CHAR (l_payload.c1));
DBMS_OUTPUT.PUT_LINE ('C2 = '||TO_CHAR (l_payload.c2));
DBMS_OUTPUT.PUT_LINE ('C3 = '||TO_CHAR (l_payload.c3));
END;
44
© 2008 Julian Dyke
juliandyke.com
Multiple Consumer Queues
Subscribers

Subscribers can subsequently be added and deleted dynamically
DECLARE
l_subscriber sys.aq$_agent;
BEGIN
l_subscriber := sys.aq$_agent ('SUBSCRIBER3',NULL,NULL);
DBMS_AQADM.ADD_SUBSCRIBER
(
queue_name => 'Q3',
subscriber => l_subscriber
);
DBMS_AQADM.REMOVE_SUBSCRIBER
(
queue_name => 'Q3',
subscriber => l_subscriber
);
END;

45
New subscribers will only be allowed to dequeue messages that have been
enqueued after the subscriber was added
© 2008 Julian Dyke
juliandyke.com
Array Payloads
Introduction


Payload of a queue can optionally be a VARRAY of object types
For example:
CREATE OR REPLACE TYPE type2 AS VARRAY (10) OF type1;
/
CREATE OR REPLACE TYPE type3 AS OBJECT (c1 type2);
/


Queue table can be created with a TYPE3 payload
 It is not possible to create a queue table with a TYPE2 payload
For example:
DBMS_AQADM.CREATE_QUEUE_TABLE ('QT3','TYPE3');
DBMS_AQADM.CREATE_QUEUE ('Q3','QT3');
DBMS_AQADM.START_QUEUE ('Q3');
46
© 2008 Julian Dyke
juliandyke.com
Array Payloads
Enqueue
DECLARE
l_payload TYPE3;
msgprop dbms_aq.message_properties_t;
enqopt dbms_aq.enqueue_options_t;
enq_msgid RAW(16);
BEGIN
l_payload := new TYPE3 (TYPE2 (
TYPE1 (10001,20001,30001),
TYPE1 (10002,20002,30002),
TYPE1 (10003,20003,30003),
TYPE1 (10004,20004,30004)
));
msgprop.expiration := DBMS_AQ.NEVER;
dbms_aq.enqueue
(
queue_name => 'Q4',
enqueue_options => enqopt,
message_properties => msgprop,
payload => l_payload,
msgid => enq_msgid
);
END;
47
© 2008 Julian Dyke
juliandyke.com
Array Payloads
Dequeue
SET SERVEROUTPUT ON
DECLARE
l_payload TYPE3;
msgprop dbms_aq.message_properties_t;
deqopt dbms_aq.dequeue_options_t;
deq_msgid RAW(16);
BEGIN
dbms_aq.dequeue
(
queue_name => 'Q4',
dequeue_options => deqopt,
message_properties => msgprop,
payload => l_payload,
msgid => deq_msgid
);
FOR i IN 1..message.c1.COUNT
LOOP
DBMS_OUTPUT.PUT ('C1 = '||TO_CHAR (l_payload.c1(i).c1)||' ');
DBMS_OUTPUT.PUT ('C2 = '||TO_CHAR (l_payload.c1(i).c2)||' ');
DBMS_OUTPUT.PUT ('C3 = '||TO_CHAR (l_payload.c1(i).c3));
DBMS_OUTPUT.NEW_LINE ();
END LOOP;
END;
48
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Introduction


49
In Oracle 10.2 and above messages can be buffered in the SGA
 Messages will not be written to database immediately
 Messages are spillled to database if:
 Number of messages exceeds threshold value
 Messages not dequeued within 10 minutes
Buffered messages
 Are much faster than persistent queues
 Do not guarantee reliability
 Cannot form part of a transaction
 Do not support (Oracle 11.1)
 Message retention / delay
 Transaction grouping
 Array enqueue / dequeue
 Message export / import
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Introduction

Buffering is specified at message level
 Queues can contain both persistent and buffered messages
 Payload can be ADT, XML, ANYDATA or RAW
 Support for LOB payloads is restricted

The following definitions are used with the examples in this section
BEGIN
dbms_aqadm.create_queue_table ('QT1','TYPE1')
dbms_aqadm.create_queue ('Q1','QT1');
dbms_aqadm.start_queue ('Q1');
END;

50
Note that all queue tables support buffered messages
 No additional attributes are specified for the queue or the queue table
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Enqueue

The following code enqueues a buffered message
DECLARE
l_payload TYPE1;
l_msgprop dbms_aq.message_properties_t;
l_enqopt dbms_aq.enqueue_options_t;
l_enq_msgid RAW(16);
BEGIN
l_payload := new TYPE1 (10001,20001,30001);
l_msgprop.expiration := DBMS_AQ.NEVER;
l_enqopt.visibility := DBMS_AQ.IMMEDIATE;
l_enqopt.delivery_mode := DBMS_AQ.BUFFERED;
dbms_aq.enqueue
(
queue_name => 'Q1',
enqueue_options => l_enqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_enq_msgid
);
END;
51
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Dequeue

The following code dequeues a buffered message:
SET SERVEROUTPUT ON
DECLARE
l_payload TYPE1;
l_msgprop dbms_aq.message_properties_t;
l_deqopt dbms_aq.dequeue_options_t;
l_deq_msgid RAW(16);
BEGIN
l_msgprop.expiration := DBMS_AQ.NEVER;
l_deqopt.visibility := DBMS_AQ.IMMEDIATE;
l_deqopt.delivery_mode := DBMS_AQ.BUFFERED;
dbms_aq.dequeue
(
queue_name => 'Q1',
dequeue_options => l_deqopt,
message_properties => l_msgprop,
payload => l_payload,
msgid => l_deq_msgid
);
DBMS_OUTPUT.PUT_LINE ('C1 = '||TO_CHAR (l_payload.c1));
DBMS_OUTPUT.PUT_LINE ('C2 = '||TO_CHAR (l_payload.c2));
DBMS_OUTPUT.PUT_LINE ('C3 = '||TO_CHAR (l_payload.c3));
END;
52
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Memory Usage


Memory is allocated from the Streams Pool
The following table shows the amount of streams pool memory required to
enqueue 5101 messages with the TYPE1 payload:
Before
After
Before
After
0
10,324,448
recov_kgqmsub
336
504
kwqbsinfy:mpr
480
2,448,480
kwqbsinfy:cco
332
332
image handles
84
428,512
kwqbsinfy:sta
208
312
kwqbsinfy:bms
72
387,692
spilled:kwqbl
216
288
kggmem_fl_1
44
224,444
fixed allocation callback
256
256
2,072
88,060
kgqmsub
144
216
Sender info
14,140
19,796
deqtree_kgqmctx
136
192
recov_kgqbtctx
12,288
16,384
substree_kgqmctx
120
160
kwqbcqini:spilledovermsgs
2,952
3,936
time manager index
120
160
kwqbsinfy:bqg
1,236
1,648
msgtree_kgqmctx
120
160
924
1,232
name_kgqmsub
32
48
kodpaih3
kggbt_alloc_block
recov_kggmctx
53
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Database Objects

Additional database objects are created the first time a buffered message is
enqueued on a queue table
 This will cause elapsed time of first enqueue operation to be high

For example the following objects might be created


54
Object ID
Object Name
Object Type
72638
AQ$_QT3_P
TABLE
72639
SYS_LOB0000072638C00032$$
LOB
72640
SYS_IL0000072638C00032$$
LOB INDEX
72641
SYS_C0010003
INDEX
The enqueuing session also creates a service for the queue
For example SYS$US01.Q3.TEST where
 US01 is the queue owner
 Q3 is the queue name
 TEST is the database name
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Database Objects

55
AQ$_<table_queue_name>_P contains the following columns
Column Name
Data Type
Column Name
Data Type
Q_NAME
VARCHAR2(30)
DEQ_TIME
DATE
MSGID
RAW(16)
DEQ_UID
VARCHAR2(30)
CORRID
VARCHAR2(128)
DEQ_TID
VARCHAR2(30)
PRIORITY
NUMBER
RETRY_COUNT
NUMBER
STATE
VARCHAR2(16)
EXCEPTION_QSCHEMA
VARCHAR2(30)
DELAY
DATE
EXCEPTION_QUEUE
VARCHAR2(30)
EXPIRATION
NUMBER
STEP_NO
NUMBER
TIME_MANAGER_INFO
TIMESTAMP(6)
RECIPIENT_KEY
NUMBER
LOCAL_ORDER_NO
NUMBER
DEQUEUE_MSGID
RAW(16)
CHAIN_NO
NUMBER
SENDER_NAME
VARCHAR2(30)
CSCN
NUMBER
SENDER_ADDRESS
VARCHAR2(1024)
DSCN
NUMBER
SENDER_PROTOCOL
NUMBER
ENQ_TIME
DATE
USER_DATA
TYPE1
ENQ_UID
VARCHAR2(30)
USER_PROP
SYS.ANYDATA
ENQ_TID
VARCHAR2(30)
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Database Objects
56

The AQ$_<queue_table_name>_P table has one primary key index on
 Q_NAME
 MSGID

Two view definitions are also updated when the first buffered message is
enqueued:
 AQ$<queue_table_name>
 e.g. AQ$QT3
 reports all messages in persistent and buffered queues
 AQ$_<queue_table_name>_F
 e.g. AQ$_QT3_F
 reports all messages that have not yet been dequeued in both
persistent and buffered queues
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Database Objects


The queue monitor slaves write spilled messages to
AQ$_<queue_table_name>_P
 Rows are inserted individually; no array operation is used
For example
INSERT INTO "us01"."aq$_qt3_p"
(
q_name, msgid, corrid, priority,state, delay, expiration, time_manager_info,
local_order_no, chain_no, enq_time, step_no, enq_uid, enq_tid, retry_count,
exception_qschema, exception_queue, recipient_key, dequeue_msgid,
user_data, sender_name, sender_address, sender_protocol, dscn, cscn
)
VALUES
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,0,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24)



Messages are asynchronously deleted from AQ$_<queue_table_name>_P by
queue monitor slaves
Messages are deleted using an array size of 32
For example
DELETE FROM us01.aq$_qt24_p WHERE q_name = :1 AND msgid = :2
57
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Database Objects


58
AQ$_<table_queue_name>_D contains the following columns
Column Name
Data Type
OID
NUMBER
MSGNUM
NUMBER
MSGID
RAW(16)
SUB
NUMBER
SEQNUM
NUMBER
RSUBS
SYS.AQ$_RECIPIENTS
The RSUBS column is stored as a LOB
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Spillage

59
If flow control is enabled then number of buffered messages that can be
enqueued on any queue is limited
 Subsequent attempts to enqueue messages will be rejected

Set _BUFQ_STOP_FLOW_CONTROL parameter to TRUE to disable flow
control completely

Limited to
 5000 buffered messages
 15000 captured messages

Can be overridden in 10.2.0.3 by applying Patch 5093060 and setting
 Event 10867 for buffered messages (level is # messages)
 Event 10868 for captured messages (level is # messages)

Can be fixed in 10.2.0.4 onwards by setting:
 _BUFFERED_PUBLISHER_FLOW_CONTROL_THRESHOLD
 _CAPTURED_PUBLISHER_FLOW_CONTROL_THRESHOLD
© 2008 Julian Dyke
juliandyke.com
Buffered Messages
Database Objects

60
For a multiple consumer queue the following objects will be created when the
first buffered message is enqueued:
Object ID
Object Name
Object Type
72638
AQ$_QT3_P
TABLE
72639
SYS_LOB0000072638C00032$$
LOB
72640
SYS_IL0000072638C00032$$
LOB INDEX
72641
SYS_C0010003
INDEX
72642
AQ$_QT3_D
TABLE
72643
SYS_IOT_OVER_72642
TABLE
72644
SYS_LOB0000072642C00006$$
LOB
72645
SYS_IL0000072642C00006$$
INDEX
72646
SYS_IOT_TOP_72642
INDEX
© 2008 Julian Dyke
juliandyke.com
Performance
Elapsed Times
Enqueue
No Commit
61
Dequeue
Commit
No Commit
Commit
Single Consumer
PERSISTENT
ON COMMIT
4.77
10.99
5.75
9.62
Single Consumer
PERSISTENT
IMMEDIATE
10.80
11.41
8.77
9.78
Single Consumer
BUFFERED
IMMEDIATE
2.32
2.60
1.53
2.13
Single Consumer
PERSISTENT
ON COMMIT
VARRAY(10) OF TYPE1
0.66
1.38
1.00
1.35
Multi Consumer
PERSISTENT
ON COMMIT
2 recipients
6.40
14.45
6.36
11.20
Multi Consumer
PERSISTENT
ON COMMIT
2 subscribers
6.02
14.59
6.54
11.40
10000 TYPE1 messages enqueued then 10000 messages dequeued. Average of 5 runs. Oracle 10.2 on RHEL4.5
x86
© 2008 Julian Dyke
juliandyke.com
Performance
Redo Generation
Enqueue
No Commit
Dequeue
Commit
No Commit
Commit
Single Consumer
PERSISTENT
ON COMMIT
9223
15491
10806
15521
Single Consumer
PERSISTENT
IMMEDIATE
15165
15485
14936
14904
Single Consumer
BUFFERED
IMMEDIATE
0
0
0
0
1211
1831
1381
1832
Multi Consumer
PERSISTENT
ON COMMIT
2 recipients
16459
23324
8102
12521
Multi Consumer
PERSISTENT
ON COMMIT
2 subscribers
15832
23404
7934
12953
Single Consumer
PERSISTENT
ON COMMIT
VARRAY(10) OF TYPE1
10 TYPE1 messages enqueued then 10 messages dequeued. Average of 5 runs. Oracle 10.2 on RHEL4.5 x86
62
© 2008 Julian Dyke
juliandyke.com
Conclusion
63

Several single queues may be more efficient than
 Multiple recipients
 Multiple subscribers

Use ON_COMMIT visibility where possible
 No transaction overhead for queuing operations
 Reduces undo / redo generation
 IMMEDIATE is much more expensive

Buffered messages give best performance
 Provided they do not spill regularly

Array payloads are very efficient
 Message overhead is reduced
© 2008 Julian Dyke
juliandyke.com
Thank you for your interest
[email protected]
64
© 2008 Julian Dyke
juliandyke.com