This discussion is archived
2 Replies Latest reply: Feb 14, 2013 4:30 AM by user10302663 RSS

Multiple Notifications enqueued on single queue not dequeued concurrently

user10302663 Newbie
Currently Being Moderated
Hi,

Oracle 10G database AQ behavior (10.2.0.4.0)

1) Enqueued 5 notifications into Oracle AQ

2) 5 Jobs are being submitted in dba_jobs in different Sessions

3) All 5 notifications are dequeued by Callback procedure in Parallel sessions


Oracle 11G database AQ behavior (11.2.0.3.0)

1) Enqueued 5 notifications into Oracle AQ

2) 1 Job is being submitted in dba_scheduler_jobs

3) 5 notifications are being dequeued by Callback procedure in single session

Is there a way to Dequeue messages concurrently in parallel sessions?

Thanks
Sridhar
  • 1. Re: Multiple Notifications enqueued on single queue not dequeued concurrently
    phcullen-Oracle Explorer
    Currently Being Moderated
    Hello,

    It is more expensive to start a scheduler job as compared to a dbms_job and so in 11.1 onwards we only start a scheduler job when we need to. As the load increases so should the number of scheduler jobs but you will never see as many running
    as you would have in 10.2.

    Is the throughput not matching your expectations or is this just an observation?

    Thanks
    Peter
  • 2. Re: Multiple Notifications enqueued on single queue not dequeued concurrently
    user10302663 Newbie
    Currently Being Moderated
    Hi Peter,

    Throughput has been decreased after moving to 11G.

    Background of code:

    Call back procedure on AQ we have consists of two parts
    1) Dequeue of first message available
    2) Calling an another procedure with information from Notification. This procedure processed all records satisfying the information from Notification.
    It takes around 6 minutes to process

    We have parameter p_var which is configured as 6 and this controls number of dbms_jobs fired as below

    LOOP
    FOR i in 1..p_var
    LOOP
    dbms_lock.allocate_unique ('QUEUE1'||i,l_lckhandle_db);
    l_rel := dbms_lock.request (l_lckhandle_db, 6, 1, FALSE);
    EXIT WHEN l_rel = 0;
    END LOOP;
    IF l_rel <> 0
    THEN
    dbms_lock.sleep (10);
    END IF;
    EXIT WHEN l_rel = 0;
    END LOOP;

    Suppose we have 10 Notifications enqueued in same second in AQ

    10G code flow:

    1) 6 jobs were fired and processing was in 6 parallel sessions

    11G code flow:

    1) only one scheduler job is fired and all 10 notifications are processed in serial.

    This is reason for less throughput in 11G.

    Do we have any way we can make this process in parallel? (not enqueing notifications in same second and changing any DB level parameter?)

    I have an idea to fire a separate dbms_job or scheduler job to process part 2 of callback procedure.
    Is this solution we can consider to problem we have or is there any other solution?

    Thanks
    Sridhar

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points