Parallel DDL causes Capture to Slow Significantly / causes Logminer Spill
Last updated on MAY 12, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.3 to 126.96.36.199 [Release 10.2 to 11.1]
Information in this document applies to any platform.
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.
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