5.1 Improving the Performance of Oracle GoldenGate Veridata

You can use the following parameters and configurations to improve the performance of Oracle GoldenGate Veridata, when processing large volumes of data.

Adding Primary Key Columns

Refer Specifying PK Columns section to know how adding primary key can help improve the performance.

Partitioning

Refer Partitioning section to know how diving data into comparable units chunks can help improve performance.

Delta Comparison

Refer Delta Comparison section to know how delta compare can help improve performance.

Excluding Columns

Refer Excluding Columns section to know how and when to remove columns to help improve performance.

Server Configurations

COOS join

Refer Server Configurationswhen to use COOS join and other server configurations that can improve performance.

Agent Configurations

  • COOS Batch Fetch

    You can use COOS Batch Fetch instead of fetching data individually, so that the agent fetches data in batches. Refer COOS Batch Fetch.

    The size of this batch is decided by COOS batch size. Refer Confirm-Out-of-Sync (COOS) Batch Size to know how to update the COOS batch size.

  • ROWSCN

    You can use ROWSCN to skip full table comparison and compare data after a particular SCN value.

    Refer ROWSCN to know how to set the ROWSCN value.
  • Change the Database Transaction Isolation Level

    Each Oracle GoldenGate Veridata agent has an agent.properties file in the root of its installation folder that contains environment parameters. One of those parameters is database.transaction.isolation. This property controls the transaction isolation level that is used during the initial comparison step.

    The default value for SQL Server and Teradata is READ_UNCOMMITTED. This means that the query that is issued to select rows can read rows that have been modified by other transactions but not yet committed (dirty reads). It does not issue shared locks to prevent other transactions from modifying the data, nor is it blocked by the locks of other transactions.

    The advantage of using the READ UNCOMMITTED option is that the initial reads of the data are faster because they are not affected by locking. The negative impact of this is that more records could be labelled as possibly out-of-sync (because the data can change over the course of the transaction) and would need to be compared again during the confirmation step.

    If there are too many rows being compared in the COOS step, you can edit the properties file and set database.transaction.isolation to COMMITED, which only permits the fetching of committed records. You must weigh any improvement against the possibility that the initial comparison step becomes slower due to the effect of locks to preserve read consistency.

    Note:

    The only value that is supported for Oracle is READ_COMMITTED, and the COOS step always uses READ_COMMITTED, because at this stage dirty reads are not acceptable.

Profile Configurations

You can control certain parameters for the sorting, initial compare, coos and repair that can help increase performance.
  • Sorting Configurations

    Server Sort

    The following factors affect the performance of the sorting mechanism:
    • The number of rows in the tables being compared
    • The indexes that are defined on the tables
    • The keys that are being used
    • The way that the database is tuned

    Refer Server Sort, to know how to use server-side sorting.

  • Initial Comparison Configurations
    • Max Concurrent Comparison Threads: Refer Max Concurrent Comparison Threads, to know how to use comparison threads.
    • Optimizer Hints: Refer Source and Target Optimizer Hint to know how to use optimizer hints.
    • Limit Number of Input Rows: Limit the number of rows that are compared, by using this parameter. You can constrain the number of rows that are fetched for processing, for a specific job profile. This enables you to process a smaller number of rows to find out how out-of-sync (or not) the entire table is. Based on the results, you can run a complete comparison or just resynchronize the data. This parameter is a general parameter for the initial comparison process.
      To set this parameter:
      1. In the Oracle GoldenGate Veridata UI, click Profile Configuration and select a profile.
      2. Click the Initial Compare tab and then update value for Limit Number of Input Rows

    • Increase process priority (NonStop only): Assign the Oracle GoldenGate Veridata Agent the highest process priority possible on a NonStop system. You can assign a priority (as well as a process name and CPU number) by using the NonStop settings of the initial and confirmation steps in the job profile.

  • Out-of-Sync Configurations

    • COOS Batch Size : Refer COOS Batch Size, to know how to use COOS batching.

    • Optimizer Hints Refer Optimizer Hints, to know how to use optimizer hints.

    • Run COOS in Parallel with Initial Compare

      By default, Oracle GoldenGate Veridata runs the initial compare and confirm-out-of-sync processes concurrently. By default, Oracle GoldenGate Veridata runs the Initial Compare and Confirm-Out-of-Sync (COOS) processes concurrently. Ensure that this setting is enabled.

      To verify this:

      In the Veridata UI, click Profile Configuration and select a profile.

      Click Confirm-Out-of-Sync. Check the value for Run Concurrently With Initial Compare. This option should be selected (checked).

    • Skip COOS step

      The default setting is to always perform a COOS step. You can skip this step if the database is quiesced or if replication is not actively replicating data changes.

      To skip COOS step:
      1. In the Oracle GoldenGate Veridata UI, click Profile Configuration and select a profile.
      2. Click the Confirm-out-of-sync tab and then uncheck the Use Default and Value for Perform Confirm Out-of-Sync Step.

    • Increase process priority (NSK)

      Assign the Oracle GoldenGate Veridata Agent the highest process priority possible on a NonStop system. Use the NonStop Process Setting under Initial compare and Confirm-out-of-sync in Profile Configuration.

Connection Configurations

Compare Fetch Size

Increase the batch size of fetched rows to increase throughput. Refer Compare Fetch Size to know how to increase fetch size.

Repair Configurations

Refer Repair Configuration, to know how to set the following parameters to improve performance.
  • Number of Concurrent Repair Operations
  • Repair Batch

Parent topic: Tuning Performance