Gathering Statistics with Preferences of "Concurrent" Runs Slowly in Parallel Against Partitioned Tables Due to AQ_TM_PROCESSES Parameter Set to Zero
(Doc ID 1559487.1)
Last updated on JULY 05, 2021
Applies to:
Oracle Database Cloud Service - Version N/A and laterOracle Database - Enterprise Edition - Version 11.2.0.2.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
Symptoms
1. When executing a gather stats statement with preferences of 'CONCURRENT' and in parallel, some of the parallel sessions experience high waits for event such as:
"Streams AQ: waiting for messages in the queue" and "Streams: RAC waiting for inter instance ack".
The waits can be collated using TKProf on a SQL_TRACE/10046 tracefile:
2. The issue only occurs when using the global preference "concurrent" option, against partitioned tables. If global prefs concurrent option is false, the issue is not seen.
Changes
none
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 |