Parallel DDL causes Capture to Slow Significantly / causes Logminer Spill (Doc ID 563217.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 to 11.1.0.6 [Release 10.2 to 11.1]
Information in this document applies to any platform.


Symptoms

Capture Slows dramatically.

Partition related DDL operation performed such as move partition.

Logminer slows due to spilling to logminer tables.

A typical PDDL activity might be:

alter table ice.alternate_reference move <partition_name>
tablespace <tablespace_name> storage(initial 1k) parallel 16;

The spill is visible in growth of a lob segment associated
with the table : system.logmnr_spill$.
To confirm this issue may be being encountered :

- Capture will be slow ;
- there is likely to be latency in changes being captured;
- there will be significant and increasing growth in the lob segment
  determined as follows :

select ds.segment_name, ds.partition_name, ds.segment_type,
ds.tablespace_name, ds.bytes, dl.segment_name, dl.column_name
from dba_segments ds, dba_lobs dl where
ds.segment_name = dl.segment_name and
dl.table_name = 'LOGMNR_SPILL$' and
dl.column_name = 'SPILL_DATA';


As parallel DDL may be associated with very large objects, the above
object may grow in an unbounded fashion.

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