[Uniface-L] ORACLE Driver Error [-81]: No more cursors available for statement processing.
Horst Junker
Hello
Starting with the rollout of our last application version we encountered a strange problem with Uniface which we did not have so far:
ORACLE Driver Error [-81]: No more cursors available for statement processing. Increase value of 'open cursors' in USYS$ORA_PARAMS.
Does anybody know something about the cause of this error (shows only in message log) and how to get rid of it again?
The problem leads to strange application errors since the database communication seems to be corrupted after the error occurs. Any subsequent database access afterwards fails until the application is restarted.
We already tried the most obvious and increased the open cursor value in Uniface from 400 to 1’000, checking at the same time the limit in Oracle (3'000), but with no success at all.
[DRIVER_SETTINGS]
; -- Oracle Driver options USYS$ORA_PARAMS
; oc = open cursors, af= array fetch size, ss=step size, fa=fixed array size
;USYS$ORAPARAMS = oc 400, af 20, ss 20, fa
USYS$ORAPARAMS = oc 1000, af 20, ss 20, fa
; -- U6.0 = Oracle9.2, U6.1 = Oracle10g
ORA = U6.1
We work with Uniface 8.4.02 (D205) and Oracle10g R1 (10.1.0.4.0). Uniface and Oracle have not been changed for over a year, only Application components change frequently.
Regards
Horst
Sample of message logs when the problem occured below:
Select-cache Fetch from ST_PER_E
I/O function: S, mode: 0, on file/table: ST_V_E index: 4 =
Where ((EP_NR = 50170) And (PER = 2008) And (PER_NR = 3))
ORACLE Driver Error [-81]: No more cursors available for statement processing. Increase value of 'open cursors' in USYS$ORA_PARAMS.
ORACLE Driver Error [-41]: Unable to get statement from cache.
ORACLE Driver Error [-38]: Select driver function failed.
I/O function: S, mode: 0, on file/table: FORM_DRUCK_V index: 1 >=
Where ((REP_NR = 50000100104)) Order By(SEITEN_NR,ZEILEN_NR,SPALTEN_NR)
SELECT /*+ FIRST_ROWS */ "REP_NR", "SEITEN_NR", "ZEILEN_NR", "SPALTEN_NR", "ZEILEN_TEXT" FROM "FORM_DRUCK_V" WHERE (("REP_NR" =
:"WPH1")) ORDER BY "SEITEN_NR" ASC, "ZEILEN_NR" ASC, "SPALTEN_NR" ASC
-> Hits selected: 10 .
I/O function: F, mode: 0, on file/table: PRATT index: 1 =
Where ((LAYOUT = 'PRINT_FORM'))
ORACLE Driver Error [-81]: No more cursors available for statement processing. Increase value of 'open cursors' in USYS$ORA_PARAMS.
ORACLE Driver Error [-41]: Unable to get statement from cache.
ORACLE Driver Error [-35]: Fetch driver function failed.
Select-cache Fetch from PARAM_E
I/O function: F, mode: 0, on file/table: TSF_E index: 1 =
Where ((TSF_CD = '109'))
ORACLE Driver Error [-81]: No more cursors available for statement processing. Increase value of 'open cursors' in USYS$ORA_PARAMS.
ORACLE Driver Error [-41]: Unable to get statement from cache.
ORACLE Driver Error [-35]: Fetch driver function failed.
Horst Junker
Team Spirit
IT-Solutions
Papiermühlestrasse 166, CH-3063 Ittigen, Switzerland
Tel.: +41 (0) 31 924 24 64, Fax: +41 (0) 31 924 24 79
horst.junker(at)teamspirit.ch, www.teamspirit.ch
Ulrich Merkel
whenever you start a retrieve,
the ORACLE driver passes a "select into cursor" to the database at first to get "the htlist".
Afterwards, each "fetch" of a record is done against that cursor.
If not explicitly closed, cursors are open until the next "commit" or "rollback".
So it is good practise to close transactions even after a sequence of "readonly" components.
Success, Uli
ORA.PDF says:
open cursors
Set maximum number of cursors per logon path which may be explicitly opened.
USYS$ORA_PARAMS = open cursors Number
USYS$ORA_PARAMS = oc N
No restrictions on use.
The default number of cursors per logon path which may be explicitly
opened is 45, while the absolute minimum is four. A realistic minimum
is two per open table plus one per segmented field. The maximum
number is determined by the value of the OPEN_CURSORS
initialization parameter of the Oracle server, minus some cursors to
account for recursive cursors opened by Oracle.
Some Background Info
================
Statement caching and cursor management
UNIFACE optimizes overhead use in the following ways:
The U3.x and U4.x connectors maintain a cache of the most recently
used SQL and PL/SQL statements. This reduces both the number of
parse calls and improves the client/server performance.
The connectors use the Oracle combined execute/fetch feature.
Unnecessary rebinding of placeholders is avoided wherever possible.
When stored packages are used, client/server communication is
reduced considerably. A stored package requires the client/server
overhead of only one statement, but it contains many I/O requests.
The query generated for the selectdb Proc statement is cached.
Deferred parsing is used.
You can see significant performance improvements when:
SQL*Net is used.
Your application accesses many different tables with a variety of I/O
requests.
and further down:
=============
To improve performance, it is recommended that (if your system
setup can support it) you allow the ORA connector to open between
150 and 250 cursors when accessing the repository in Oracle. The
amount you need depends on how extensively you use the features of
UNIFACE Eight. When you test forms in UNIFACE Eight, you may
want to allow extra cursors for the tables accessed by those forms.
This is only necessary if you access the test data on the same logon
path as the Repository.
> -----Ursprüngliche Nachricht-----
> Von: "Horst Junker" <horst....@teamspirit.ch>
> Gesendet: 23.02.09 19:45:12
> An: <uniface...@lists.umanitoba.ca>
> Betreff: [Uniface-L] ORACLE Driver Error [-81]: No more cursors available for statement processing.
> Hello
>
> Starting with the rollout of our last application version we
> encountered a strange problem with Uniface which we did not have so
> far:
>
> ORACLE Driver Error [-81]: No more cursors available for statement
> processing. Increase value of 'open cursors' in USYS$ORA_PARAMS.
>
> Does anybody know something about the cause of this error (shows only
> in message log) and how to get rid of it again?
>
-- Ulrich Merkel --
ulrich...@web.de
+49(69)317881
Ulrich Merkel
AFAIK, the open cursors are consumed by uniface (and its database driver).
at the very fist step I recommend to increase the "open cursors" definition in the ASN-file.
This keeps the pressure off your investogations.
Next thing is to check what code has changed in the last release.
- In these forms, check if there are frame-in-frame-in-frame situations.
- In Read-only forms like lists etc. add a "commit" at the end to close the transaction
- set the IOPRINT to 38: 32(generated SQL) + 2(Driver function calls) + 4(Return from Fetch and Select)
Perhaps you can use an SQL/PRINT to launch an ORACLE command showing open cursors:
Success, Uli
P.S. googeled "oracle"+"open cursors", found: http://www.arikaplan.com/oracle/ari60298.html
To see how many open cursors you have, type:
select * from v$sysstat where name = 'opened cursors current';
If this is over 200, then you need to change your initSID.ora file. Change the
like max_open_cursors to say 400 or 500. Then shutdown and restart your database.
Best of luck,
-Ari Kaplan
Independent Oracle DBA Consultant
> -----Ursprüngliche Nachricht-----
> Von: "Horst Junker" <horst....@teamspirit.ch>
> Gesendet: 24.02.09 11:48:05
> An: "'Ulrich Merkel'" <Ulrich...@web.de>
> CC: <uniface...@lists.umanitoba.ca>
> Betreff: AW: [uniface] Re: [Uniface-L] ORACLE Driver Error [-81]: No more cursors available for statement processing.
> Hello Uli
>
> Thanks for the fast response.
>
> Do you know where I need to look for the problematic code, is it
> Uniface coding or rather the PL/SQL in Oracle later on?
>
> We usually do all commit and rollback only in Uniface and did not
> change that behaviour (on purpose) lately.
>
> Is there any way I can monitor where how many cursors are opened and
> closed (or not closed) so I can track down the bad piece of code?
>
> Or is there an abnormal way of ending a Uniface module which would
> result in "not closed" cursors that you would know of?
>
> Prior tot his error I was not even aware that this cursor thing is
> used in Uniface at all. Obviously we introduced some weird coding in
> our last release which causes the problem. And now I need to track it
> as fast as possible. We got the error message on a daily basis from
> our users.
>
> Best regards
>
> Horst
>
> Horst Junker
>
> ________________________________
>
> Team Spirit
>
> IT-Solutions
>
> Papiermühlestrasse 166, CH-3063 Ittigen, Switzerland
>
> Tel.: +41 (0) 31 924 24 64, Fax: +41 (0) 31 924 24 79
>
> Tel. direkt: +41 (0) 31 924 24 63
>
> Mobile: +41 (0) 79 408 69 93, Skype: hjjunker
>
> horst.junker(at)teamspirit.ch, www.teamspirit.ch
>
> ________________________________
>
> -----Ursprüngliche Nachricht-----
> Von: owner-...@www.teamspirit.ch [mailto:owner-uniface@www.
> teamspirit.ch] Im Auftrag von Ulrich Merkel
> Gesendet: Dienstag, 24. Februar 2009 08:39
> An: Uniface User Group Discussion Forum
> Betreff: [uniface] Re: [Uniface-L] ORACLE Driver Error [-81]: No more
> cursors available for statement processing.
>
> _______________________________________________
>
> Uniface User Group Discussion Forum
>
> For more information: http://lists.umanitoba.ca/mailman/listinfo/
> uniface-l
>
> To unsubscribe/set options: http://lists.umanitoba.ca/mailman/options/
> uniface-l