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 MAY 22, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2.0 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

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