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:
- 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.
-
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
Skip Large Maintenance Transactions
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:
- 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.
- 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 and Open Target (as indicated per feature)
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
Valid for Oracle, SQL Server, and PostgreSQL targets
The Transaction Concurrency feature configures a Post process to apply transactions in parallel to increase overall throughput. To use this feature, supplemental logging for primary and unique keys must be enabled on the source.
To enable Transaction Concurrency:
- For an Oracle target database, set the SP_OPO_DEPENDENCY_CHECK parameter to 1.
- For SQL Server and PostgreSQL, set the SP_OPX_THREADS parameter to 2 or greater.
Note: The use of Transaction Concurrency may reduce or eliminate the need to run multiple Post processes, but you can still benefit from that configuration because it eliminates a single point of failure. If a Post process fails, the other Post processes can continue, resulting in less recovery time, after the problem is resolved. The Transaction Concurrency feature can be used in a multi-Post configuration, so long as the rules for using multiple Post 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
Valid for Oracle and all Open Target databases
The Commit Reduction feature of Post combines batches of small transactions into larger ones. One large transaction runs faster than multiple smaller ones by having fewer commits and acknowledgments to process.
Post skips the commits of small transactions until their combined size reaches the threshold specified by one of the following parameters:
- SP_OPO_COMMIT_REDUCE_MSGS (Oracle targets)
- SP_OPX_COMMIT_REDUCE_MSGS (Open Target)
The default batch transaction size is 100 messages. This value is an approximation. If the size of the last transaction in the batch exceeds the specified threshold, SharePlex waits for the remaining messages and the commit before applying the batch transaction to the target.
Commit reduction is enabled by default. To disable commit reduction, set this parameter to a value of 1.
Split a large transaction into a smaller one
Valid for: Currently supported for JMS
You can configure Post to split a large transaction into a series of smaller ones. This option can work around resource limits that affect large transactions, such as the number of row locks permitted per transaction.
To split a large transaction into smaller ones:
Use the target command to set the commit_frequency parameter.
target r.database [queue queuename] set resources commit_frequency=number_of_operations
This parameter specifies a maximum number of operations after which Post issues a commit. It can be any integer greater than 1.
Example:
target r.mydb queue q1 set resources commit_frequency=10000
