Queries on GV$AQ Yield Incorrect Results And Errors Like ORA-01476 After Upgrading to 11.2.0.3 (Doc ID 1506218.1)

Last updated on NOVEMBER 06, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

 Oracle 11.2.0.3 RAC database with two nodes in both primary and standby site, we are unable to query GV$AQ.

 SQL> list
  1  select
  2  b.name,
  3  a.QID ,
  4   a.WAITING,
  5   a.READY  ,
  6   a.EXPIRED,
  7   a.TOTAL_WAIT,
  8   a.AVERAGE_WAIT
  9  from gv$aq a , system.AQ$_QUEUES b
 10  where (a.EXPIRED > 0 or a.READY > 0 or a.WAITING > 0)
 11  AND a.QID not in (796030)
 12* AND a.QID = b.EVENTID

ERROR at line 12:
ORA-01476: divisor is equal to zero
Elapsed: 00:00:00.04

When system is not so busy, or when we query GV$AQ1 dictionary, we got result in negative value in READY column as below and will not complete the query:
       QID    WAITING      READY    EXPIRED AVERAGE_MSG_AGE TOTAL_WAIT AVERAGE_WAIT
---------- ---------- ---------- ---------- --------------- ---------- ------------
   820853          0        -82          0           56719    1644869        56719
    820858          0   -3223152          0           45584 1.6690E+10        45584
   1168828          0          0          0               0          0            0

Changes

 Upgraded the database to 11.2.0.3

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms