5 Tuning Performance

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.

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).

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.

By default, Oracle GoldenGate Veridata uses the database to sort data for comparison. The following factors affect the performance of the database 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

After some test runs, if the performance of comparisons is not satisfactory, it might be faster to use server-side sorting, where Oracle GoldenGate Veridata Server itself performs the sorting.

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

You can divide large source and target tables into row partitions, each partition being associated with a different row subset. Row partitions enable you to process sets of data in parallel and also to control the timing of processing. For example, you can compare one partition today and the other one tomorrow. In addition, the results of a subset comparison can give you an idea of the synchronization status of the entire table.

If a table contains columns that you know will never change, or if it does not matter whether those columns are in-sync, you can exclude those columns from the comparison to reduce the processing load. You exclude columns when you create or edit compare pairs.

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 Using Delta Processing.

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.

You can control certain parameters for the initial and confirmation steps that can help increase performance.

• 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.

• 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: The default is four threads. If the machine where Oracle GoldenGate Veridata Server is running has more processors, you can change the value of the Max Concurrent Comparison Threads parameter accordingly, keeping all threads busy by executing simultaneous comparisons. This parameter is in the General profile settings of the initial comparison process.

• Run each comparison step separately: By default, Oracle GoldenGate Veridata runs the initial compare and confirmation processes concurrently. If you run them in sequence, fewer system resources are used, but it will take longer to get results. This functionality is controlled by the Run Concurrently with Initial Compare parameter of the comparison step's General profile settings.

• Skip the confirmation step: The default is to always perform a confirmation step. You can skip this step if the database is quiesced or if replication is not actively replicating data changes. Use the Perform Confirmation Step parameter of the confirmation step's General profile settings.

• 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.

Try increasing the batch size of fetched rows to increase throughput. To do this, increase the size of the Initial Compare Fetch Batch Size parameter for the initial and confirmation steps.

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.

The time spent processing the fetched rows.

The number of rows fetched from the database.

The number of rows processed per second.

The total number of bytes that were processed.

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

The number of 32k blocks of LOB data fetched.

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

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

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

The number of bytes per message that were applied.

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

The amount of time spent fetching LOB data.

The total number of bytes that were processed.

The number of transactions used to apply the data.

the number of single row transactions applied during error recovery.