Advanced Queuing Internals Julian Dyke Independent Consultant Web

Advanced Queuing Internals Julian Dyke Independent Consultant Web Version - November 2008 © 2008 Julian Dyke juliandyke. com

Agenda u u u u 2 © 2008 Julian Dyke Introduction Single Consumer Queues Multiple Consumer Queues u Recipients u Subscribers Exception Queues Array Payloads Buffered Messages u Spilled Messages Performance juliandyke. com

Introduction Advanced Queuing 3 u Advanced Queuing u Introduced in Oracle 8. 0 u Extended and enhanced in most subsequent versions u Supports Oracle Streams in Oracle 9. 2 and above u Supports buffered messages in Oracle 10. 2 and above u Allows messages to be enqueued and dequeued from queues that are managed by the database u Each queue is associated with a queue table u Properties of queue table specify behaviour of associated queues u Each queue has a payload which can be: u RAW - only messages of type RAW can be enqueued u Object type- only messages of the specified type can be enqueued u ANYDATA - messages with any object type can be enqueued © 2008 Julian Dyke juliandyke. com

Introduction Advanced Queuing u By default messages are dequeued in the order they are enqueued u Default behaviour can be overridden in several ways u Messages can be persistent or buffered u Persistent messages u Stored in queue table u Survive an instance restart u u 4 Buffered messages u Stored in SGA u Can be spilled to queue table u Lost during instance restart Messages can be immediate or on-commit u Immediate messages are committed immediately when they are enqueued/dequeued u On-commit messages are committed with the enqueuing transaction u Buffered messages can only be immediate. © 2008 Julian Dyke juliandyke. com

Introduction Advanced Queuing 5 u Queue tables can be created for single or multiple consumers u Messages in single consumer queue tables can only be dequeued once u Messages in multiple consumer queue tables can be dequeued multiple times by multiple consumers u Multiple consumer queue tables can be associated with u Multiple recipients u Multiple subcribers u Subscribers can: u Specify rules to control which messages they dequene u Specify transformations to be performed against dequeued data © 2008 Julian Dyke juliandyke. com

Advanced Queuing Introduction 6 u Sessions can listen for messages on multiple queues u Session is notified when a message arrives on any of the target queues u Queue messages can be propagated from one queue to another u In the same database u In different databases u Propagation can be immediate or at specified intervals u Transformation converts payload from one object type to another u Queue data can be transformed when messages are: u Enqueued u Propagated u Dequeued u Messages must be transformed using DBMS_TRANSFORM API © 2008 Julian Dyke juliandyke. com

