Progress
DataServer
for ORACLE Guide
Index Cursors
The Progress 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 (-db database-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. Progress 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. If yourinit.ora
file does not contain an OPEN_CURSORS parameter, then the ORACLE default is 50. The valid range for numbers of cursors varies depending on the version of ORACLE and system configuration.The Progress default maximum number of ORACLE open cursors for the DataServer is 50 also. 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 number of cursors the DataServer uses as the cc value after the open calls:
Avoid setting the -c parameter too low or too high:
- Too low — Your application fails because it opens too many queries, nests FOR EACH loops too deeply, or uses too many FIND statements that reference different indexes. The DataServer returns an error message suggesting that you set -c. A low setting can also cause unnecessary recompiles of SQL which hurts performance.
- Too high — You allocate all available cursors, including the cursor that the ORACLE DBMS needs for internal purposes. The DataServer returns a recursive SQL error. See how many cursors your application uses and set -c to a slightly lower number.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |