The performance of data synchronization between the source and destination tables is affected by many different factors including the availability of various resources or any contention affecting the availability of those resources. This includes processors, memory, disk queueing, extent allocation, row locking, etc.
The primary bottleneck during synchronization is often the wait events associated with db file sequential reads during index unique scans on the PKCI_DOC index and subsequent table access by index rowid on the IN_DOC table. As with most operations that require fetching blocks from disk, the performance of disk reads is critical.
Another significant factor in the performance of data synchronization is the presence of indexes on the destination table. As the uptime steps are completed and all the indexes are created on the destination table this adds overhead as those indexes must be maintained and updated as data is inserted into the destination table.
Faster throughput can be achieved with setting the destination table and its indexes to NOLOGGING during setup of the upgrade and synchronization framework. Note, however, that this impacts the recoverability for these objects so consideration must be made with respect to backups and any disaster recovery solutions that are configured that utilize archived redo logs (log shipping) for replication.
By design, the synchronization process does not run in parallel so as to prevent the unecessary overhead associated with managing parallel processes for very small operations.
The test environment used to measure throughput was a Linux virtual machine with Oracle 12.2.0.1.0 which had an SGA size of 20GB and 16 CPU’s available to the instance. The load on the instance was limited to the synchronization process so resource contention was minimal.
Testing was conducted with row counts between 0 and 90 million rows in the SYNC_STAGE_DOC table and up to 465 million rows across the source and destination tables. Testing was conducted with LOGGING enabled for all objects.
According to an Oracle Active Session History (ASH) Report the following indexes had the largest impact towards degrading the performance of data synchronization. The impact of each index can vary depending on the implementation and usage of the available document keys.
NEW_DOC_IDX2 - 19.57 % - db file sequential read (DRAWER_ID, FOLDER, TAB, F3, F4, F5, DOC_TYPE_ID)
NEW_DOC_IDX83 - 16.09 % - db file sequential read (F3)
NEW_DOC_IDX84 - 10.18 % - db file sequential read (F4)
NEW_DOC_IDX82 - 3.49 % - db file sequential read (TAB)
NEW_DOC_IDX85 - 2.81 % - db file sequential read (F5)
The following synchronization throughput was recorded during testing
- With no indexes on the destination table other than the
PK and PKCI indexes, which should be in place for efficient row
lookup and synchronization.
Throughput was between 800K - 1.2M Rows Per Minute (RPM)
- With all indexes except NEW_DOC_IDX2, NEW_DOC_IDX83, NEW_DOC_IDX84.
Throughput was between 70K - 212K Rows Per Minute (RPM)
- With all indexes having been created on the destination table.
Throughput was between 3K - 7K Rows Per Minute (RPM)
Due to the significant increase in the duration of the synchronization process when indexes are in place, it is advised to delay index creation until closer to the scheduld downtime event. You may also conduct your own internal benchmarking to measure the effects and choose to subsequently remove the indexes or the entire synhronization framework until the days or hours leading up to the scheduled downtime.