Chat now with support
Chat with Support

SharePlex 11.4 - Installation and Setup guide

About this Guide Conventions used in this guide Revision History Installing and Setting up SharePlex on an Oracle Source
SharePlex Pre-installation Checklist for Oracle Download the SharePlex installer Install SharePlex on Linux and UNIX Set up an Oracle environment for replication Set up replication from Oracle to a different target type Installation and Setup for Cloud-Hosted Databases for Oracle Installation and setup for remote capture Installation and setup for HA cluster Generic SharePlex demonstration for Oracle Advanced SharePlex demonstrations for Oracle Database Setup Utilities Solve Installation Problems for Oracle
Installing and Setting up SharePlex on a PostgreSQL Database as Source and Service
SharePlex Pre-installation Checklist for PostgreSQL Download the SharePlex installer for PostgreSQL Install SharePlex on Linux for PostgreSQL as a Source Set up Replication from PostgreSQL to Supported Target Types Installation and Setup for Cloud-Hosted Databases for PostgreSQL Installation and Setup for Remote Capture for PostgreSQL Install SharePlex on PostgreSQL High Availability Cluster Configure SharePlex on PostgreSQL Azure Flexible Server with High Availability Using Logical Replication Generic SharePlex Demonstration for PostgreSQL Advanced SharePlex Demonstrations for PostgreSQL Database Setup for PostgreSQL Database Setup for PGDB as a Service Installation of pg_hint_plan extension Solve Installation Problems for PostgreSQL
Installing SharePlex on a Docker container Assign SharePlex users to security groups Solve Installation Problems Uninstall SharePlex Advanced installer options Install SharePlex as root SharePlex installed items

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