open cursors

Set maximum number of cursors per logon path which may be explicitly opened.

USYS$ORA_PARAMS = open cursorsNumber

USYS$ORA_PARAMS = ocNumber

Arguments

Number—maximum number of statement markers (cursors) or statements per logon path that Uniface is allowed to open. The default value is 45. The absolute minimum value is 4.

Note:  When using Oracle for the Uniface Repository, it is recommended that you allow the ORA connector to open between 150 and 250 cursors, so the OPEN_CURSORS initialization parameter should be at least 160 to 260.

Description

The maximum number of open cursors is determined by the OPEN_CURSORS initialization parameter of the Oracle server to which the application connects. However, the open cursors option should be set to value that is lower than that to provide a safety margin to accommodate extra cursors.

The default value of the open cursors connector option is 45, which is less than the default value 50 of the OPEN_CURSORS parameter in Oracle. This provides a small safety margin, but is probably insufficient.

A realistic minimum of open cursors is two per open table plus one per segmented field.

Exceeding Maximum Open Cursors

Uniface cannot guarantee that the maximum number of statement markers allowed by the Oracle server is not exceeded.

When the open cursors value in the USYS$ORA_PARAMS assignment exceeds the maximum number of statement markers allowed by the Oracle server, Oracle can generate an error, but this only happens once Uniface has actually opened the maximum number of statement markers. 

ORA-1000 Maximum open cursors exceeded

This error is most likely to occur if the Uniface statement cache has already opened its maximum number of statement markers, and a table is created on the fly. It is recommended that you do the following to avoid this problem:

  • Create tables, indexes, and packages using the SQL scripts generated by the Create Table facility instead of creating these objects on the fly.
  • Configure the Oracle server to allow some extra statement markers, in addition to the maximum number of open statement markers you allow Uniface to open. In this way, you give yourself a safety margin.

Extra cursors may be opened by:

  • Oracle. Recursive cursors may be opened by Oracle on behalf of the application. For more information, consult your Oracle documentation on recursive calls and recursive cursors.
  • Database triggers that you create.
  • User-defined 3GL that accesses Oracle on the same logon path as the ORA connector.
  • PL/SQL called in the sql ProcScript statement and in the SQL Workbench.

You can configure the maximum number of statement markers per session allowed by the Oracle server by setting the Oracle initialization parameter OPEN_CURSORS (a parameter in INIT.ORA). You must shut down and restart the server after changing its value.

The value of the open cursors connector option can be used to tune private SQL and PL/SQL areas and to reduce the number of parse calls issued by your Uniface application.

Calculating the Number of Cursors Needed

It is recommended that you begin with the following guidelines to compute an appropriate value for the open cursors connector option.

  • For every table accessed by an application on one particular logon path, add the number of cursors required, as shown in the table:
    Number of Cursors Opened when Accessing a Uniface Table

    Table Access Method

    Uniface Base Table

    Uniface Overflow Table

    Stored package

    6

    Not applicable

    Dynamic SQL (read only)

    8

    2

    Dynamic SQL (read/write)

    10

    4

    The suggested number of cursors assume that you want to improve performance and reduce client/server communication overhead, at the cost of extra private SQL areas allocated by the Oracle server. This is usually a good choice in client/server environments (especially when using SQL*Net).

  • If you are accessing Oracle locally on a platform with memory limitations, you may want to allow Uniface fewer open statement markers than suggested by these guidelines. Do not restrict the number of cursors to less than two per open table on any particular logon path. If you do specify less than this, the following error can occur: 
    Oracle connector Error[-81]:
No more cursors available for statement processing.
Increase value of 'open cursors' in USYS$ORA_PARAMS.
  • You may want to add additional statement markers, based on the knowledge of how a particular table is accessed by your applications. For example, the use of read u_where and selectdb ProcScript commands or order by clauses, and the use of various retrieve profiles can require extra statement markers.
  • Determine the total number of statement markers per logon path for your application. Take the maximum of these values and set the open cursors connector option to that value. Do this for all your applications.
  • Different applications may use different values for the open cursors option, which means that you will have to arrange for your applications to read different assignment files.
  • Ensure that the Oracle OPEN_CURSORS initialization parameter is at least equal to the largest value of open cursors set for the applications connecting to the server. As already described, allow a safety margin of some extra statement markers to avoid problems with recursive statement markers opened by the Oracle server.

For more information on tuning Oracle memory allocation, consult your Oracle documentation. It also provides information on how to trace an application and observe its behavior with respect to parse calls. It is a good idea to perform this tracing in a production environment, as the figures described in this section are only guidelines.

Calculating the Number of Cursors Needed

The following examples show how to calculate the number of statement markers required by an application. The following three tables are defined:

  • Table EMP is a table which has an associated stored package.
  • Table DEPT uses Uniface variable-length techniques (overflow table).
  • Table PROJECTS uses Uniface variable-length techniques (overflow table).

Application HIRE

Application HIRE uses two concurrent logon paths, $ORA1 and $ORA2.

  • It accesses the PROJECTS table via the $ORA1 path to database personnel2.
  • Ten statement markers are required for the PROJECTS base table, and four statement markers for the PROJECTS overflow table.
  • It accesses the EMP table via the $ORA2 path to database personnel1. Six statement markers are required for the EMP table.

The total number of statement markers required, therefore, is fourteen for the $ORA1 logon path, and six for the $ORA2 logon path. Choosing the maximum of the values, therefore, means that the open cursors connector option should be set to a value of 14.

Application FIRE

Application FIRE uses one logon path $ORA.

  • $ORA accessed the personnel1 database.
  • Six statement markers are required for the EMP table.

    Stored packages are disabled.

  • The DEPT table is accessed read only, which means that eight statement markers are required for the base table and two are required for the overflow table.

In this case, the open cursors connector option should be set to a value of 16.

The server for database personnel1 should allow at least 16 open statement markers, and the server for database personnel2 should allow at least 14 (not six statement markers as computed for logon path $ORA2, but the maximum value of open cursors for all applications connecting to it).

Note:  This calculation does not yet include the extra cursors which you should reserve for Oracle recursive cursors, database triggers, user-defined 3GL and PL/SQL called in the sql ProcScript statement or in the SQL Workbench.

Related Topics