3 Replies Latest reply: Mar 14, 2014 4:04 AM by WGabriel RSS

    Many Rows in State 3 - Expired

    Kamal Kishore-Oracle

      Hi

       

      I am using Oracle Database version 10.2.0.2.0 in my application.

      I have a queue in which the number of rows in state - 3 is increasing slowly. I have not done any changes on the logic to queue and De-queue. I have always used only one option and that is - VISIBILITY_COMMIT. All the others are defaults.

      I know that the status 3 means expired but why are the rows getting expired? They are not very old rows Is there any option that i need to use along with the one that i am using now?

      Also, where can i check the properties of my queue?

       

      Kindly help.

       

      Thanks

        • 1. Re: Many Rows in State 3 - Expired
          WGabriel

          Hello,

          you can check the basic properties of your AQ using the views AQ$<QUEUE_TABLE> (e.g. message state MSG_STATE).

          But here you cannot, of course, check your runtime message properties of your enqueue record.

          You have configured your enqueue options using

           

             v_enqueue_options.VISIBILITY         := DBMS_AQ.ON_COMMIT;

           

          Did you check the setting of v_message_properties.EXPIRATION? The default is DBMS_AQ.NEVER.

          What about the setting of MAX_RETRIES (DBMS_AQADM.CREATE_QUEUE) for the maximum number of

          dequeue attempts? Did you check your DEQUEUE process: how often is it checking your AQ concerning new inserts?

           

          Kind regards,

          WoG

          • 2. Re: Many Rows in State 3 - Expired
            Kamal Kishore-Oracle

            Hello,

             

            Enqueue Options are set using the below code :

             

              aqeo = new AQEnqueueOption();

              aqeo.setVisibility (aqmp.VISIBILITY_ONCOMMIT);

             

             

            Message properties are set using the below code :

             

              AQMessageProperty aqmp = new AQMessageProperty();

              aqmp.setPriority (1);

              aqmp.setDelay (aqmp.DELAY_NONE);

             

             

            Not setting any other enqueue options or message properties. I believe I should use aqmp.setExpiration(aqmp.EXPIRATION_NEVER) for no message to move to expiration.My requirement clearly is not to miss any message and pick every single one. Irrespective of the delay.

             

             

            The point that i do not understand is, the same piece of code is working since ever. Why am I suddenly seeing moving many messages into Expired state? If i load the queue with too many messages in a short interval, can this happen then?

             

            Also, how do i revive the expired messages after setting the expiration to never?

             

            Thank you very much for the response.

            • 3. Re: Many Rows in State 3 - Expired
              WGabriel

              Hello,

              you are using the JDBC API for AQ. In PL/SQL the default is DBMS_AQ.NEVER.

              For JDBC the documentation says:

              "setExpiration(int expiration): the duration the message is available for dequeuing;

              this parameter is an offset from the delay; if NEVER, the message will not expire".

              So you first should specify aqmp.setExpiration(aqmp.EXPIRATION_NEVER).

               

              > If i load the queue with too many messages in a short interval, can this happen then?

              No, this not depends on the amount of messages.

               

              Kind regards,

              WoG