The "copy table" and "append table" are typically used for synchronizing large tables, frequently of the order of gigabytes and occasionally of the order of a terabyte or more. This may require some tuning of the sync server or sync client parameters. Currently there is not much that can be tuned and the following describes the parameters that are a candidate for such tuning.
Need to improve performance of copy
The following parameters may be useful in improving the speed of the copy. They are all live meaning that they do not require any process bounce. All one needs to do is to set the parameter prior to issuing the next "copy table" and "append table" command. It may be worth setting them to a certain level and use trial and error approach to fine tune them as needed.
Source:
SP_OSY_EXP_BUFFER_SIZE
This parameter adjusts the amount of data that is buffered before being sent to the target
system.
Internal
Introduced in version 6.0
Default: 1024 kbytes
Range of valid values: any integer > 0 (in kilobytes)
Takes effect: immediately available for the next sync
SP_OSY_COMPRESSION
This parameter adjusts the data compression level from off (0) to full (9).
User configurable
Introduced in version 6.0
Default: 6
Range of valid values: 0 to 9 (9 being the highest level of compression)
Takes effect: immediately available for the next sync
SP_OSY_THREADS
This parameter sets the number of pairs of synchronization processing threads, export/
import, between the source and target systems. This parameter is utilized by SharePlex
sync processes, e.g. the copy/append commands.
User configurable
Introduced in version 6.0
Default: 5
Range of valid values: 1 to 10
Takes effect: immediately available for the next sync
Target:
SP_OSY_IMP_BUFFER_SIZE
This parameter adjusts the a mount of data that is buffered before being applied on the
target system.
Internal
Introduced in version 6.0
Default: 1024 kbytes
Range of valid values: any positive integer (in kilobytes)
SP_OSY_COMPRESSION
This parameter adjusts the data compression level from off (0) to full (9).
User configurable
Introduced in version 6.0
Default: 6
Range of valid values: 0 to 9 (9 being the highest level of compression)
Takes effect: immediately available for the next sync
SP_OSY_THREADS
This parameter sets the number of pairs of synchronization processing threads, export/
import, between the source and target systems. This parameter is utilized by SharePlex
sync processes, e.g. the copy/append commands.
User configurable
Introduced in version 6.0
Default: 5
Range of valid values: 1 to 10
Takes effect: immediately available for the next sync
The values of the SP_OSY_EXP_BUFFER_SIZE and SP_OSY_IMP_BUFFER_SIZE parameters are passed to the export and import processes. They determine the size of the buffer used internally for data that is being written out to the export pipe or applied to the target database. The sync processes also buffer data between export or import and the tcp/ip connection over which the data is sent.
The first thing that the sync server does is to convert the list of objects that it gets from the arugment file into the list of objects that it is going to copy. Since currently you can only copy one table at a time (there is no equivalent command to copy all objects in a config at present), the resulting list will either contain one table, or it will contain all the partitions/subpartitions of one table. The sync server portions out the work of copying the objects in this list to one or more threads. If the number of objects in the list is less than or equal to SP_OSY_THREADS, as many threads as objects will be created to do the copy. If there are more objects in the list than SP_OSY_THREADS, the objects are portioned out to each thread using each object's rowcount to keep the workload of each thread as evenly matched as possible. The objects for each thread are put into a group list. The server process creates as many groups (and group lists) as it will create threads.
By default, the data that is read from the export pipe is compressed before it is sent to the target. Compression is performed by the zlib library. This library provides the ability to set a compression level ranging from 0 (no compression) to 9 (highest compression). The tradeoff is that the higher the compression, the longer time it will take to compress and decompress the data. The default value for SP_OSY_COMPRESSION is 6, the same as the default used by the zlib library.
The following para have explanation on setting of the above parameters:
1. The parameters SP_OSY_EXP_BUFFER_SIZE and SP_OSY_IMP_BUFFER_SIZE are another name for the BUFFER parameter. Their value is passed to BUFFER in the Export/Import parameter file. If there is adequate memory on the server, they can be set to a value which is much higher than 1024k bytes. A larger value reduced the database I/O by reducing the # of times Oracle has to go to the export file for data. A very large value can induce paging or swapping, though. If you want to set it to 4MB, you can set it as:
sp_ctrl>set param SP_OSY_EXP_BUFFER_SIZE 4096
Then run copy. The parameter will be set to this value for every copy run on the source till it is changed. The same goes for other copy parameters.
2. Currently the copy is only designed for one table and not multiple tables (say, something that will sync tables in a config file). If the table is not a partitioned one, then you may want to leave the parameter SP_OSY_THREADS untouched at its default value of 5. Otherwise you may want to increase to a higher value with one thread for every partition as a rule of thumb, of up to a maximum of 10 threads.
3. The SP_OSY_COMPRESSION is supposed to improve the throughput by compressing the data before sending it to target. There may be a drag when this data is uncompressed later on at target. It may need a trial and error approach as though the compression should aid by sending smaller byte size of data across network, it may also result in increased overhead when it is de-compressed.
Here are all the parameters associated with sync:
sp_ctrl > list param all sync
Sync parameters:
Parameter Name Actual Value Units Set At
------------------------------ -------------------- ------- ---------------
SP_OSY_COMPRESSION 6 Live
SP_OSY_DEBUG 0x00000000 bitflag Live
SP_OSY_EXP_BUFFER_SIZE 1024 kbytes Live
SP_OSY_IMP_BUFFER_SIZE 1024 kbytes Live
SP_OSY_LOCK_ALL 0 Live
SP_OSY_LOCK_TIMEOUT 2 Seconds Live
SP_OSY_PORT 2501 Live
SP_OSY_POST_TIMEOUT 1800 Seconds Live
SP_OSY_TCP_TIMEOUT 60 Seconds Live
SP_OSY_THREADS 5 Thread Live
