OraFAQ Forum: Performance Tuning » Advanced Queuing correlation id

Home » RDBMS Server » Performance Tuning » Advanced Queuing correlation id (Oracle 10g Release 10.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Advanced Queuing correlation id [message #429187]

Mon, 02 November 2009 08:09

johnbach
Messages: 32
Registered: June 2009

Member

I have a single QUEUE TABLE.
Will there be any considerable difference in performance(dequeue time) between these two scenario?
scenario 1:
7 Queues each with 1 million entries
scenario 2:
Only one queue,but have 7 different correlation id,each with 1 million entries.

Report message to a moderator

Re: Advanced Queuing correlation id [message #429218 is a reply to message #429187]

Mon, 02 November 2009 10:22

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Only a benchmark and your treatments may answer this.
What is your idea to mix up all entries? Why do you want to do this?

Regards
Michel

Report message to a moderator

Re: Advanced Queuing correlation id [message #429279 is a reply to message #429218]

Tue, 03 November 2009 00:48

johnbach
Messages: 32
Registered: June 2009

Member

Tested both scenario
Scenario 1
Faster even at 80 dequeue per second (~ 1 milli second per dequeue)
ENQUEUE

     qname:='pin_q_'||:new.amount;
      DBMS_AQ.enqueue(queue_name=>qname,

DEQUEUE

qname:='pin_q_'||iamount;
   DBMS_AQ.dequeue(queue_name=>qname,

Scenario 2
Slow (~ 700 milli second for dequeue)
ENQUEUE

l_message_properties.correlation:=:new.amount;
      DBMS_AQ.enqueue(queue_name=>'pin_q',

DEQUEUE

l_dequeue_options.correlation:=iamount;
    l_dequeue_options.navigation:=dbms_aq.FIRST_MESSAGE;
    DBMS_AQ.dequeue(queue_name=>'pin_q',

Report message to a moderator

Previous Topic:	BITMAP INDEX performance issue
Next Topic:	undo usage in a query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 16:31:22 CST 2024