Goldengate Query Very Slow (Doc ID 1597202.1)

Last updated on MAY 17, 2017

Applies to:

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

Symptoms

OGG installed recently, detecting slowness in the following query on the database.

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#) 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) 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 (VISIBILITY != 'INVISIBLE' OR 'FALSE' = 'TRUE') 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)) AND EXISTS ( SELECT 'x' FROM DBA_TAB_COLUMNS T WHERE T.OWNER = :B2 AND T.TABLE_NAME = :B1 AND T.COLUMN_NAME = C.COLUMN_NAME) ) KEY ORDER BY KEY.KEY_NAME, KEY.POSITION


Changes

Recently installed OGG and enabled DDL replication trigger on the database.

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