What is the difference between THREADS and PARALLELISM in compare or repair? (4298751)

What is the difference between THREADS and PARALLELISM in compare or repair?

What is the difference between THREADS and PARALLELISM in compare or repair?  Both these terms refer to some sort of multitasking and this solution attempts to delve on the subtle difference between the two.

General information.

The PARALLELISM option/parameter is used to provide parallel hints in the SELECTs in Oracle.  When a compare or repair is started, the compare process selects certain # of rows (based on the setting of the compare parameter SP_DEQ_MALLOC) from the source and target database respectively. It sorts those rows in the TEMP segment of the database, among others. The PARALLELISM has been there in Oracle since long and it is only recently that the Compare command allows it to be specified. This greatly helps in speeding up part of the compare operations. One point to note is that the Oracle Optimizer can still ignore such parallel hint provided to it by the Compare process via this parameter. The reasons for such behavior are beyond the scope of this solution.

The THREADS is meant to do multitasking in compare process by way of opening up multiple threads on the source compare process (sp_desvr process). For each thread used on source, there is a corresponding sp_declt process spawned on target. If there is only one table to be compared, then the ‘threads’ default to 1 even though the user may have provided a higher value.

It should be noted that these options can be provided as a command line option in compare/repair command or can be set as a parameter in compare/repair.

If too many out of sync are involved, the “nosqllog” option of compare/repair can be used to improve the performance of compare/repair. See the Knowledgebase article 228415 for details.

Here is the description of the parameters SP_DEQ_THREADS and SP_DEQ_PARALLELISM which correspond to the compare options 'threads' and 'parallelism' respectively as taken from SharePlex Reference Guide: SP_DEQ_THREADS This parameter controls the number of processing threads used by the compare command on the source system. Each thread creates a sp_declt process on the target machine. The default of 2 threads has proven to be the best number for UP machines, but you might obtain performance improvements with up to 15 threads. Too many threads causes diminishing benefits. The value for the thread count is independent of the number of tables to be compared, and SharePlex will not generate more threads than there are tables to be compared. If the machine has only one processor, set this parameter to a value of 1. Set this parameter before you issue the compare config command. Default: 2 threads Range of valid values: 1 to 15 Takes effect: immediately available for the next comparison SP_DEQ_PARALLELISM This parameter manages the select statement Degree of Parallelism hint. When SP_DEQ_PARRALLISM is set to zero, no parallel hint will be used. A user-provided hint from the command line will have higher precedence. Default: 2 Range of valid values: 0 to 500 Takes effect: immediately available for the next comparison. For compare/repair command options, please refer to Shareplex Reference Guide.