Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Advanced Queue and Java Messaging System(JMS)

Re: Oracle Advanced Queue and Java Messaging System(JMS)

From: Martic Zoran <>
Date: Tue, 12 Apr 2005 03:42:02 -0700 (PDT)
Message-ID: <>


> Can you share some numbers ? What are the average
> enqueue and dequeue speeds,
> and on what hardware ?

Around 450 per CPU per process from the business logic. This is simple maybe not optimized result meaning the number may be higher if I needed to be.

So some little business logic to pack the messages is there, meaning not 100% accurate result. Also every group of messages is commited meanining commit is every 3 messages in average.

HW: Solaris 9, RAC, 450MHz CPU

> Also how many queues, producers and consumers do you
> have ?

4 queues. 2 are with 1 producer and 4 or more consumers, 2 are with 4 or more producers and 1 consumer. This was good enough for our customers because the bottleneck was in some other integrated systems :)

If you want to be faster you will go with bulk enq/deq (DML) in 10g.

> Does this mean that you're storing raw messages ?
No, the messages are type based. 5 columns in the message type.

Oracle AQ is very good as I said because it is simple table with the structure like this having 3 indexes:

Name                          Null?    Type
----------------------------- -------- ---------------
Q_NAME                                 VARCHAR2(30)
MSGID                         NOT NULL RAW(16)
CORRID                                 VARCHAR2(128)
PRIORITY                               NUMBER
STATE                                  NUMBER
DELAY                                  DATE
EXPIRATION                             NUMBER
TIME_MANAGER_INFO                      DATE
LOCAL_ORDER_NO                         NUMBER
CHAIN_NO                               NUMBER
CSCN                                   NUMBER
DSCN                                   NUMBER
ENQ_TIME                               DATE
ENQ_UID                                NUMBER
ENQ_TID                                VARCHAR2(30)
DEQ_TIME                               DATE
DEQ_UID                                NUMBER
DEQ_TID                                VARCHAR2(30)
RETRY_COUNT                            NUMBER
EXCEPTION_QSCHEMA                      VARCHAR2(30)
EXCEPTION_QUEUE                        VARCHAR2(30)
STEP_NO                                NUMBER
RECIPIENT_KEY                          NUMBER
DEQUEUE_MSGID                          RAW(16)
SENDER_NAME                            VARCHAR2(30)
SENDER_ADDRESS                         VARCHAR2(1024)
SENDER_PROTOCOL                        NUMBER
USER_DATA                              ZOX.SUBS_TYP

Where enq and deq (from the trace file) are using these SQL's:

  1. Enqueue

insert into ZOX.QUEUE1_TAB (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) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, 0, :15, :16, :17, :18, :19, :20, :21, :22)

2. dequeue

select q_name, state, delay, expiration, rowid, msgid,  dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corrid,
time_manager_info, sender_name, sender_address, sender_protocol from ZOX.QUEUE1_TAB where time_manager_info <= :1 and state != :2 order by enq_tid

select enq_tid from ZOX.QUEUE1_TAB qtable1 where

q_name = :1 and state = :2 and step_no =        
(select min(qtable2.step_no)          from  
ZOX.QUEUE1_TAB qtable2          where  qtable2.q_name
= qtable1.q_name and                 qtable2.state  =
qtable1.state  and                 qtable2.enq_tid =
qtable1.enq_tid) order by q_name, state, enq_tid,
enq_time, step_no,                          chain_no,
local_order_no for update skip locked

select user_data from ZOX.QUEUE1_TAB where rowid = :1 and (enq_tid = :2 or enq_tid is NULL) and step_no = :3

update ZOX.QUEUE1_TAB set state=:1,
time_manager_info=:2, deq_time=:3, deq_uid=:4, deq_tid=:5 where rowid = :6

How scalable you can imagine from this implementation details.

Because it is very simple it is great and very fast. With addition of bulk DML it should be much faster.


Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
Received on Tue Apr 12 2005 - 06:46:01 CDT

Original text of this message