Upon turning-on
the Oracle 10g servererror trigger we were surprised to see tens of
thousands of log entries for the ORA-25228 error. Also, see my
notes on using
Oracle
system triggers for complete details:
ERROR
NBR ERRORS
ORA-25228: timeout or
end-of-fetch during message 17,193
ORA-25254: time-out in LISTEN while waiting for a 2,865
The Oracle docs
note:
ORA-25254: |
time-out in LISTEN while waiting for a message |
Cause: |
The specified wait time has elapsed and there were no
messages for any of the agents in the agent-list. |
Action: |
Try the LISTEN call with an appropriate time-out.
|
ORA-25228 |
timeout or end-of-fetch during message dequeue from
string.string |
Cause: |
User-specified dequeue wait time has passed or the end
of the queue has been reached but no message has been
retrieved. |
Action: |
Try dequeue again with the appropriate WAIT_TIME or the
FIRST_MESSAGE option |
Logging all of
these these ORA-25228 and ORA-25254 servererrors causes measurable
overhead, and there was no reported problems with the application,
which was using Oracle Streams replication. We logged an SR on
the ORA-25228 issue and also noted this on the web, suggesting that
the ORA-25228 error may happen if a queue contains no messages:
We suspect that
these are "noise" errors and should be suppressed so as not to cause
excessive overhead for the servererror trigger.
***************************************************
Question: In pl/sql, is
there a simple way to determine if a consumer is subscribed to a
queue? It seems I get the same error message if there are no
messages or if the consumer is not subscribed (ORA-25228:
timeout or end-of-fetch during message dequeue from
AQADM.QUE_JREX). I could query the _s table but is there a
package procedure I could use?
Answer: You would have to write your own PL/SQL routine
which would query the aq$<queuetablename>_s view.