This discussion is archived
10 Replies Latest reply: Aug 16, 2012 4:01 AM by a.aurel RSS

Messages remain in Queue Table

a.aurel Explorer
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    ---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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

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