Messaging Gateway Agent polling query consumes excessive resources (Doc ID 1508606.1)

Last updated on NOVEMBER 06, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

The time to execute Messaging Gateway (MGW) polling queries of the form

select count(*) from <USER>.AQ$<QUEUE_TABLE> qt where
qt.msg_state = :1 AND qt.queue = :2 AND qt.consumer_name = :3 AND
qt.address IS NULL AND qt.protocol = 0

is being flagged up in ADDM

Recommendation 2: SQL Tuning
  Estimated benefit is .65 active sessions, 7.18% of total activity.
  ------------------------------------------------------------------
  Action
  Run SQL Tuning Advisor on the SQL statement with SQL_ID "61mfx7207dwnx".
  Related Object
  SQL statement with SQL_ID 61mfx7207dwnx.
  select count(*) from <USER>.AQ$<QUEUE_TABLE> qt where
  qt.msg_state = :1 AND qt.queue = :2 AND qt.consumer_name = :3 AND
  qt.address IS NULL AND qt.protocol = 0
  Rationale
  SQL statement with SQL_ID "61mfx7207dwnx" was executed 173 times and had
  an average elapsed time of 3.3 seconds.

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