My Oracle Support Banner

GG Replicat takes time to catch up with lag due to slow performance of internal sql's to DBA_GOLDENGATE_PRIVILEGES (Doc ID 2754509.1)

Last updated on MARCH 23, 2021

Applies to:

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

Symptoms

Below SQL was taking more time to complete when replicat was started up. Number of executions of below sql depends on number of Tables in replicat and each execution was taking 2.8 secs

In 2 hours, it was found that around 483 executions of this sql took place and it took avg of 2.8 secs per execution. So, replicat took time to catchup with lag.

SELECT COUNT(*) FROM ( SELECT GP.USERNAME FROM DBA_GOLDENGATE_PRIVILEGES GP WHERE DBMS_LOGREP_UTIL.STRCMP_CS(:B1 , GP.USERNAME)
 = 0 AND GP.GRANT_SELECT_PRIVILEGES = 'NO' AND NOT EXISTS (SELECT 1 FROM DBA_ROLE_PRIVS WHERE DBMS_LOGREP_UTIL.STRCMP_CS(:B1 , GRANTEE) = 0 AND DBMS_LOGREP_UTIL.STRCMP_CS(GRANTED_ROLE, 'DBA') = 0 )
 UNION ALL SELECT XP.USERNAME FROM DBA_XSTREAM_ADMINISTRATOR XP WHERE DBMS_LOGREP_UTIL.STRCMP_CS(:B1 , XP.USERNAME)
 = 0 AND XP.GRANT_SELECT_PRIVILEGES = 'NO' AND NOT EXISTS (SELECT 1 FROM DBA_ROLE_PRIVS WHERE DBMS_LOGREP_UTIL.STRCMP_CS(:B1 , GRANTEE) = 0 AND DBMS_LOGREP_UTIL.STRCMP_CS(GRANTED_ROLE, 'DBA') = 0 ))

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.