My Oracle Support Banner

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

Last updated on JULY 16, 2019

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

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
Cause
Solution

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.