SharePlex 10.1.2 - Installation and Setup for Oracle Source

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