Introduction Payloads u Queue payloads can be u RAW u Abstract data types u ANYDATA u Abstract data types u Maximum number of attributes is limited to 900 u For example: CREATE TYPE type 1 AS OBJECT ( c 1 NUMBER, c 2 NUMBER, c 3 NUMBER ); / 7 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Queue Tables u u u 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 ('QT 1', 'RAW'); DBMS_AQADM. CREATE_QUEUE_TABLE ('QT 1', 'TYPE 1'); u u 8 By default single consumer queues will be created Each queue table can contain multiple queues u Queues inherit properties of queue table u 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 ('QT 1', 'RAW'); u Queue table QT 1 contains the following columns: Col# Intcol# Column Name 9 Data Type Col# Intcol# Column Name Data Type 1 1 Q_NAME VARCHAR 2(30) 16 16 DEQ_TIMESTAMP(6) 2 2 MSG_ID RAW(16) 17 17 DEQ_UID VARCHAR 2(30) 3 3 CORRID VARCHAR 2(128) 18 18 DEQ_TID VARCHAR 2(30) 4 4 PRIORITY NUMBER 19 19 RETRY_COUNT NUMBER 5 5 STATE NUMBER 20 20 EXCEPTION_QSCHEMA VARCHAR 2(30) 6 6 DELAY TIMESTAMP(6) 21 21 EXCEPTION_QUEUE VARCHAR 2(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 VARCHAR 2(30) 11 11 CSCN NUMBER 26 26 SENDER_ADDRESS VARCHAR 2(1024) 12 12 DSCN NUMBER 27 27 SENDER_PROTOCOL NUMBER 13 13 ENQ_TIMESTAMP(6) 28 28 USER_DATA BLOB 14 14 ENQ_UID VARCHAR 2(30) 29 29 USER_PROP SYS. ANYDATA 15 15 ENQ_TID VARCHAR 2(30) © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Queue Table Columns (Object Payload) DBMS_AQADM. CREATE_QUEUE_TABLE ('QT 1', 'TYPE 1'); u Queue table QT 1 contains the following columns: Col# Intcol# Column Name 10 Data Type Col# Intcol# Column Name Data Type 1 1 Q_NAME VARCHAR 2(30) 17 17 DEQ_UID VARCHAR 2(30) 2 2 MSG_ID RAW(16) 18 18 DEQ_TID VARCHAR 2(30) 3 3 CORRID VARCHAR 2(128) 19 19 RETRY_COUNT NUMBER 4 4 PRIORITY NUMBER 20 20 EXCEPTION_QSCHEMA VARCHAR 2(30) 5 5 STATE NUMBER 21 21 EXCEPTION_QUEUE VARCHAR 2(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 VARCHAR 2(30) 10 10 CHAIN_NO NUMBER 26 26 SENDER_ADDRESS VARCHAR 2(1024) 11 11 CSCN NUMBER 27 27 SENDER_PROTOCOL NUMBER 12 12 DSCN NUMBER 28 28 USER_DATA TYPE 1 13 13 ENQ_TIMESTAMP(6) 28 29 SYS_NC 00029$ NUMBER 14 14 ENQ_UID VARCHAR 2(30) 28 30 SYS_NC 00030$ NUMBER 15 15 ENQ_TID VARCHAR 2(30) 28 31 SYS_NC 00031$ NUMBER 16 16 DEQ_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 ('QT 1', 'RAW'); u u 11 The following objects will be created (object IDs and constraint IDs will vary): Object ID Object Name Object Type 70581 QT 1 TABLE 70582 SYS_LOB 0000070581 C 00028$$ LOB 70583 SYS_IL 0000070581 C 00028$$ LOB INDEX 70584 SYS_LOB 0000070581 C 00029$$ LOB 70585 SYS_IL 0000070581 C 00029$$ LOB INDEX 70586 SYS_C 009433 INDEX 70587 AQ$_QT 1_T INDEX 70588 AQ$_QT 1_I INDEX 70589 QT 70581_BUFFER VIEW 70590 AQ$QT 1 VIEW 70591 AQ$_QT 1_F VIEW 70592 AQ$_QT 1_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 ('QT 1', 'TYPE 1'); u u 12 The following objects will be created (object IDs and constraint IDs will vary): Object ID Object Name Object Type 70581 QT 1 TABLE 70582 SYS_LOB 0000070581 C 00032$$ LOB 70583 SYS_IL 0000070581 C 00032$$ LOB INDEX 70584 SYS_C 009433 INDEX 70585 AQ$_QT 1_T INDEX 70586 AQ$_QT 1_I INDEX 70587 QT 70581_BUFFER VIEW 70589 AQ$QT 1 VIEW 70590 AQ$_QT 1_F VIEW 70591 AQ$_QT 1_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 ('QT 1', 'TYPE 1'); u The following indexes will be created by default (constraint IDs will vary): Index Name Column # Column Name SYS_C 009436 1 MSGID AQ$_QT 1_I 1 Q_NAME 2 STATE 3 ENQ_TIME 4 STEP_NO 5 CHAIN_NO 6 LOCAL_ORDER_NO 1 TIME_MANAGER_INFO AQ$_QT 1_T 13 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Sort Lists u u The columns indexed by AQ$_QTI are determined by the SORT_LIST parameter. Possible values are u enq_time (default) DBMS_AQADM. CREATE_QUEUE_TABLE u priority ('QT 1', 'TYPE 1', SORT_LIST=>"priority, enq_time"); u priority, enq_time u enq_time, priority Must be defined when queue table is created Cannot be subsequently altered Column # enq_time priority, enq_time, priority 1 Q_NAME 2 STATE 3 ENQ_TIME 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 LOCAL_ORDER_NO 7 14 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Views 15 u Two views are created for each queue table u For example for QT 1 (object ID =70581): u QT<object_id>_BUFFER u e. g. QT 70581_BUFFER u based on X$BUFFER 2 u AQ$_<queue_table_name>_F u e, g. AQ$_QT 1_F u based on QT 1 and ALL_DEQUEUE_QUEUES © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Views u Two views are created for each queue table u For example for QT 1 (object ID =70581) u QT 70581_BUFFER u AQ$_QT 1_F u QT 70581_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$BUFFER 2 WHERE objno = 70581; 16 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Views u AQ$_QT 1_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 "QT 1" qt, ALL_DEQUEUE_QUEUES qo WHERE qt. q_name = qo. name AND qo. owner = 'US 01' WITH READ ONLY; 17 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Every queue must be associated with a queue table u Queue table must exist before queue can be created u DBMS_AQADM. CREATE_QUEUE (queue_name => 'Q 1', queue_table => 'QT 1'); u 18 This statement might create the following object: Object ID Object Name Object Type 70793 Q 1 QUEUE u Every queue has a type which can be: u NORMAL (default) u EXCEPTION u NON PERSISTENT u Non persistent queues are deprecated in Oracle 10. 2 u Use buffered messages instead © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Enqueue u The following code enqueues a message of TYPE 1 on a single consumer queue: DECLARE message TYPE 1; msgprop dbms_aq. message_properties_t; enqopt dbms_aq. enqueue_options_t; enq_msgid RAW(16); BEGIN message : = new TYPE 1 (10001, 20001, 30001); msgprop. expiration : =DBMS_AQ. NEVER dbms_aq. enqueue ( queue_name => 'Q 1', enqueue_options => enqopt, message_properties => msgprop, payload => message, msgid => enq_msgid ); END; 19 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Enqueue u The enqueue processes executes the following recursive statement: insert into "US 01". "QT 1" (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) u 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)') u 20 For this statement the following objects are modified © 2008 Julian Dyke Object ID Object Name Object Type 70581 QT 1 TABLE 70586 SYS_C 009433 INDEX 70588 AQ$_QT 1_1 INDEX juliandyke. com

