enq: TS - contention

'enq: TS - contention' / Hang While Gathering Statistics in Parallel [ID 1463791.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms

• DBMS_STATS with parallel degree set to > 1 hangs with waits for 'enq: TS contention' event.
• The statistics gathering job is blocked by one of the parallel execution server processes and waits on 'enq: TS - contention'.
• Errorstack for the statistics gathering job shows sql like following:
  Insert /*+ append */ into sys.ora_temp_1_ds_350003 SELECT /*+  parallel(t,16) parallel_index(t,16) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)...

Cause

When parallel slaves are being used as part of a gather table statistics command (i.e gathering stats using parallel slaves) they can encounter TS enqueue contention.
The Insert sql identified above from the trace file looks like an exact match of (Unpublished) Bug 13707751 which was closed as duplicate of Bug 12865902.

Solution

Apply Patch:12865902 for Bug 12865902.

OR

As a workaround, consider gathering statistics with parallel degree set to 1. Since the 'enq: TS contention' only impacts  DBMS_STATS with parallel degree set to > 1; other activities work fine. You can change the defaults for collection of statistics using the information in the following article:

Document 725845.1 How to Change Default Parameters for Gathering Statistics