SQM Command Cache

Use the SQM command cache to store parsed data from the Executor thread that the Distributor thread can retrieve directly, and therefore improve replication performance.

The Executor thread transfers LTL commands from a Replication Agent to Replication Server. The Executor thread parses the LTL commands and stores them in an internal parsed format. The parsed data is then packed in binary format. The Executor thread sends the binary data to the SQM thread so that the Executor thread can receive new data from the Replication Agent. The SQM thread stores the binary data in the SQM cache until the data is written to the inbound stable queue. The Distributor thread retrieves the binary data, restores the data to the original format, and determines where to send the data.

Set cmd_direct_replicate on for the Executor thread to send internal parsed data along with the binary data. Replication Server stores the parsed data in a separate SQM command cache. The parsed data in the SQM command cache maps to the binary data stored in SQM cache. When required, the Distributor module can retrieve and process data from parsed data directly, and save time otherwise spent parsing binary data.

Use the sqm_cmd_cache_size and sqm_max_cmd_in_block parameters to set the the SQM command cache memory configuration. You can configure cmd_direct_replicate, sqm_cmd_cache_size and sqm_max_cmd_in_block in the same command or separately.

Guidelines for Setting SQM Command Cache Memory Configuration

The SQM command cache memory configuration settings depend on the total amount of memory available to Replication Server, the number of inbound queues, and the transaction profile, which depends on the command size. When setting the SQM command cache memory configuration:

Increase sqm_cmd_cache_size if the Replication Server has a large total SQM cache. Total SQM cache = sqm_cache_size (in pages) * sqm_page_size (in blocks) * block_size (in kilobytes)
Decrease sqm_max_cmd_in_block if command size or table row size is large.
Increase sqm_max_cmd_in_block if block_size is large.

After you have set the initial values, tune the values based on replication performance and data from monitoring counters:

Increase sqm_cmd_cache_size if SQMNoDirectReplicateInCache shows a large value.
Increase sqm_max_cmd_in_block if SQMNoDirectReplicateInBlock shows a large value.

Use configure replication server to change the sqm_cache_size, sqm_page_size, and block_size for all database connections to Replication Server. Otherwise, use alter connection to set the configuration for a specific database connection.

See Replication Server Reference Manual > Replication Server Commands for the default value and valid range of values for the parameters.

Example 1

To set the configuration for all connections and queues for a 64-bit Replication Server:

configure replication server
set cmd_direct_replicate to 'on'
set sqm_cmd_cache_size to '40971520'
set sqm_max_cmd_in_block to '640'
go

Example 2

To set the configuration for the connection to the pdb1primary database in the TOKYO_DS data server and for inbound queue number 2 for a 32-bit Replication Server:

alter connection to TOKYO_DS.pdb1
set cmd_direct_replicate to 'on'
go
alter queue 2, 1,
set sqm_cmd_cache_size to '2048576'
set sqm_max_cmd_in_block to '640'
go