The OpenEdge Index Cursor (-c) connection parameter sets the maximum number of Oracle cursors that the DataServer client session uses when you connect to an Oracle database. Specify -c after you specify the name of the Oracle database (-dbdatabase-name) in the list of parameters.

The DataServer uses cursors whenever it executes an SQL statement to access data in a table. Each Oracle cursor uses up to 4K of memory. To minimize memory consumption, the DataServer attempts to free and reuse Oracle cursors as soon as possible. It also reuses cursors that are active (not free) if there are no free cursors available. This might reduce performance, but it allows the application to continue even if there are not enough cursors. ABL uses a least-recently-used algorithm to select which active cursor to reuse.

Oracle allows you to set the maximum number of cursors in your init.ora file using the OPEN_CURSORS parameter. The valid range for numbers of cursors varies depending on the version of Oracle and system configuration.

ABL also maintains a default maximum number of Oracle open cursors as 50 for the DataServer. When you use the -c parameter to set the maximum number of cursors, you cannot exceed the number that your init.ora file specifies. For example, if the Oracle OPEN_CURSORS parameter is set to 250, then you can set the upper limit for maximum open cursors open to 250 with the -c parameter.

Determining the optimal number of cursors for your application involves balancing memory consumption, performance, and possible application failures. Use the -Dsrv qt_debug,EXTENDED parameter to log information on how many cursors your application uses. The following excerpt from the dataserv.lg file shows the cursor handler identifier within the angle brackets (<n>) that the DataServer uses for each OCI call:

OCI call OCIStmtPrepare <2>     sqlcrc = 41633
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.CUSTOMER 
          T0 WHERE PROGRESS_RECID = :rid
OCI call OCIStmtExecute-DescribeOnly <2>
OCI call omru   <2>
OCI call OCIHandleAlloc <0>
OCI call OCIStmtPrepare <3>     sqlcrc = 60664
     SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM DOCTEST.ORDER_ T0 
          WHERE PROGRESS_RECID = :rid
OCI call OCIStmtExecute-DescribeOnly <3>
OCI call omru   <3>
OCI call OCIHandleAlloc <0>
OCI call OCIStmtPrepare <4>     sqlcrc = 56980
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##COUNTRY_POST)  */ PROGRESS_RECID 
          unique_id_0,CUST_NUM,COUNTRY,NAME,ADDRESS,ADDRESS2,CITY,STATE,
          POSTAL_CODE,CONTACT,PHONE,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,
          DISCOUNT,COMMENTS,PROGRESS_RECID FROM DOCTEST.CUSTOMER T0
OCI call OCIStmtExecute <4>
OCI call OCIHandleAlloc <0>
OCI call OCIStmtPrepare <5>     sqlcrc = 48586
     SELECT /*+ INDEX_ASC(T0 ORDER_##CUST_ORDER)  */ PROGRESS_RECID un
     ique_id_0,ORDER_NUM,CUST_NUM,ORDER_DATE,SHIP_DATE,PROMISE_DATE,CA
     RRIER,INSTRUCTIONS,PO,TERMS,SALES_REP,PROGRESS_RECID FROM DOCTEST
     .ORDER_ T0 WHERE (CUST_NUM = :1)
OCI call OCIStmtExecute <5>

Avoid setting the -c parameter too low or too high:

  • Too low — A low setting can cause unnecessary recompiles of SQL, which hurts performance. Your application could also fail because it opens more queries, nested FOR EACH, or FIND statements, that reference different indexes, than the -c parameter allows.
  • Too high — A high setting can cause unnecessary consumption of resources such as memory and cursors, which can hurt performance when they are not reused. Your application can also fail when you allocate all available cursors, including a cursor that the Oracle DBMS needs for internal purposes. If this occurs, Oracle returns a recursive SQL error.

In case your application exceeds the maximum value specified for the -c parameter, you can use the close inactive cursor operation. For more information on closing inactive cursors, see RUN STORED-PROC statement with special internal option and Closing inactive cursors.