My Oracle Support Banner

SOA DB Query On DLV_MESSAGE Consumes High I/O Due To A /*+ FULL(DLV_MESSAGE) */ Hint (Doc ID 2806573.1)

Last updated on SEPTEMBER 17, 2021

Applies to:

Oracle SOA Suite - Version 12.2.1.2.0 and later
Information in this document applies to any platform.

Symptoms

The DBA team noticed that the below query runs periodically and consumes high I/O
The query has a hint that forces a full table scan so no index introduction can improve the query:
SELECT /*+ FULL(DLV_MESSAGE) */ MESSAGE_GUID, COMPONENT_NAME, COMPOSITE_LABEL, COMPOSITE_NAME, COMPOSITE_REVISION, DOMAIN_NAME, RECOVER_COUNT FROM
DLV_MESSAGE WHERE
STATE = 0 AND
RECEIVE_DATE < :1 AND
DLV_TYPE = 1 AND
COMPONENT_TYPE = :2 AND
ROWNUM <= :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
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.