DDL on a Large Partioned Table Is Very Slow Due to Goldengate DDL Trigger (Doc ID 1438433.1)

Last updated on JULY 17, 2017

Applies to:

Oracle GoldenGate - Version 11.1.1.1.0 and later
Information in this document applies to any platform.

Symptoms

1. GGS_DDL_HIST_ALT has lots of duplicate ids then this has been encounterred.

2. For a table with hundreds of partitions, after enabling Oracle Goldengate DDL trigger, the DDL on the partition table is very slow.  SQL trace points to following SQL:

SELECT KEY.KEY_NAME INDEX_NAME, KEY.COLUMN_NAME, KEY.DESCEND
FROM
(SELECT C.CONSTRAINT_NAME KEY_NAME, C.COLUMN_NAME COLUMN_NAME, C.POSITION
POSITION, 'ASC' DESCEND FROM DBA_CONS_COLUMNS C WHERE C.OWNER = :B2 AND
C.TABLE_NAME = :B1 AND C.CONSTRAINT_NAME IN ( SELECT CON1.NAME FROM
SYS.USER$ USER1, SYS.USER$ USER2, SYS.CDEF$ CDEF, SYS.CON$ CON1, SYS.CON$
CON2, SYS.OBJ$ OBJ1, SYS.OBJ$ OBJ2 WHERE USER1.NAME = :B2 AND OBJ1.NAME =
:B1 AND CDEF.TYPE# = 3 AND BITAND(CDEF.DEFER, 36) = 4 AND (CDEF.TYPE# = 5
OR CDEF.ENABLED IS NOT NULL) AND CON2.OWNER# = USER2.USER#(+) AND
CDEF.ROBJ# = OBJ2.OBJ#(+) AND CDEF.RCON# = CON2.CON#(+) AND OBJ1.OWNER# =
USER1.USER# AND CDEF.CON# = CON1.CON# AND CDEF.OBJ# = OBJ1.OBJ#) UNION
SELECT I.INDEX_NAME KEY_NAME, C.COLUMN_NAME COLUMN_NAME, C.COLUMN_POSITION
POSITION, C.DESCEND DESCEND FROM DBA_INDEXES I, DBA_IND_COLUMNS C WHERE
I.TABLE_OWNER = :B2 AND I.TABLE_NAME = :B1 AND I.UNIQUENESS = 'UNIQUE' AND
I.OWNER = C.INDEX_OWNER AND I.INDEX_NAME = C.INDEX_NAME AND :B1 =
C.TABLE_NAME AND :B2 = C.TABLE_OWNER AND I.INDEX_NAME IN ( SELECT
INDEX_NAME FROM DBA_INDEXES WHERE TABLE_OWNER = :B2 AND TABLE_NAME = :B1
AND UNIQUENESS = 'UNIQUE') AND I.INDEX_NAME NOT IN ( SELECT
C.CONSTRAINT_NAME FROM DBA_CONS_COLUMNS C WHERE C.OWNER = :B2 AND
C.TABLE_NAME = :B1 AND C.CONSTRAINT_NAME IN ( SELECT C1.NAME FROM SYS.USER$
U1, SYS.USER$ U2, SYS.CDEF$ D, SYS.CON$ C1, SYS.CON$ C2, SYS.OBJ$ O1,
SYS.OBJ$ O2 WHERE U1.NAME = :B2 AND O1.NAME = :B1 AND D.TYPE# IN (2, 3) AND
(D.DEFER IS NULL OR D.DEFER = 0 OR BITAND(D.DEFER, 36) = 4) AND (D.TYPE# =
5 OR D.ENABLED IS NOT NULL) AND C2.OWNER# = U2.USER#(+) AND D.ROBJ# =
O2.OBJ#(+) AND D.RCON# = C2.CON#(+) AND O1.OWNER# = U1.USER# AND D.CON# =
C1.CON# AND D.OBJ# = O1.OBJ#) AND EXISTS ( SELECT 'X' FROM DBA_TAB_COLUMNS
T WHERE T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME
= C.COLUMN_NAME)) ) KEY ORDER BY KEY.KEY_NAME, KEY.POSITION


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 72 0.10 0.12 0 0 0 0
Fetch 72 2717.82 2654.60 0 580994675 0 66
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144 2717.92 2654.72 0 580994675 0 66

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