Single Consumer Queues Dequeue u The following code dequeues a message of TYPE 1 from a single consumer queue: DECLARE message TYPE 1; msgprop dbms_aq. message_properties_t; deqopt dbms_aq. dequeue_options_t; deq_msgid RAW(16); BEGIN dbms_aq. dequeue ( queue_name => 'Q 1', dequeue_options => deqopt, message_properties => msgprop, payload => message, msgid => deq_msgid ); END; 21 © 2008 Julian Dyke juliandyke. com

Single Consumer Queues Dequeue u 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 "US 01". "QT 1" 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 u u 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 u 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 u 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 QT 1 (cr=7 pr=0 pw=0 time=0 us cost=3 size=2759 card=1)' u As queue grows, object statistics must be gathered to u ensure AQ$_QT 1_I index is used u prevent full table scans on QT 1 u Rows identified by SELECT FOR UPDATE are deleted using: delete /*+ CACHE_CB("QT 9") */ from "US 01". "QT 1" where rowid = : 1 u Execution plan for DELETE statement is: STAT #7 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE QT 1 (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 QT 1 (cr=1 pr=0)' 23 © 2008 Julian Dyke juliandyke. com

Queues Exception Queues u A default exception queue is created for each queue table u Exception messages will be moved to default queue unless a user-defined exception queue has been specified when the message is enqueued u For example to create a user-defined exception queue DBMS_AQADM. CREATE_QUEUE (queue_name => 'Q 1', queue_table => 'QT 1'); DBMS_AQADM. CREATE_QUEUE (queue_name => 'Q 1 E', queue_table => 'QT 1' queue_type => DBMS_AQADM. EXCEPTION_QUEUE); u Object ID Object Name Object Type Queue Type 70793 Q 1 QUEUE NORMAL 70794 Q 1 E QUEUE EXCEPTION To check number of rows in each queue: SELECT q_name, COUNT(*) FROM qt 1 GROUP BY q_name; 24 © 2008 Julian Dyke juliandyke. com

