Compare running slow. (18765)

Compare is running slow.

One or more reasons can slow down the compare at the time you are running compare. See resolution below.

One or more of the followings, but not limited to, can slow down the compare while it is running:

1. Large number of messages in the backlog of the post queue.

2. Resouce busy on the target tables when compare using repair option because compare needs to lock the tables for repair.

3. Now there are more rows in the tables.

4. Less overall free resource (CPU, memoery) on both source and target servers can slow down everything.

5. Slow response from the target (network issue).

Few things can be tried to speed up compare:

1. Run compare during non peak hours when there are near zero messages in the queues.

2. Increase the value of SP_DEQ_BATCHSIZE and see if it gets improved. This parameter controls the batch size that SharePlex uses for the compare/repair function when the compare config or compare table command is issued. The batch size controls the number of rows that SharePlex selects at once for comparison. Larger batch sizes increase processing speed but require more memory. Memory usage also depends on the number and size of the columns in the table(s) being compared, and the number of compare processes running at the same time.

If the SP_DEQ_BATCHSIZE parameter is larger than the number of rows in the source table being compared, SharePlex reduces the batch size to the number of rows in the table. That prevents SharePlex from allocating more space than it needs for the compare processes. As you gain experience with the compare commands, you might find a more optimal setting for this parameter.

Default: 10,000 rows
Range of valid values: 20 to 32,767 rows (up to the maximum number of rows in the
table)
Takes effect: Immediately available for the next comparison

3. Use WHERE clause options to constrain row selection in the following situations:

To speed processing when the tables are large and data synchronization is only critical for a subset of the records. You can compare the subset instead of all of the rows.

Example:
sp_ctrl(SysA)> compare table scott.emp to scott.emp target SysB@o.oraB
repair where "file >001005"
sp_ctrl(SysA)> compare table scott.emp to scott.emp target SysB@o.oraB
where "file >001005" repair

4. Having a primary key or a unique, non-null key and an index (preferably a unique index) on large source and target tables speeds the ordering process:

The compare table command issues a SELECT statement with an ORDER BY clause on the source and target systems. Having a primary key or a unique, non-null key and an index (preferably a unique index) on large source and target tables speeds the ordering process.

SharePlex orders the rows by using the best available key columns, as determined by the order of priority listed below:

1) Primary key.

2) Unique key with the fewest columns, where at least one of the columns is NOT NULL.

3) All columns (except LONG, LOB, and BFILE) up to a maximum of 255 columns.

This serves as a simulated unique key but makes sorting significantly slower.

Note: Unique keys that allow NULL columns are not used, because they can cause the compare process to report that a table is out-of-synchronization when, in reality, all rows are synchronized. Instead, the command uses all of the columns.