My Oracle Support Banner

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 MARCH 07, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.