Queues Exception Queues u Exceptions will be written to user-defined exception queue if it is specified during enqueue operation DECLARE l_payload TYPE 1; l_msgprop dbms_aq. message_properties_t; l_enqopt dbms_aq. enqueue_options_t; l_enq_msgid RAW(16); BEGIN l_payload : = new TYPE 1 (10001, 20001, 30001); l_msgprop. expiration : = 60; l_msgprop. exception_queue : = 'Q 1 E'; dbms_aq. enqueue ( queue_name => 'Q 1', enqueue_options => l_enqopt, message_properties => l_msgprop, payload => l_payload, msgid => l_enq_msgid ); END; u 25 Message will expire after 60 seconds u Expired message will be move to exception queue Q 1 E by queue monitor © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Introduction u There are two ways to use multiple consumer queues u Multiple Recipients u Multiple Subscribers u The same queue definitions are used for both examples: BEGIN dbms_aqadm. create_queue_table ('QT 3', 'TYPE 1', multiple_consumers=>TRUE) dbms_aqadm. create_queue ('Q 3', 'QT 3'); dbms_aqadm. start_queue ('Q 3'); END; / 26 © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Database Objects DBMS_AQADM. CREATE_QUEUE_TABLE ('QT 1', 'TYPE 1', MULTIPLE_CONSUMERS=>TRUE); u 27 The following objects will be created (object IDs and constraint IDs will vary): Object ID Object Name Object Type 70756 QT 1 TABLE 70767 AQ$_QT 1_H TABLE 70757 SYS_LOB 0000070581 C 00032$$ LOB 70768 SYS_IOT_TOP_70767 INDEX 70758 SYS_IL 0000070581 C 00032$$ LOB INDEX 70769 AQ$_QT 1_G TABLE 70759 SYS_C 009457 INDEX 70770 SYS_IOT_OVER_70769 TABLE 70760 AQ$_QT 1_S TABLE 70771 SYS_IOT_TOP_70769 INDEX 70761 SYS_C 009460 INDEX 70772 AQ$_QT 1_I TABLE 70762 AQ$_QT 1_N SEQUENCE 70773 SYS_IOT_TOP_70772 INDEX 70763 AQ$QT 1_S VIEW 70774 QT 70756_BUFFER VIEW 70764 AQ$_QT 1_V EVAL CTXT 70775 AQ$QT 1 VIEW 70765 AQ$_QT 1_T TABLE 70776 AQ$_QT 1_F VIEW 70766 SYS_IOT_TOP_70765 INDEX 70777 AQ$_QT 1_E QUEUE © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Tables 28 u AQ$_<queue_table_name>_T e. g AQ$_QT 3_T u IOT used queue monitor to manage timed operations u Single consumer queues use TIME_MANAGER_INFO column only u AQ$_<queue_table_name>_I u IOT that maintains state for dequeue operations u One row per message per recipient/subscriber u AQ$_<queue_table_name>_S u Heap table containing information about subscribers u AQ$_<queue_table_name>_H u IOT used to store dequeue history u One row per message per recipient/subscriber u AQ$_<queue_table_name>_G u IOT correlating messages to subscriber signatures © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Tables u 29 AQ$_<queue_table_name>_T u IOT used queue monitor to manage timed operations u e. g. AQ$_QT 3_T Column Name Data Type NEXT_DATE TIMESTAMP TXN_ID VARCHAR 2(30) MSGID RAW(16) ACTION NUMBER u First 3 columns form primary key u Values for the ACTION column include: u 0 - delay u 1 - expiration u 2 - delay u Single consumer queues use TIME_MANAGER_INFO column only © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Tables u u u 30 AQ$_<queue_table_name>_I u IOT that maintains state for dequeue operations Column Name Data Type SUBSCRIBER NUMBER NAME VARCHAR 2(30) QUEUE# NUMBER MSG_ENQ_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 u 31 AQ$_<queue_table_name>_S u Heap table containing information about subscribers © 2008 Julian Dyke Column Name Data Type SUBSCRIBER_ID NUMBER QUEUE_NAME VARCHAR 2(30) ADDRESS VARCHAR 2(1024) PROTOCOL NUMBER SUBSCRIBER_TYPE NUMBER RULE_NAME VARCHAR 2(30) TRANS_NAME VARCHAR 2(65) RULESET_NAME VARCHAR 2(65) NEGATIVE_RULESET_NAME VARCHAR 2(65) CREATION_TIMESTAMP(6) MODIFICATION_TIMESTAMP(6) DELETION_TIMESTAMP(6) SCN_AT_REMOVE NUMBER juliandyke. com

Multiple Consumer Queues Tables u u u 32 AQ$_<queue_table_name>_H u IOT used to store dequeue history Column Name Data Type MSGID RAW(16) SUBSCRIBER# NUMBER NAME VARCHAR 2(30) ADDRESS# NUMBER DEQUEUE_TIMESTAMP TRANSACTION_ID VARCHAR 2(30) DEQUEUE_USER VARCHAR 2(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 u u 33 AQ$_<queue_table_name>_G u IOT correlating messages to subscriber signatures Column Name Data Type NAME VARCHAR 2(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 u u u 34 By default six indexes are created for each queue table. For example: Index Name Index Type # Columns Table Name SYS_C 009457 NORMAL 1 QT 3 SYS_C 009460 NORMAL 1 AQ$_QT 3_S SYS_IOT_TOP_70765 IOT 3 AQ$_QT 3_T SYS_IOT_TOP_70767 IOT 4 AQ$_QT 3_H SYS_IOT_TOP_70769 IOT 4 AQ$_QT 3_G SYS_IOT_TOP_70772 IOT 8 AQ$_QT 3_I Index columns for NORMAL indexes are: Index Name Column # Column Name SYS_C 009457 1 MSGID SYS_C 009460 1 SUBSCRIBER_ID Index columns for IOT indexes are shown on previous slides © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Views u u 35 Three views are created for each queue table For example for QT 3 (object ID = 70756) u QT 70756_BUFFER u AQ$_QT 3_F u AQ$QT 3 u <queue_object_Id>_BUFFER e. g QT 70756_BUFFER u Similar for single and multiple consumers u AQ$_<queue_table_name>_F e. g AQ$_QT 3_F u Similar for single and multiple consumers u AQ$<queue_table_name> views e. g. AQ$QT 3 u Based on: u Queue table (QT 3) u History IOT (AQ$_QT 3_H) u Subscriber table (AQ$_QT 3_S) © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Views u AQ$<queue_table_name> views (AQ$QT 3) are based on: u Queue table (QT 3) u History IOT (AQ$_QT 3_H) u Subscriber table (AQ$_QT 3_S) u Abbreviated definition is as follows: SELECT <column_list> FROM "QT 8" qt, "AQ$_QT 8_H" h, "AQ$_QT 8_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; u 36 Best view to understand current state of queue for all subscribers © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Views u AQ$QT 3 contains the following columns Column Name Data Type QUEUE VARCHAR 2(30) RETRY_COUNT NUMBER MSG_ID RAW(16) EXCEPTION_QUEUE_OWNER VARCHAR 2(30) CORR_ID VARCHAR 2(128) EXCEPTION_QUEUE VARCHAR 2(30) MSG_PRIORITY NUMBER USER_DATA TYPE 1 MSG_STATE VARCHAR 2(16) PROPAGATED_MSGID RAW(16) DELAY DATE SENDER_NAME VARCHAR 2(30) DELAY_TIMESTAMP(6) SENDER_ADDRESS VARCHAR 2(1024) EXPIRATION NUMBER SENDER_PROTOCOL NUMBER ENQ_TIME DATE ORIGINAL_MSGID RAW(16) ENQ_TIMESTAMP(6) ORIGINAL_QUEUE_NAME VARCHAR 2(30) ENQ_USER_ID VARCHAR 2(30) ORIGINAL_QUEUE_OWNER VARCHAR 2(30) ENQ_TXN_ID VARCHAR 2(30) EXPIRATION_REASON VARCHAR 2(31) DEQ_TIME DATE CONSUMER_NAME VARCHAR 2(30) DEQ_TIMESTAMP(6) ADDRESS VARCHAR 2(1024) DEQ_USER_ID VARCHAR 2(30) PROTOCOL NUMBER DEQ_TXN_ID VARCHAR 2(30) 37 © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Recipients u The following code enqueues a message for three named recipients DECLARE l_payload type 1; 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 ('CONSUMER 1', NULL); l_recipient_list(2) : = sys. aq$_agent ('CONSUMER 2', NULL); l_recipient_list(3) : = sys. aq$_agent ('CONSUMER 3', NULL); l_msgprop. recipient_list : = l_recipient_list; l_msgprop. expiration : = DBMS_AQ. NEVER; l_payload : = new TYPE 1 (10001, 20001, 30001); dbms_aq. enqueue ( queue_name => 'Q 3', 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 u A recipient list is constructed using AQ$_AGENT objects u In the example all recipients are in the local database u The enqueue operation performs the following actions: u Inserts one row in the queue table (QT 3) u Inserts three rows in the queue status table (AQ$_QT 3_I) u Inserts three rows in the queue history table (AQ$_QT 3_H) © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Recipients u The following code dequeues a message for one of the named recipients DECLARE l_payload TYPE 1; l_msgprop dbms_aq. message_properties_t; l_deqopt dbms_aq. dequeue_options_t; l_deq_msgid RAW(16); BEGIN l_deqopt. consumer_name : = 'CONSUMER 2'; dbms_aq. dequeue ( queue_name => 'Q 3', dequeue_options => l_deqopt, message_properties => l_msgprop, payload => l_payload, msgid => l_deq_msgid ); END; u 40 Notes u A consumer name MUST be specified u The message must have been enqueued specifically for that consumer © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Recipients 41 u The dequeue operation performs the following actions u Deletes one row from the queue status IOT (AQ$_QT 3_I) u Updates the following columns in one row of the queue history IOT (AQ$_QT 3_H) u DEQUEUE_TIME u TRANSACTION_ID u DEQUEUE_USER u Inserts one row into the queue timer table (AQ$_QT 3_T) u The queue monitor (QMNC) process asynchronously checks the timer table (AQ$_QT 3_T) for actions u If any actions are found these are sent to the queue monitor slaves (Q 001, Q 002 etc) u When last recipient has dequeued message, queue monitor slaves perform the following actions u Delete all rows for message in queue history table (AQ$_QT 3_H) u Delete row in queue table (QT 3) for message © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Subscribers u u Subscribers must exist for the queue before messages can be enqueued The following code creates two subscribers for queue Q 3 DECLARE l_subscriber sys. aq$_agent; BEGIN l_subscriber : = sys. aq$_agent ('SUBSCRIBER 1', NULL); DBMS_AQADM. ADD_SUBSCRIBER ( queue_name => 'Q 3', subscriber => l_subscriber ); l_subscriber : = sys. aq$_agent ('SUBSCRIBER 2', NULL); DBMS_AQADM. ADD_SUBSCRIBER ( queue_name => 'Q 3', subscriber => l_subscriber ); END; u 42 Creating a subscriber inserts one row in the AQ$_QT 3_S table © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Subscribers u The following code enqueues ten messages on Q 3 DECLARE l_payload TYPE 1; 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 TYPE 1 (10000 + f, 20000 + f, 30000 + f); l_msgprop. expiration : = DBMS_AQ. NEVER; dbms_aq. enqueue ( queue_name => 'Q 3', 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 u The following code dequeues a message from Q 3 for SUBSCRIBER 1 SET SERVEROUTPUT ON DECLARE l_payload TYPE 1; l_msgprop dbms_aq. message_properties_t; l_deqopt dbms_aq. dequeue_options_t; l_deq_msgid RAW(16); BEGIN l_deqopt. consumer_name : = 'SUBSCRIBER 1'; dbms_aq. dequeue ( queue_name => 'Q 3', dequeue_options => l_deqopt, message_properties => l_msgprop, payload => l_payload, msgid => l_deq_msgid ); DBMS_OUTPUT. PUT_LINE ('C 1 = '||TO_CHAR (l_payload. c 1)); DBMS_OUTPUT. PUT_LINE ('C 2 = '||TO_CHAR (l_payload. c 2)); DBMS_OUTPUT. PUT_LINE ('C 3 = '||TO_CHAR (l_payload. c 3)); END; 44 © 2008 Julian Dyke juliandyke. com

