Chat now with support
Chat with Support

SharePlex 11.2 - Administration Guide

About this Guide Conventions used in this guide Revision History Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure data replication Configure replication to and from a container database Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Assign SharePlex users to security groups Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Recover replication after Oracle failover Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Add a second thread

You can set the SP_OCT_OLOG_RDS_MINER parameter to 1 to add a second thread to Capture. This thread can be used to address performance issues when Capture is lagging behind Oracle on a very busy system.

Due to the processing load incurred by using this thread, it is disabled by default. To enable it, set this parameter to 1.

Note: Enabling the SP_OCT_OLOG_RDS_MINER parameter is deprecated and no longer supported starting with Oracle 19c.

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

Use Oracle 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"

Tune SQL Caching

SharePlex caches frequently-used SQL statements for reuse so that they do not have to be parsed and bound every time they recur. This is an adjustable feature of SharePlex that is named SQL Cache. You can tune this feature to maximize its advantages based on the amount of repetitive statements your application generates.

SQL Cache improves the performance of Post only if the same SQL statements are issued over and over again, with no variation except the data values. If that is not true of your environment, then SQL Cache adds unnecessary overhead to the Post process, and you should disable it.

Supported targets

All

Enable or disable SQL Cache

Control SQL Cache as follows:

Oracle
Parameter Description

SP_OPO_SQL_CACHE_DISABLE

Enables or disables SQL Cache. Enabled by default with a setting of 0. To disable SQL Cache set the parameter to 1. To disable SQL Cache only for batch operations set the parameter to 3, which reduces the amount of memory that Post uses.

SP_OPO_MAX_CDA

 Determines the number of active statements to cache per Post session. Post opens 50 cursors per session by default. You can increase or decrease this setting if needed. For more information, see Adjust Open Cursors.
Open Target
Parameter Description

SP_OPX_SQL_CACHE_DISABLE

Enables or disables SQL Cache. Enabled by default with a setting of 0. To disable SQL Cache set the parameter to 1.

Use the target command:

target r.database [queue queuename] set resources max_active_statements=number_of_active_statements

Determines the number of active statements to cache per Post session. For Open Target databases, Post gets the number of allowed active statements from the ODBC driver. If that value is lower than the setting for max_active_statements, Post stops and returns an error. You can either disable the SQL Cache feature or reduce the value of max_active_statements.

Tune SQL Cache for best performance

Follow these steps to make certain that the number of active statements is optimal for the operations that are replicated.

  1. Determine the hit ratio for cached statements by running sp_ctrl and issuing the show post detail command.
  2. Look for the SQL cache hit count field. It shows the ratio of the total number of messages that are executed without parsing and binding divided by the total number of INSERT, UPDATE and DELETE operations. For example, a hit ratio of 36% indicates that Post is using cached statements 36 percent of the time.
  3. View the hit ratio after several days of typical replication activity to gauge the ideal setting for the number of active statements. If the hit ratio is under 50 percent, increase the parameter value in a small increment of about 5 statements.
  4. Monitor the hit ratio over the next few days. If the hit ratio increases, it means your applications are using all of the cursors allowed for active statements. Continue to increase the parameter value in small increments until the hit ratio remains constant.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating