Re: Oracle Advanced Queue and Java Messaging System(JMS)
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: radu.popescu@xxxxxxxxxxxxx
- Date: Tue, 12 Apr 2005 03:42:02 -0700 (PDT)
Radu,
> 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 9.2.0.6, 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.
Regards,
Zoran
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
--
//www.freelists.org/webpage/oracle-l
Other related posts:
