Re: Max Open Cursors

From: George Fields <WalterFields_at_jhuapl.edu>
Date: Wed, 09 Apr 2003 08:50:02 -0400
Message-ID: <3E9416FA.6ED92B6A@jhuapl.edu>

Jim, Kanv:
  Thanks for your help. Here is a copy of the TAR from Ron Yount that had the solution to my problem. I hope you find it as interesting as I did.

<snip>
TAR Number 1921166.995 Open Date 19-JAN-02 08:37:03 Support Identifier 3023620 Name Ron Yount Priority 3 Last Update 05-FEB-02 07:34:51 Product Oracle Server - Enterprise Edition Product Version 8.1.7.2.0 Platform AIX-Based Systems (64-bit) Detailed Status Hard Close TAR Reference n/a BUG Reference n/a

Abstract

Unable to determine actual Open Cursors - how do I set max_cursors?

Resolution History

19-JAN-02 08:37:03 GMT Can you easily recover from, bypass or work around the problem? = YES Does your system or application continue normally after the problem occurs? =
NO
Are the standard features of the system or application still available; is the
loss of service minor? = NO

#### Remote Diagnostic Agent ####

Unknown

### Platform and O/S version, including patchset or service pack level? ###
IBM AIX RS/6000 4.3.3
### What version and patchset level of the database are you running? ###
8.1.7.2.0

### Please describe your problem: ###

max_cursors exceeded, cannot determine how to set this "safely".

### If you are receiving errors, please list exact error messages and text:
###

max cursor limit exceeded

### Did the error generate a trace file? ###
Does not apply

### Please list all files that you plan to upload: ###
n/a

### What was being done at time of error? Any changes since this last
worked? ##
We are using connection pooling via WebLogic web servers and JDBC (thin) right
now, but will be going to (oci-thick) to support TAF in future versions. I have read notes regarding inability to distinquish the difference between open
cursors that are in use, and open cursors that have been marked as "cancelled"
by oracle post a client/app request to close it. I am having trouble now in my
production database, with knowing how to set max_cursors?

### Can error be generated if SQL is run in SQL*PLUS or Server Manager? ###
Does not apply

### What is the frequency of the error? ###
Intermittently

### What is the impact to your business because of this problem? ###
This impacts a production database and the weblogic (middle tier) interfaces.

Contact me via : E-mail -> ryount_at_erac.com

19-JAN-02 08:42:02 GMT New info : Here is an exert from a previous discussion on this topic: From: Oracle, Helen Schoone 17-Jul-01 17:52 Subject: Re : number of open cursours
Hi. The statistic 'opened cursors current' displays the total number of opened
cursors for the session, although not necessarily what is currently 'open.' The view v$open_cursors will provide a better representation of the number of
cursors which are currently open for a session. Note however, that there have
been some issues with inaccurate statistics when Parallel Query is in use. If you are not using any parallelism, the following query should reasonably provide the currently 'open' cursors for a particular session: select a.sql_text
from v$session s, v$open_cursor o, v$sqlarea a where

s.saddr=o.saddr and
s.sid=o.sid and
o.address=a.address and
o.hash_value=a.hash_value and
s.schemaname='<the name of the schema>';

However, a row in v$open_cursor does not necessarily mean that the cursor is

open and *in use*. For performance reasons, cursors are not "closed", per se.
In PL/SQL V2, they are "cancelled" which should have the effect of releasing

most system resources but are still "open" and re-used as available. There is
currently no view which will provide the information as to how many cursors are
really open and in-use versus how many are open and "cancelled." So, if the above statement is accurate, that leaves me with a burning question:
When (if ever) will a "cancelled" cursor truly be closed? If never, then how

do I configure my database to support a middle tier application that maintains
sessions (which are shared by several application processes) for long periods
of time. e.g. Since the session does not disconnect, when will the "cancelled"
cursors be cleared out to prevent the session from exceeding "max cursor limit"?

19-JAN-02 14:24:57 GMT Explanation

---

The cursors listed in V$OPEN_CURSORS are, in fact, still open. This is a performance optimization method used by the server. Even though you have CLOSED the cursors, they are still cached in Server memory.

This is not a bug. The code leaves cursors open and caches them, as intended. The CLOSE_CACHED_OPEN_CURSORS parameter in init.ora forces all of these cached cursors to be closed at the end of each transaction, if that is desired. This parameter is available in Oracle 7 & 8 and absoleted in 8i

This parameter controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. If PL/SQL cursors are reused frequently, setting the parameter to FALSE can cause subsequent executions to be faster.

A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK. The cursor can then be reopened as needed. If cursors are rarely reused, setting the parameter to TRUE frees memory used by the cursor when the cursor is no longer in use.

The following is a technical explanation provided by Oracle Development:

The server caches cursors opened by the PL/SQL engine. Each time a cursor is closed, it is really moved to an Least Recently Used (LRU) list of open cursors and left open. This is done as a performance improvement by saving a 'round_trip' from Client to Server each time a cursor is opened.

Note that no optimization occurs. In other words, the cursor caching code does not check to see if a particular query already has a cursor open; it simply creates another one. This is done until OPEN_CURSORS is reached. If you have OPEN_CURSORS cached as open, however, then if an identical query occurs, the server reuses the cached cursor.

Action Plan

---

please try to set the following parameter in your init.ora file and bounce the DB and see if this will fix the problem

_CLOSE_CACHED_OPEN_CURSORS=true

and see if this will keep the cursors opened or not

thanks

19-JAN-02 14:47:51 GMT Email Update button has been pressed -- Sending email.

21-JAN-02 17:04:03 GMT New info : Before implementing any changes, I would like to clarify my understanding of two points:
1) CLOSE_CACHED_OPEN_CURSORS (init parm) is obsolete in 8i ,but _CLOSE_CACHED_OPEN_CURSORS (undocumented init parm) DOES work in 8i Is this correct?
2) Having "unused" open cursors has no potential for performance benefit until
"open_cursors" e.g. max_cursors is reached... then, if there is a reusable cursor in the cursor cache, it will be used, but if not, then you will get the
oracle error for max_cursors exceeded? Is this correct as stated? thanks,
-Ron-

21-JAN-02 17:13:12 GMT Ron,

You are correct in both points.

21-JAN-02 18:23:16 GMT New info : Thanks! You may now hard close this tar. In order to have proactive control of resources and an accurate on-line assesment of open cursors, we will implement the _CLOSE_CACHED_OPEN_CURSOR parameter. -Ron-

21-JAN-02 19:16:11 GMT PAA Summary

---

Customer was asking about why cursor was kept open in the v$open_cursor view even after the session exit or we commit the transaction . told him that this is expected behavior and should boost the performance .

to turn of this feature you can use _CLOSE_CACHED_OPEN_CURSORS=true parameter

thanks

05-FEB-02 07:34:51 GMT TAR passed SCL review date. Setting to HCL.

</snip>

Kanv wrote:

> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.com> wrote in message news:<VPJja.338660$L1.96043_at_sccrnsc02>...
> > George,
> > If you are using PL/SQL then fine with implicit cursors.  They will get
> > closed upon exit of the procedure or function.  If you are using an api (eg
> > oci, odbc, ado, jdbc, etc.) then you need to either:
> > 1. Open a cursor once and reuse it for the life of an application. (using
> > bind variables this is a very efficient method; you rebind and reexecute
> > when your bind variable values change)
> > or
> > 2. Close the cursor when you are done.
> >
> >
> > You probably have a leak in your application - opening a cursor again and
> > again, whereas if you opened it once and rebound the bind variables you
> > would be all set.  If you aren't using bind varibles then you are
> > signifigantly hurting your performance and scalability.(and need to close
> > the cursor every time)
> >
> > I would get off 8.05 asap and go to at least 8.1.7.4 or later.
> >
> > Jim
>
> Would vote on what Jim says on the leak in your app. Nine out of ten
> times have seen that to be the cause of the open cursors exceeded
> available. Any particular reason why you wouldnt increase the 900
> value?

Received on Wed Apr 09 2003 - 07:50:02 CDT