Chat now with support
Chat with Support

Welcome, ApexSQL customers to Quest Support Portal click here for for frequently asked questions regarding servicing your supported assets.

SharePlex 9.0 - Administration Guide

About this Guide Conventions used in this guide Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure SharePlex to replicate data Configure replication to and from a container database Configure named queues Configure partitioned replication Configure SharePlex to maintain a change history target Configure a replication strategy Configure SharePlex to replicate Oracle DDL Set up error handling Transform data Configure SharePlex security features Activate replication in your production environment Monitor SharePlex Prevent and solve replication problems Repair out-of-sync Data Procedures to maintain Oracle high availability Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Tune the Capture process Tune the Post process Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

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.

Adjust open cursors

Valid for: Oracle targets

 

The value of the Oracle parameter OPEN_CURSORS needs to be set high enough to support the level of performance expected of the Post process. This parameter defines the maximum number of cursors that a process (such as Post) can open.

Internally, Post establishes its maximum total number of open cursors from the value of OPEN_CURSORS, minus the 10 required for routine calls. You view this value in the event_log. For the following example, OPEN_CURSORS is set to 512.

Notice: sp_opst_mt (for o.oracle-o.oracle queue oracle) Post will not open more than 502 cursors (OPEN_CURSORS – 10).

Post maintains a record of the number of cursors it has open. If Post detects that it will exceed the maximum number of cursors, it closes the least-recently used cursor in the least-recently used session.

To avoid running out of cursors, the Post process queries the OPEN_CURSORS value when it starts. If the value is not high enough, Post writes the following warning to the event_log:

Warning: (sp_opst_mt for o.oracle-o.oracle queue oracle)Oracle parameter 'OPEN_CURSORS' is < number. Check 'OPEN_CURSORS' setting.

The OPEN_CURSORS value can be modified or added if absent.

To view the OPEN_CURSORS value, query the database using the following SQL statement:

select value from v$parameter where name = 'open_cursors';

To estimate a value for OPEN_CURSORS that is high enough for the Post process

  1. Estimate the peak number of concurrent transactions (sessions) that will be expected for the target instance. Post opens a session on the target system for each one on the source system. You can get a good estimate of the number of transactions by issuing the show post detail command in sp_ctrl when production is at its maximum level. The Number of Open Transactions field in the display shows the number of concurrent transactions.
  2. Use the following formulas to determine the correct setting for OPEN_CURSORS to support SharePlex (and other applications that may be accessing the target data).

    SQL Cache enabled (default): By default, Post needs to reserve 10 cursors for routine calls that are closed once they finish, plus a minimum of 7 cursors per transaction (the base minimum of 2 plus an additional 5). The formula is:

    10 + (peak number of concurrent transactions x 7) = minimum open cursors needed

    SQL Cache disabled: The Post process needs to reserve 10 cursors for routine calls that are closed once they finish, plus a minimum of 2 cursors per transaction. The formula is:

    10 + (peak number of concurrent transactions x 2) = minimum open cursors needed

Skip maintenance DML

Valid for: Oracle targets

 

Large transactions that are applied by application patches or other internal Oracle operations can be omitted from replication if they are not relevant to the data needed by user applications. These operations can translate into thousands or millions of individual UPDATE or DELETE statements for SharePlex, all to be applied by Post. Such transactions can adversely affect Post performance and increase the latency between the source and target data that user applications need to perform their work. There may be reasons to prevent other DML operations from being posted to a target database.

There are two ways you can handle such transactions:

  • Assuming there are no referential relationships between those operations and the user data, configure those operations to process through a dedicated named post queue. For more information, see Configure named post queues.
  • Configure Post to skip the operations, and then apply the SQL statement directly through Oracle. See the following instructions.

To skip maintenance DML

  1. On the source system, run the create_ignore.sql script from the util sub-directory in the SharePlex product directory. This script creates the SHAREPLEX_IGNORE_TRANS public procedure in the database. When executed at the start of the transaction, the procedure directs the Capture process to ignore the DML operations that occur until the transaction is committed or rolled back. Thus, the affected operations are not replicated. For more information about the script, its limitations, and how to run it, see create_ignore.sql in the SharePlex Reference Guide.
  2. Edit your patch script to call SHAREPLEX_IGNORE_TRANS before UPDATE or DELETE operations. This allows SharePlex to ignore the transaction and not send it to the target. The script will also have to be run on the target to bring the database back into sync.

Note: Only DML operations are affected by the SHAREPLEX_IGNORE_TRANS procedure. It does not cause SharePlex to skip DDL operations, including TRUNCATE. DDL operations are implicitly committed by Oracle, so they render the procedure invalid.

Make small transactions faster

Valid for: Oracle

 

You can improve the speed of Post when it is processing mostly small transactions, such as those most commonly found in OLTP.

There are two features you can use, depending on the supported database:

  • Increase the level of concurrency
  • Reduce the number of commits

Together these features are called Post Enhanced Performance, or PEP.

Increase the level of concurrency

The Transaction Concurrency feature enables you to configure Post to apply transactions in parallel to increase overall throughput. The smaller the transaction, the bigger the performance gain.

To enable the concurrency feature, set the SP_OPO_DEPENDENCY_CHECK parameter to 1.

Note: This feature may reduce or eliminate the need to run multiple Post processes, but you can still benefit from the use of multiple Post processes because they eliminate a single point of failure. If a Post process fails, the other Post processes will continue, resulting in less recovery time, after the problem is resolved. This feature can be used in a multi-Post configuration, so long as the rules for using multiple processes are followed (such as including all tables with referential integrity in the same process stream). For more information, see Configure named post queues.

Reduce the number of commits

The Commit Reduction feature enables you to configure Post to combine smaller transactions into larger ones. This reduces the number of commits and acknowledgments that must be processed. The smaller the transaction, the bigger the performance gain.

Commit reduction is enabled by default when you enable the concurrency feature, and it cannot be enabled without enabling concurrency. The size of the combined transaction is controlled by the SP_OPO_COMMIT_REDUCE_MSGS parameter.

This parameter sets the minimum number messages (operations) in the transaction. Post skips the commits of small transactions whose boundaries are within the specified range, and instead applies all of the operations in the combined transaction. The default combined transaction size is 100 messages. To disable commit reduction, set this parameter to a value of 1.

The parameter setting is not an absolute threshold. SharePlex will not break up a transaction across different combined transactions. Therefore, Post may need to exceed that threshold in order to include all of the operations and the commit of the last transaction in the group.

With both concurrency and commit reduction enabled, testing has shown that performance can be increased by as much as two or three times over conventional SharePlex posting speeds.

Related Documents