2 Replies Latest reply: Feb 14, 2013 6:30 AM by user10302663 RSS

    Multiple Notifications enqueued on single queue not dequeued concurrently

    user10302663
      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
          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
            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