Analyzing and Improving GoldenGate Replicat Performance

Analysis of a slow replication configuration can be daunting. There are a lot of moving parts and it may not be clear where bottlenecks are appearing.

For traditional GoldenGate configurations, each Replicat process creates 1 connection to the database. If a replicat is consistently showing lag times, a performance check may be needed. This performance check can be made by using information from Replicat process and the Database the process is running against.

1) Identify the slow Replicat process

• Use GGSCI INFO ALL command to view current status of the Replicat
  
• Review monitoring information to determine historical performance of the Replicat
  

2) Verify the configuration
Determine the table(s) included in the slow performing Replicat:

• View the Replicat parameter file
  
• View the most recent Replicat report
  
• Use STATS command in GGSCI to show activity on replicated table names and operations
  

3) Check Database Performance using OEM or other tools
Check for busy connections to the database from the host where the Replicat processes are running. Look for INSERT/UPDATE/DELETE statements against the tables identified in previous steps.

• Make use of Top Activity utilities to see most active statements from the DB user login from the Replicat.
  
• Use AWR reports to determine the count and timing of top SQLs over time. SQL Execution by Elapsed Time is helpful here.
  
• Determine the most expensive statements - usually the statements taking the most time to execute
  

4) Check Replicat performance
You can use the STATS command in GGSCI to view tables that are being replicated by the process. The count of inserts/updates/deletes will be shown with the STATS output. Review the output to determine the tables with the largest number of operations over time. Tables with a large number of operations may not necessarily be using the most time. Due to performance of individual SQL statements it is possible that a table that doesn't see many operations may take a lot of time due to slow execution of SQL statements against the table.

Improving Performance of a GoldenGate Replicat process
Once you have determined the statements taking the most time you have 3 options:
1) Tune the SQL Statements being sent by the Replicat

• This is usually the preferred otion when UPDATES are doing Full Table Scans against a table. Often addition of an index will dramatically improve performance. Verify the columns that make up the Primary Key and ensure there is an index available on the target database for those columns.
  
• Ensure there is no locking/blocking in he DB or any other impediments to the SQL statement running normally.
  

2) Split the tables with expensive operations into their own separate replicat

• Modify existing Replicat by removing the table from the configuration. You can leverage DDL EXCLUDE and MAPEXCLUDE statements for this.
  
• Create, configure, and start a new Replicat process for the table. Typically this will be an additional Replicat that is still pointing to the original trail files.
  3) If the table already has its own Replicat, split its processing into multiple replicats using the @RANGE function
  The @RANGE function uses a hash algorithm to split up work into multiple ranges.
  
  
  
  • Stop the single Replicat
    
  • Record SEQNO and RBA positions from INFO command
    
  • Configure new replicats. For example if configuring 3 replicats to take place of 1, add FILTER (@RANGE (1,3)), , FILTER (@RANGE (2,3)), FILTER (@RANGE (2,3)) to MAP statement in each of the new replicats so each one will handle 1/3 of the work.
    
  • Reposition the new Replicats to same SEQNO/RBA position as recorded above so no transactions are missed or duplicated.
    
  • Start the new replicats. Verify they are working using STATS command.
    
  • Drop original replicat as it will no longer be used.