SharePlex 9.0 - Administration Guide

Tune Capture on Exadata

The Capture process can be configured to use multiple capture threads for faster performance on an Exadata system. Capture reads directly from the logs on the Exadata ASM disks.

The SP_OCT_ASM_MULTI_OCI parameter controls the number of threads that Capture uses to read the redo logs.

The value set for this parameter must satisfy all of the following:

• greater than 1.
• an even divisor of the number of disks in the ASM disk group that stores the redo logs. For example, if there are 16 disks, the values of 2, 4, 8, and 16 are valid.
• not larger than the number of disks in the ASM disk group that stores the redo logs.

A large number of threads is not required, and performance actually diminishes with too many threads. The more threads, the more memory Capture requires.

Start with a small number of threads and monitor performance, then add threads if needed until you obtain an ideal balance between performance gain and memory usage. For example, if there are 16 disks, you could start with a value of 2.

To configure SharePlex for multi-threaded capture on Exadata

1. Run sp_ctrl.

2. Set the SP_OCT_ASM_MULTI_OCI parameter to the number of threads that you want Capture to use.

   sp_ctrl> set param SP_OCT_ASM_MULTI_OCI 3

3. Restart Capture.

Note: Capture automatically adjusts its buffer size to the value of the AU_SIZE parameter that is set for the disk group where the logs reside. This is the recommended buffer size for best performance and should not be changed. The SP_OCT_ASM_MULTI_OCI_BLOCK_SIZE parameter can override the default behavior if necessary.

Tune Checkpointing

Capture checkpoints it state to disk on a regular basis to support recovery. This information includes the log and location within that log of the most recently processes data. In a database environment where there are frequent log switches, a switch can occur before SharePlex writes its checkpoint. You can use the SP_OCT_CHECKPOINT_LOG parameter to ensure that Capture issues a checkpoint before a log switch.

The checkpoint is triggered when Capture lags a specified number of logs behind Oracle. For example, with the default of 2, Capture does a checkpoint when it falls 2 or more logs behind Oracle.

The range of permissible values for this parameter is from 2 (the default) to a value equal to the number of logs you are using. A value of 0 disables this feature.

Tune the Post process

This chapter contains instructions for improving the performance of the Post process. Because replicated data is applied through standard SQL mechanisms, the Post process provides the most potential for performance tuning.

Contents

Use INDEX hints

Valid for: Oracle targets

When SharePlex performs UPDATEs and DELETEs on a target table, Oracle sometimes does not pick the most efficient index for SharePlex. Without the right index, the Post process slows down when multiple UPDATEs and DELETEs are performed. SharePlex enables you to make use of Oracle’s INDEX hints to enforce the use of the correct index on target objects.

To use INDEX hints, use the hints.SID file, where SID is the ORACLE_SID of the target instance. When Post applies a SQL statement, it reads the hints file. If the file contains entries, Post reads the data into memory and then checks each UPDATE and DELETE statement that it processes. If any of those operations involve tables listed in the hints file, Post sends the hints to Oracle.

Use hints only for tables that need them. For example, if Post is doing full-table scans on tables where there are defined indexes, use hints only for those tables. The use of hints causes Post to read the hints.SID file for each operation on tables listed in the file. This can slow down processing if numerous tables are listed.

The default maximum number of hints (table/index pairs) is 100. You can adjust this value with the SP_OPO_HINTS_LIMIT parameter. See the SharePlex Reference Guide for more information.

Make certain all indexes are valid. Although SharePlex will use an invalid index as a hint, Oracle ignores invalid hints and returns no errors. SharePlex writes the following information to the event_Log if it detects abnormal conditions relating to the specified hints.

15050 – hint file not found

17000 – error opening hint file

15051 – missing column in the hint file (either table or index name)

15052 – syntax error for tablename

15053 – syntax error for indexname

15054 – source table’s object_id not found in object cache

15055 – more than 20 valid entries were entered into the hints file

To use the hints file

There is a blank hints.SID file in the SharePlex variable-data directory on each system. Use the hints.SID file that resides on the target system. If a hints file does not exist, create one in this directory and make certain to use the hints.SID naming format.

1. Stop Post if it is running.
2. Open the file.
3. You can add comment lines anywhere in the file. Start a comment line with a pound symbol (#).

4. On a non-commented line, use the following template to specify a source table and the index that you want to use for that table. Put at least one space between the table name and the index name. Place each specification on a separate line.

   src_owner.table

   tgt_owner.index

   Example

   scott.emp

   scott.emp_index