SOA DB Query On DLV_MESSAGE Consumes High I/O Due To A /*+ FULL(DLV_MESSAGE) */ Hint
(Doc ID 2806573.1)
Last updated on MAY 10, 2023
Applies to:
Oracle SOA Suite - Version 12.2.1.2.0 and laterInformation 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 |