Index cursors

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.

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>

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 Closinginactive cursors.