10 Performance

10.1 Improving the Performance of Oracle GoldenGate Veridata

The following are some of the factors that influence the performance of Oracle GoldenGate Veridata and some ways you can improve its performance when processing large volumes of data.

Database and Network Use

Two critical performance factors for Oracle GoldenGate Veridata are:

  • How data is sorted
  • How the data is sent across the network

Performance Statistics for these performance factors are printed to the comparison report for each finished comparison and are recorded for the initial comparison step on the source and target. See Using the Comparison Report

Network Use

Oracle GoldenGate Veridata automatically optimizes its use of the network by using hashing and network message compression for efficient data transfer. The greater the size in bytes of the average row of a table or file (see the bytes/row performance statistic), the greater the rate of compression that is achieved through hashing (see the rh bytes/row and hash comp rate performance statistics). Whether a row is 50 bytes or 1000, the number of bytes that are used to represent its non-key values will be 12. Therefore, as a percentage of table size (in bytes), larger rows tend to use the network more efficiently. For those same reasons, the smaller the key size relative to row size, the more efficient the use of the network.

Additionally, on the NonStop platform check the send and receive TCP/IP buffer sizes. They should be set to 32K for Oracle GoldenGate Veridata.

Configuration Options

The following are some other things you can consider to improve comparison performance:

Partitioning Large Tables

You can divide large source and target tables into partitions. For more information on partitioning large tables, see Partitioning.

Fetching COOS Rows as Batch from Database

To fetch the coos rows from the database as a batch, you can set coos batch fetch. For more information, see Coos Batch Fetch.

Fetching COOS Rows using Temporary Table

This is applicable only to Oracle Database. To optimize the performance of fetching of rows from database during the confirm-out-of-sync phase, specifically for table with no primary key or unique key, by default the coos strategy will be selected as coos join. For more information, see coos.join.strategy in Server Parameters for Confirm Out-of-Sync Fetch Strategy Parameters of the Administering Oracle GoldenGate Veridata guide. This strategy uses temporary table either Private or Global to fetch rows from compare pair table. The veridata user need to have create table privileges.

Excluding Columns

In use cases in which some columns in a table do not require comparison, you can exclude such columns from the comparison, to reduce the processing load. For more information about excluding columns, see Exclude Columns .

Using delta processing

You can configure compare pairs to use delta processing, a performance feature whereby Oracle GoldenGate Veridata only compares data blocks that have changed, instead of comparing all of the rows in a table or file. For more information about delta processing, see Delta Comparison.

Specifying Primary Key Columns

For key columns, Oracle GoldenGate Veridata compares the data by value. Non-key column values are compared by hash. If there are no key columns, all columns are processed as key columns, and the comparison is done by value for every column in the table. Specify key columns to increase the compare performance. For more information about specifying primary key columns, see Specifying Primary Key (PK) Columns.

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 labeled 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 you think that there are too many rows being compared in the confirmation 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 affect of locks to preserve read consistency.

Note:

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

Profile options

You can control certain parameters for sorting, initial compare, coos, and repair functions, that can help increase performance.

Sorting Parameters

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

For more information, see Sorting Configuration.

Initial Comparison Parameters

  • Limit the number of rows that are compared: By using the Limit Number of Input Rows parameter for a specific job profile, you can constrain the number of rows that are fetched for processing. This enables you to process a smaller number of rows to get an idea of how out-of-sync (or not) the entire table is. Based on the results, you can make a decision about whether to run a complete comparison or just resynchronize the data. The Limit Number of Input Rows parameter is a General parameter for the initial comparison process.

  • To set this number:

    1. In the Oracle GoldenGate Veridata UI, click Profiles 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.

  • Increase processing threads: If the machine on which the Oracle GoldenGate Veridata Server is running has more processors, you can change the value of the Max Concurrent Comparison Threads parameter accordingly. For more information, see Max Concurrent Comparison Threads
  • Source and Target Optimizer Hint: For Oracle DB, hints can be used to improve performance. For more information, see Source and Target Optimizer Hint.

COOS Parameters

  • Run coos step separately

    By default, Oracle GoldenGate Veridata runs the initial compare and confirm-out-of-sync processes concurrently. If you run them in sequence, fewer system resources are used, but it will take longer to process the results.

    To set this parameter:
    1. In the Oracle GoldenGate Veridata UI, click Profiles and select a profile.
    2. Click the Confirm-out-of-sync tab and then update value for Run Concurrently With Initial Compare.
  • Skip coos step

    The default 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 set this parameter:
    1. In the Oracle GoldenGate Veridata UI, click Profiles and select a profile.
    2. Click the Confirm-out-of-sync tab and then update 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. 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.

  • Coos Batch Size: You can apply batching, to speed up the COOS phase. For more information, see Confirm-Out-Of-Sync(COOS) Batch Size.
  • Source and Target Optimizer Hint

Connection options

Try increasing the batch size of fetched rows to increase throughput. For more information on increasing the fetch size, see Connection Configurations.

Repair Parameters

You can use the parameters, Number of Concurrent Repair Operations and Repair Batch Size, to optimize performance for Repair Jobs. For more information, see Repair Configuration.

Parent topic: Performance

10.2 Performance Statistics

The two most critical aspects of Oracle GoldenGate Veridata performance are database access and network usage. Performance statistics for both of these aspects are printed to the comparison report for each comparison performed and are recorded for the initial comparison step on the source and target systems. The following describes these statistics. Depending on the results of these statistics, there are ways to optimize database access and network usage.

duration

The time spent processing the fetched rows.

rows fetched

The number of rows fetched from the database.

rows/sec

The number of rows processed per second.

bytes fetched

The total number of bytes that were processed.

bytes/sec

The number of rows, in terms of bytes, processed per second.

lob chunks fetched

The number of 32k blocks of LOB data fetched.

batches fetched

The number row batches fetched. The default is 10 rows per batch.

ipc msgs

The number of interprocess messages between the server and agent processes.

ipc bytes

The number of bytes transferred between the server and agent processes.

bytes applied

The number of bytes per message that were applied.

lob chunks applied

The number 32k byte LOB chunks applied at the target database.

lob fetch time duration (secs)

The amount of time spent fetching LOB data.

batches applied

The total number of bytes that were processed.

transaction batches

The number of transactions used to apply the data.

transaction single statements

the number of single row transactions applied during error recovery.

Parent topic: Performance