Kbase 17621: ORACLE Dsrv: Setting the -c & OPEN_CURSOR value
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/1/2009 |
|
Status: Verified
GOAL:
How high should I set the -c value working with Oracle DataServer?
GOAL:
WHAT ARE CURSORS?
GOAL:
WHAT SHOULD I DO IF I GET THE ORACLE 604 ERROR?
GOAL:
WHAT SHOULD I DO IF I GET Progress ERROR 1445?
GOAL:
How high should I set the OPEN_CURSORS value within the init.ora file?
GOAL:
Progress Index Cursor (-c)
GOAL:
ORA-00604:error occurred at recursive sql level 1
GOAL:
Open cursor error, please increase -c. (1445)
FACT(s) (Environment):
All Supported Operating Systems
Oracle DataServer
FIX:
WHAT ARE 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. 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.
ORACLE allows you to set the maximum number of cursors in your init.ora file using the OPEN_CURSORS parameter. If your init.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.
HOW HIGH SHOULD I SET MY -c ?
The -c parameter should be set to the number of cursors that your Progress application needs to run. In earlier versions of Progress it was not easy to tell how many cursors your application required, however, in later versions of Progress it can be done by the -Dsrv parameters. In Progress 8.x, use -Dsrv qt_debug,273 and in Progress 9.x, use -Dsrv qt_debug,CURSOR. By using these startup parameters, you can determine how many cursors your Progress program is using, as all information is outputted to the dataserv.lg file. The highest number is approximately the number of cursors that your application is using.
For instance, the following entry would indicate that your application is using 80 cursors:
OCI call oopen <81> cc = 80
Then, you should set the -c to that number or slightly higher.
HOW HIGH SHOULD I SET OPEN_CURSORS IN INIT.ORA?
The number of cursors that you will need to run your Progress program against an ORACLE database will be the number that is required by your Progress program (explained above) plus an approximate 10 more. ORACLE needs some extra cursors for its own internal use so make sure the setting in the init.ora is at least 10 to 20 cursors higher than the value set in the -c.
In practice, set the OPEN_CURSORS value to a higher number (as the default 50 is normally not enough for most applications other than small test programs). It is also important to note that ORACLE does not necessarily give you all of the cursors that you specify with the OPEN_CURSORS parameter,they simply guarantee you will get no more than the value defined.
The ORACLE instance must be re-started after a change has been made to the init.ora file for the change to take affect.
WHAT SHOULD I DO IF I GET THE ORACLE 604 ERROR?
Error (oracle 604 error: "error occurred at recursive SQL level %s") occurs when ORACLE does not have an available cursor which it needs to process a sql statement. In this case you will need to make sure that the OPEN_CURSORS setting in the init.ora files is higher than your -c by 10 to 20. Also, you need to make sure it is set to something reasonable to begin with. See the OPEN_CURSORS section of this document for some guidelines.
WHAT SHOULD I DO IF I GET Progress ERROR 1445?
Error 1445 (Open cursor error, please increase -c. (1445)) indicates that you should increase the -c parameter to a number that will more accurately reflect how many cursors your Progress program needs to run. Make sure that it is never greater than or equal to the open_cursors setting in the init.ora file.