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.
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.
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.
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 |
|
scott.emp |
scott.emp_index |
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.
All
Control SQL Cache as follows:
| 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. |
| 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. |
Follow these steps to make certain that the number of active statements is optimal for the operations that are replicated.
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
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
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Términos de uso Privacidad Cookie Preference Center