7 Replies Latest reply: Jun 23, 2014 1:57 AM by User13260331-Oracle RSS

    Queue not sending

      Hi all,

       

      11.2.0.1

       

      Our queue has lots of pending.

       

        select count(*) from orasms.MERCHANT_NOTIFY_QT;

       

       

        COUNT(*)

      ----------

             188

       

       

      The count is usually 0. But now its has lots of pending

       

      How do I troubleshoot this queue?

       

      Thanks all,

      pK

        • 1. Re: Queue not sending
          sb92075

          petra-K wrote:

           

          Hi all,

           

          11.2.0.1

           

          Our queue has lots of pending.

           

            select count(*) from orasms.MERCHANT_NOTIFY_QT;

           

           

            COUNT(*)

          ----------

                 188

           

           

          The count is usually 0. But now its has lots of pending

           

          How do I troubleshoot this queue?

           

          Thanks all,

          pK

          when all else fails Read The Fine Manual (URL below)

           

          Contents

          • 2. Re: Queue not sending

            Are there better answers than sb's 

            • 3. Re: Queue not sending
              John Stegeman

              petra-K wrote:

               

              Are there better answers than sb's 

              Yes there are.

               

              They are to be found in the documentation.

              • 4. Re: Queue not sending

                Hi all,

                 

                A kind followup on this please. Aside from Sb, John,Sybrand, as they are exempted

                 

                We always encounter high pending queues during payday when db processes reached 300. Our daily normal db processes is 200.

                How do you troubleshoot pending queues not being sent?

                 

                Thanks,

                • 5. Re: Queue not sending
                  User13260331-Oracle

                  Hi,

                   

                  First you need to check what is the status of messages in the queue, based on the proper action needs to be taken.

                   

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

                   

                   

                  Thanks,

                  Lalitha

                  • 6. Re: Queue not sending

                    Hi Lalitha, column name does not exist   Thanks

                     

                    select count(*), msg_state, consumer_name, queue from MCPGOWNER.aq$sms_send_qt group by msg_state, consumer_name, queue;

                    ORA-00904: "CONSUMER_NAME": invalid identifier

                     

                    desc aq$sms_send_qt

                    Name                  Null     Type                 

                    --------------------- -------- ----------------------

                    QUEUE                          VARCHAR2(30)         

                    MSG_ID                NOT NULL RAW(16 BYTE)         

                    CORR_ID                        VARCHAR2(128)        

                    MSG_PRIORITY                   NUMBER               

                    MSG_STATE                      VARCHAR2(16)         

                    DELAY                          DATE                 

                    DELAY_TIMESTAMP                TIMESTAMP(6)         

                    EXPIRATION                     NUMBER               

                    ENQ_TIME                       DATE                 

                    ENQ_TIMESTAMP                  TIMESTAMP(6)         

                    ENQ_USER_ID                    NUMBER               

                    ENQ_TXN_ID                     VARCHAR2(30)         

                    DEQ_TIME                       DATE                 

                    DEQ_TIMESTAMP                  TIMESTAMP(6)         

                    DEQ_USER_ID                    NUMBER               

                    DEQ_TXN_ID                     VARCHAR2(30)         

                    RETRY_COUNT                    NUMBER               

                    EXCEPTION_QUEUE_OWNER          VARCHAR2(30)         

                    EXCEPTION_QUEUE                VARCHAR2(30)         

                    USER_DATA                      AQ$_JMS_TEXT_MESSAGE()

                    ORIGINAL_QUEUE_NAME            VARCHAR2(30)         

                    ORIGINAL_QUEUE_OWNER           VARCHAR2(30)         

                    EXPIRATION_REASON              VARCHAR2(18)         

                    SENDER_NAME                    VARCHAR2(30)         

                    SENDER_ADDRESS                 VARCHAR2(1024)       

                    SENDER_PROTOCOL                NUMBER               

                    ORIGINAL_MSGID                 RAW(16 BYTE)

                    • 7. Re: Queue not sending
                      User13260331-Oracle

                      Use this query

                      select count(*), msg_state, queue from aq$<queue table name> group by msg_state, queue;