Gathering Statistics with Preferences of "Concurrent" Runs Slowly in Parallel Against Partitioned Tables Due to AQ_TM_PROCESSES Parameter Set to Zero
Last updated on MAY 22, 2017
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11.0 and later
Information in this document applies to any platform.
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.
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