A few things can be reviewed 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 the SP_DEQ_MALLOC parameter and see if performance improves.
This parameter controls the fetch batch size.
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.
The value is divided equally by the number of compare threads to be used
And then the batch size is recalculated based on all column sizes added together.
Default: 500 (MB).
Range of valid values: 50 to 32,767 (MB).
Takes effect: Immediately available for the next comparison.
3. Use the WHERE clause option to limit the number of rows selected in the following scenarios:
To speed up processing when the tables are large and data synchronization is only critical for a subset of the records,
you can compare a subset of rows rather than all of the rows.
Example: (On Source):
sp_ctrl> compare table scott.emp to scott.emp target SysB@o.oraB repair where "file >001005"
sp_ctrl> compare table scott.emp to scott.emp target SysB@o.oraB where "file >001005" repair
4. 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 up 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 Sync
when, in reality, all rows are synchronized.
Instead, the command uses all of the columns.
If too many rows are out of sync, the “nosqllog” option can be used to improve the performance of compare/repair.
See KnowledgeBase article 228415 for further details.
