10 Replies Latest reply: Aug 16, 2012 6:01 AM by a.aurel RSS

    Messages remain in Queue Table

    a.aurel
      Hello,
      I work as a dba in a company where my colleagues are developing an application that uses AQ.
      The application is installed on multiple databases but we have a problem in a particular one:
      the processed messages are not deleted from table by Oracle.

      DBMS_AQADM.CREATE_QUEUE( Queue_name => 'my_AQ', Queue_table => 'AQT', Queue_type => 0, Max_retries => 65535, Retry_delay => 0, Retention_time => 2592000, dependency_tracking => FALSE);

      SELECT msg_state, count(*) FROM AQT GROUP BY msg_state;
      -----------------------------------------
      PROCESSED     830548


      SELECT min(enq_time),max(enq_time) FROM AQT
      -----------------------------------------
      07-OCT-10      01-AUG-12

      SELECT value FROM v$parameter WHERE name='aq_tm_processes'
      -----------------------------------------
      10

      thanks,
        • 1. Re: Messages remain in Queue Table
          758358
          Hi,

          What version of the database are you running? Is this RAC or single instance?

          Are there any messages being output to the Qmon trace files? Can you actually see the q00n background processes on the DB host? If you trace their processes back to the database what are they waiting on?

          Did you review:

          Master Note for AQ Queue Monitor Process (QMON) (Doc ID 305662.1)
          Section: PROCESSED Messages not being removed

          Thanks
          Paul
          • 2. Re: Messages remain in Queue Table
            a.aurel
            ---What version of the database are you running? Is this RAC or single instance?

            11.2.0.1 single instance

            ---Can you actually see the q00n background processes on the DB host?

            select pname,tracefile from v$process where pname like 'Q0%'
            -----------------------------------------------------------------
            Q000 ....G_q000_25755730.trc
            Q001     ....G_q001_25690214.trc
            Q002     ....G_q002_20709516.trc
            Q00A     ....G_q00a_26738758.trc
            Q003     ....G_q003_19857412.trc
            Q004     ....G_q004_13369598.trc
            Q005     ....G_q005_7733370.trc
            Q006     ....G_q006_24772736.trc
            Q007     ....G_q007_20840488.trc
            Q008     ....G_q008_5308658.trc
            Q009     ....G_q009_22348028.trc

            ---Are there any messages being output to the Qmon trace files? ? If you trace their processes back to the database what are they waiting on?

            os cmd: pwd
            ----------------------------------
            same as: SELECT value
            FROM v$diag_info
            WHERE name = 'Diag Trace';

            os cmd: ls -l ....G_q0<TAB>
            --------------
            null
            • 3. Re: Messages remain in Queue Table
              758358
              Hi,

              I didn't notice this in your first post:
              DBMS_AQADM.CREATE_QUEUE( 
                  Queue_name => 'my_AQ', 
                  Queue_table => 'AQT', 
                  Queue_type => 0, 
                  Max_retries => 65535, 
                  Retry_delay => 0, 
                  Retention_time => 2592000,      <<<<<<<<<< why are you specifying such a high value for this?
                  dependency_tracking => FALSE
              );
              Retention_time: See doc for this value: http://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_admin.htm#i1006091

              This parameter specifies the number of seconds a message is retained in the queue table after being dequeued from the queue. When retention_time expires, messages are removed by the time manager process. INFINITE means the message is retained forever. The default is 0, no retention.

              You are asking for messages to be retained for 30 days, now looking at the min/max enq_time you have messages which are much older than this, but did you check when they were actually DQ'd (DEQ_TIME)?

              Also, from my previous post, did you check out that MOS note ? Did you check what the Q00 processes are doing in the database?

              Thanks
              Paul
              • 4. Re: Messages remain in Queue Table
                a.aurel
                Thanks for prompt response.

                I want the messages to be retained for 30 days.

                DEQ_TIME is null for all records and msg_state is 'PROCESSED'! it's ok?

                I checked the document on MOS:
                Note 251737.1 PROCESSED Messages remain in Queue Table after a Successful Dequeue
                -------
                AQ_TM_PROCESSES parameter is set to 10


                Note 378247.1 PROCESSED Messages not removed from Queue Table in a RAC database after Reconfiguration
                -------------
                I don't have RAC


                Note 752708.1 Intermittently PROCESSED Messages are not removed from Queue Tables by the QMON Processes.
                -------------
                The problem is only observed on the HP-UX Itanium port. I have AIX
                • 5. Re: Messages remain in Queue Table
                  758358
                  Ok, might be worth just checking some more data about these "PROCESSED" messages:

                  select consumer_name, address, queue, msg_state, count(*)
                  from aq$<queue_table_name>
                  group by consumer_name, address, queue, msg_state;

                  Thanks
                  Paul
                  • 6. Re: Messages remain in Queue Table
                    a.aurel
                    select consumer_name, address, queue, msg_state, count(*)
                    from aq$<queue_table_name>
                    group by consumer_name, address, queue, msg_state;
                    --------------------------------
                    SENDER_CB          AQ_*******_Q            PROCESSED           836632                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    • 7. Re: Messages remain in Queue Table
                      758358
                      Hi,

                      Could you run these queries and post the output?
                      select count(*) 
                      from AQ$_<queue_table_name>_I i 
                      where not exists (select t.msgid 
                                        FROM <queue_table_name> t where i.msgid = t.msgid);
                      
                      select count(*) 
                      from AQ$_<queue_table_name>_T i 
                      where not exists (select t.msgid 
                                        FROM <queue_table_name> t where i.msgid = t.msgid);
                      
                      select count(*) 
                      from AQ$_<queue_table_name>_H i 
                      where not exists (select t.msgid 
                                        FROM <queue_table_name> t where i.msgid = t.msgid);
                      Substitute in your queue table name for <queue_table_name>

                      Thanks
                      Paul
                      • 8. Re: Messages remain in Queue Table
                        a.aurel
                        count(*)=0 for all three


                        ;

                        select count(*)
                        from AQ$_<queue_table_name>_I => 0

                        select count(*)
                        from AQ$_<queue_table_name>_T => 775611

                        select count(*)
                        from AQ$_<queue_table_name>_H i => 785555

                        Edited by: a.aurel on 13.08.2012 18:07
                        • 9. Re: Messages remain in Queue Table
                          758358
                          You say count(*)=0 for all 3 but then the output shows differently? I'll assume the output is correct.

                          You have orphaned data in the underlying queue structures which will probably be preventing the QMON processes from removing the data as it is incomplete.

                          Have you at any point used the purge_queue_table procedure in dbms_aqadm? There is a known issue with this which can leave the queue in this state - I think it is fixed in 11.2.0.1 but if this database was upgraded from a previous version it could have happened then.

                          Also, do you happen to know if anyone has been manually updating the queue table structures?

                          I'd recommend raising an SR with support for advise on cleaning this up - I think you can just safely delete those orphaned entries but best to get their view, having cleaned them up you might also want to re-org the queue objects to get some space back:

                          How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

                          HTH
                          Paul
                          • 10. Re: Messages remain in Queue Table
                            a.aurel
                            I do not know if my colleagues (DEV team) have manually deleted or purged the table.

                            Most likely I will remove them and I watch carefully what happens next.

                            I appreciate your effort to help me. Thank you.