Multiple Consumer Queues Subscribers u Subscribers can subsequently be added and deleted dynamically DECLARE l_subscriber sys. aq$_agent; BEGIN l_subscriber : = sys. aq$_agent ('SUBSCRIBER 3', NULL); DBMS_AQADM. ADD_SUBSCRIBER ( queue_name => 'Q 3', subscriber => l_subscriber ); DBMS_AQADM. REMOVE_SUBSCRIBER ( queue_name => 'Q 3', subscriber => l_subscriber ); END; u 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 u u Payload of a queue can optionally be a VARRAY of object types For example: CREATE OR REPLACE TYPE type 2 AS VARRAY (10) OF type 1; / CREATE OR REPLACE TYPE type 3 AS OBJECT (c 1 type 2); / u u Queue table can be created with a TYPE 3 payload u It is not possible to create a queue table with a TYPE 2 payload For example: DBMS_AQADM. CREATE_QUEUE_TABLE ('QT 3', 'TYPE 3'); DBMS_AQADM. CREATE_QUEUE ('Q 3', 'QT 3'); DBMS_AQADM. START_QUEUE ('Q 3'); 46 © 2008 Julian Dyke juliandyke. com

Array Payloads Enqueue DECLARE l_payload TYPE 3; msgprop dbms_aq. message_properties_t; enqopt dbms_aq. enqueue_options_t; enq_msgid RAW(16); BEGIN l_payload : = new TYPE 3 (TYPE 2 ( TYPE 1 (10001, 20001, 30001), TYPE 1 (10002, 20002, 30002), TYPE 1 (10003, 20003, 30003), TYPE 1 (10004, 20004, 30004) )); msgprop. expiration : = DBMS_AQ. NEVER; dbms_aq. enqueue ( queue_name => 'Q 4', 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 TYPE 3; msgprop dbms_aq. message_properties_t; deqopt dbms_aq. dequeue_options_t; deq_msgid RAW(16); BEGIN dbms_aq. dequeue ( queue_name => 'Q 4', dequeue_options => deqopt, message_properties => msgprop, payload => l_payload, msgid => deq_msgid ); FOR i IN 1. . message. c 1. COUNT LOOP DBMS_OUTPUT. PUT ('C 1 = '||TO_CHAR (l_payload. c 1(i). c 1)||' '); DBMS_OUTPUT. PUT ('C 2 = '||TO_CHAR (l_payload. c 1(i). c 2)||' '); DBMS_OUTPUT. PUT ('C 3 = '||TO_CHAR (l_payload. c 1(i). c 3)); DBMS_OUTPUT. NEW_LINE (); END LOOP; END; 48 © 2008 Julian Dyke juliandyke. com

Buffered Messages Introduction u u 49 In Oracle 10. 2 and above messages can be buffered in the SGA u Messages will not be written to database immediately u Messages are spillled to database if: u Number of messages exceeds threshold value u Messages not dequeued within 10 minutes Buffered messages u Are much faster than persistent queues u Do not guarantee reliability u Cannot form part of a transaction u Do not support (Oracle 11. 1) u Message retention / delay u Transaction grouping u Array enqueue / dequeue u Message export / import © 2008 Julian Dyke juliandyke. com

Buffered Messages Introduction u Buffering is specified at message level u Queues can contain both persistent and buffered messages u Payload can be ADT, XML, ANYDATA or RAW u Support for LOB payloads is restricted u The following definitions are used with the examples in this section BEGIN dbms_aqadm. create_queue_table ('QT 1', 'TYPE 1') dbms_aqadm. create_queue ('Q 1', 'QT 1'); dbms_aqadm. start_queue ('Q 1'); END; u 50 Note that all queue tables support buffered messages u No additional attributes are specified for the queue table © 2008 Julian Dyke juliandyke. com

Buffered Messages Enqueue u The following code enqueues a buffered message DECLARE l_payload TYPE 1; l_msgprop dbms_aq. message_properties_t; l_enqopt dbms_aq. enqueue_options_t; l_enq_msgid RAW(16); BEGIN l_payload : = new TYPE 1 (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 => 'Q 1', 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 u The following code dequeues a buffered message: SET SERVEROUTPUT ON DECLARE l_payload TYPE 1; 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 => 'Q 1', dequeue_options => l_deqopt, message_properties => l_msgprop, payload => l_payload, msgid => l_deq_msgid ); DBMS_OUTPUT. PUT_LINE ('C 1 = '||TO_CHAR (l_payload. c 1)); DBMS_OUTPUT. PUT_LINE ('C 2 = '||TO_CHAR (l_payload. c 2)); DBMS_OUTPUT. PUT_LINE ('C 3 = '||TO_CHAR (l_payload. c 3)); END; 52 © 2008 Julian Dyke juliandyke. com

Buffered Messages Memory Usage u u Memory is allocated from the Streams Pool The following table shows the amount of streams pool memory required to enqueue 5101 messages with the TYPE 1 payload: Before After 0 10, 324, 448 recov_kgqmsub 336 504 kwqbsinfy: mpr 480 2, 448, 480 kwqbsinfy: cco 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 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 kodpaih 3 kggbt_alloc_block recov_kggmctx 53 © 2008 Julian Dyke juliandyke. com

Buffered Messages Database Objects u Additional database objects are created the first time a buffered message is enqueued on a queue table u This will cause elapsed time of first enqueue operation to be high u For example the following objects might be created u u 54 Object ID Object Name Object Type 72638 AQ$_QT 3_P TABLE 72639 SYS_LOB 0000072638 C 00032$$ LOB 72640 SYS_IL 0000072638 C 00032$$ LOB INDEX 72641 SYS_C 0010003 INDEX The enqueuing session also creates a service for the queue For example SYS$US 01. Q 3. TEST where u US 01 is the queue owner u Q 3 is the queue name u TEST is the database name © 2008 Julian Dyke juliandyke. com

Buffered Messages Database Objects u 55 AQ$_<table_queue_name>_P contains the following columns Column Name Data Type Q_NAME VARCHAR 2(30) DEQ_TIME DATE MSGID RAW(16) DEQ_UID VARCHAR 2(30) CORRID VARCHAR 2(128) DEQ_TID VARCHAR 2(30) PRIORITY NUMBER RETRY_COUNT NUMBER STATE VARCHAR 2(16) EXCEPTION_QSCHEMA VARCHAR 2(30) DELAY DATE EXCEPTION_QUEUE VARCHAR 2(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 VARCHAR 2(30) CSCN NUMBER SENDER_ADDRESS VARCHAR 2(1024) DSCN NUMBER SENDER_PROTOCOL NUMBER ENQ_TIME DATE USER_DATA TYPE 1 ENQ_UID VARCHAR 2(30) USER_PROP SYS. ANYDATA ENQ_TID VARCHAR 2(30) © 2008 Julian Dyke juliandyke. com

Buffered Messages Database Objects 56 u The AQ$_<queue_table_name>_P table has one primary key index on u Q_NAME u MSGID u Two view definitions are also updated when the first buffered message is enqueued: u AQ$<queue_table_name> u e. g. AQ$QT 3 u reports all messages in persistent and buffered queues u AQ$_<queue_table_name>_F u e. g. AQ$_QT 3_F u reports all messages that have not yet been dequeued in both persistent and buffered queues © 2008 Julian Dyke juliandyke. com

Buffered Messages Database Objects u u The queue monitor slaves write spilled messages to AQ$_<queue_table_name>_P u Rows are inserted individually; no array operation is used For example INSERT INTO "us 01". "aq$_qt 3_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) u u u 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 us 01. aq$_qt 24_p WHERE q_name = : 1 AND msgid = : 2 57 © 2008 Julian Dyke juliandyke. com

Buffered Messages Database Objects u u 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 u 59 If flow control is enabled then number of buffered messages that can be enqueued on any queue is limited u Subsequent attempts to enqueue messages will be rejected u Set _BUFQ_STOP_FLOW_CONTROL parameter to TRUE to disable flow control completely u Limited to u 5000 buffered messages u 15000 captured messages u Can be overridden in 10. 2. 0. 3 by applying Patch 5093060 and setting u Event 10867 for buffered messages (level is # messages) u Event 10868 for captured messages (level is # messages) u Can be fixed in 10. 2. 0. 4 onwards by setting: u _BUFFERED_PUBLISHER_FLOW_CONTROL_THRESHOLD u _CAPTURED_PUBLISHER_FLOW_CONTROL_THRESHOLD © 2008 Julian Dyke juliandyke. com

Buffered Messages Database Objects u 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$_QT 3_P TABLE 72639 SYS_LOB 0000072638 C 00032$$ LOB 72640 SYS_IL 0000072638 C 00032$$ LOB INDEX 72641 SYS_C 0010003 INDEX 72642 AQ$_QT 3_D TABLE 72643 SYS_IOT_OVER_72642 TABLE 72644 SYS_LOB 0000072642 C 00006$$ LOB 72645 SYS_IL 0000072642 C 00006$$ INDEX 72646 SYS_IOT_TOP_72642 INDEX © 2008 Julian Dyke juliandyke. com

Performance Elapsed Times Enqueue No Commit 61 Dequeue Commit No 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 TYPE 1 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 TYPE 1 messages enqueued then 10000 messages dequeued. Average of 5 runs. Oracle 10. 2 on RHEL 4. 5 x 86 © 2008 Julian Dyke juliandyke. com

Performance Redo Generation Enqueue No Commit Dequeue Commit No Commit Single Consumer PERSISTENT ON COMMIT 9223 15491 10806 15521 Single Consumer PERSISTENT IMMEDIATE 15165 15485 14936 14904 Single Consumer BUFFERED IMMEDIATE 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 TYPE 1 10 TYPE 1 messages enqueued then 10 messages dequeued. Average of 5 runs. Oracle 10. 2 on RHEL 4. 5 x 86 62 © 2008 Julian Dyke juliandyke. com

Conclusion 63 u Several single queues may be more efficient than u Multiple recipients u Multiple subscribers u Use ON_COMMIT visibility where possible u No transaction overhead for queuing operations u Reduces undo / redo generation u IMMEDIATE is much more expensive u Buffered messages give best performance u Provided they do not spill regularly u Array payloads are very efficient u Message overhead is reduced © 2008 Julian Dyke juliandyke. com

Thank you for your interest info@juliandyke. com 64 © 2008 Julian Dyke juliandyke. com