How Can I Configure Oracle's Supplemental Logging for Sync Performance?

Question

How can I optimize Oracle's supplemental logging configuration to improve the sync performance of Oracle connectors?

Environment

Oracle connectors that use LogMiner or High-Volume Agent as the incremental sync method.

Answer

Using supplemental logging requires extra system resources of the Oracle host, which may impact the performance of the sync extraction process.

To minimize the impact on the sync's performance, do the following:

1. Use database-level minimal supplemental logging with table-level identification key logging. See our Oracle setup guide for more information.

2. Ensure that neither all columns nor primary key supplemental logging are enabled at the database level. You can use the following verification query:

   SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,   
   SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_PK  
   FROM V$DATABASE;
   

3. Enable supplemental logging for only the tables you want to sync. You can use the following verification query:

   SELECT OWNER, TABLE_NAME, LOG_GROUP_TYPE  
   FROM ALL_LOG_GROUPS   
   WHERE OWNER='REPLACE_WITH_SCHEMA_NAME'  
   AND TABLE_NAME IN ('REPLACE_WITH_TABLE_NAME')  
   ORDER BY OWNER, TABLE_NAME;
   

4. Enable all columns supplemental logging only on:

   • tables that do not have a primary key.
   • tables with a primary key where values are expected to change, including the columns of a composite primary key.

5. Enable primary key supplemental logging for tables with a primary key where values are not expected to change.

Modifying the use of supplemental logging

To modify the use of supplemental logging, do the following:

1. Pause the connector.
2. Enable or disable supplemental logging as needed.
3. Unpause the connector.

NOTE: If the table's SCN is no longer available to us, an automatic table re-sync may occur.

Query Examples

To verify that both SUPPLEMENTAL_LOG_DATA_ALL and SUPPLEMENTAL_LOG_DATA_PK are not enabled, use the following query:

SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,  
SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;

To verify the log group type for tables you intend to sync, use the following query:

SELECT OWNER, TABLE_NAME, LOG_GROUP_TYPE FROM ALL_LOG_GROUPS 
WHERE OWNER='TEST_SCHEMA' AND TABLE_NAME IN ('TEST_TABLE_1', 'TEST_TABLE_2') 
ORDER BY OWNER, TABLE_NAME;