RE: OPEN_CURSORS !
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Jul 2004 17:14:39 -0400
Hi based on my few understanding a cursor is a query, not what you get from
it.
I hope not to bore you but here is something I investigate about this
parameter sometime ago and will be in my next paper, I need to check once
again.
Anything wrong someone could find tellme please
1 open_cursors parameter
6.1 What it?s for?
6.1.1 Precompilers Programs
6.1.2 Heterogeneous Services
6.1.3 Relation with session_cached_cursors
6.2 Syntax
6.3 Evaluating the accuracy of the value
6.3.1 V$OPEN_CURSOR
6.3.2 Stat: opened cursors current
6.4 Examples
6.4.1 Closing Cursors
1.1 What it?s for?
Specifies the maximum number of open cursors (handles to private SQL areas)
each session can have at once. You can use this parameter to prevent a
session from opening an excessive number of cursors. This parameter also
constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid
having to reparse as statements are reexecuted by a user.
If the limit is exceeded an ORA-01000 error is fired, and you should have to
increase this parameter?s value.
This parameter can too be used in trigger cascading, when a statement in a
trigger body causes another trigger to be fired, the triggers are said to be
cascading. Oracle allows up to 32 triggers to cascade at any one time.
However, you can effectively limit the number of trigger cascades using the
initialization parameter OPEN_CURSORS, because a cursor must be opened for
every execution of a trigger.
If your program exceeds the limit imposed by OPEN_CURSORS, Oracle gives you
an error.
Assuming that a session does not open the number of cursors specified by
OPEN_CURSORS, there is no added overhead to setting this value higher than
actually needed.
Cursors are allocated 64 at a time up to OPEN_CURSORS so having it set high
is OK. The recommended value is between 0 and 10,000 open cursor will
allocate an array in the session space (smallish). 200 would be fine for
most. Reports, Forms, etc they all use a large number of cached cursors.
500-1000 (recommend 1000).
It should be noted that OPEN_CURSORS simply allocates a fixed number of
slots but does not allocate memory for these slots for a client (eg: it sets
an array up to have 1,000 cursors for example but does not allocate 1,000
cursors).
The management of private SQL areas is the responsibility of the user
process. The allocation and deallocation of private SQL areas depends
largely on which application tool you are using, although the number of
private SQL areas that a user process can allocate is always limited by the
initialization parameter It is important to set the value of OPEN_CURSORS
high enough to prevent your application from running out of open cursors.
The number will vary from one application to another.
Applications should close unneeded cursors to conserve system memory. If a
cursor cannot be opened due to a limit on the number of cursors
To take advantage of the additional memory available for shared SQL areas,
you may also need to increase the number of cursors permitted per session.
You can increase this limit by increasing the value of the initialization
parameter OPEN_CURSORS.
Be careful where you place a recursive call. If you place it inside a cursor
FOR loop or between OPEN and CLOSE statements, another cursor is opened at
each call. As a result, your program might exceed the limit set by the
Oracle initialization parameter OPEN_CURSORS.
1.1.1 Precompilers Programs
When writing precompiler programs, increasing the number of cursors using
MAX_OPEN_CURSORS can often reduce the frequency of parsing and improve
performance.
Oracle allocates an additional cache entry if it cannot find one to reuse.
For example, if MAXOPENCURSORS=8 and all eight entries are active, a ninth
is created. If necessary, Oracle keeps allocating additional cache entries
until it runs out of memory or reaches the limit set by OPEN_CURSORS. This
dynamic allocation adds to processing overhead.
MAXOPENCURSORS specifies the initial size of the cursor cache. If a new
cursor is needed and there are no free cache entries, the server tries to
reuse an entry. Its success depends on the values of HOLD_CURSOR and
RELEASE_CURSOR and, for explicit cursors, on the status of the cursor itself
If the value of MAXOPENCURSORS is less than the number of cache entries
actually needed, the server uses the first cache entry marked as reusable.
For example, suppose an INSERT statement's cache entry E(1) is marked as
reusable, and the number of cache entries already equals MAXOPENCURSORS. If
the program executes a new statement, cache entry E(1) and its private SQL
area might be reassigned to the new statement. To reexecute the INSERT
statement, the server would have to reparse it and reassign another cache
entry.
Thus, specifying a low value for MAXOPENCURSORS saves memory but causes
potentially expensive dynamic allocations and deallocations of new cache
entries. Specifying a high value for MAXOPENCURSORS assures speedy execution
but uses more memory.
A system-wide limit of cursors for each session is set by the initialization
parameter named OPEN_CURSORS found in the parameter file (such as INIT.ORA).
1.1.2 Heterogeneous Services
HS_OPEN_CURSORS FOR Heterogeneous Services, defines the maximum number of
cursors that can be open on one connection to a non-Oracle system instance.
1.1.3 Relation with session_cached_cursors
None relation.
session_cached_cursors -- how many cached CLOSED cursors you can have.
open_cursor -- how many concurrently opened cursors you can have.
ops$tkyte@ORA920> show parameter _cursors
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
open_cursors integer 50
session_cached_cursors integer 100
that means, "you cannot have more then 50 open at the same time - but we
might cache 100 of them for you off to the side..."
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&1')||'%'
5 /
old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('cursor')||'%'
NAME VALUE
------------------------------ ----------
opened cursors cumulative 26
opened cursors current 9
session cursor cache hits 0
session cursor cache count 13
cursor authentications 1
ops$tkyte@ORA920> declare
2 type rc is ref cursor;
3
4 l_cursor rc;
5 begin
6 for i in 1 .. 100
7 loop
8 for j in 1 .. 5
9 loop
10 open l_cursor for 'select * from dual xx' || i;
11 close l_cursor;
12 end loop;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&1')||'%'
5 /
old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('cursor')||'%'
NAME VALUE
------------------------------ ----------
opened cursors cumulative 529
opened cursors current 9
session cursor cache hits 400
session cursor cache count 100
cursor authentications 1
that shows I've 100 cursors in my "cache" ready to be opened faster then
normal -- but I never exceeded my 50 open cursors at a time threshold.
1.2 Syntax
You can set this parameter with
ALTER SESSION SET OPEN_CURSORS = value
ALTER SYSTEM SET OPEN_CURSORS = value [DEFERRED]
In parameter file
set OPEN_CURSORS = (number), default value 50
1.3 Evaluating the accuracy of the value
While executing an embedded PL/SQL block, one cursor. the parent cursor, is
associated with the entire block and one cursor, the child cursor, is
associated with each SQL statement in the embedded PL/SQL block. Both parent
and child cursors count toward the OPEN_CURSORS limit.
The following calculation shows how to determine the maximum number of
cursors used. The sum of the cursors used must not exceed OPEN_CURSORS.
SQL statement cursors
PL/SQL parent cursors
PL/SQL child cursors
+ 6 cursors for overhead
--------------------------
Sum of cursors in use
The Oracle9i default of 50 or so is too small to accommodate Oracle Internet
Directory server cursor cache. Note that this value is not dependent on
other Oracle Internet Directory server parameters, such as # SERVERS and #
WORKERS. The value of 200 is sufficient for any size DIT.
1.3.1 V$OPEN_CURSOR
V$OPEN_CURSOR represents a set of cached cursors the server has for you.
1.3.2 Stat: opened cursors current
Is Total number of current open cursors
This statistics gives you the actual number of truely open cursors
--For current session
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current', for current session
--For all sessions
select a.sid, a.value, b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
order by value desc
1.4 Examples
1.4.1 Closing Cursors
1.4.1.1 Closing ref cursor explicitly
How, would I close a ref cursor, after I fetch from it.
It depends on the language.
Pro*c: EXEC SQL CLOSE :ref_cursor_variable;
SQLPlus: mplicit
PLSQL: Close ref_cursor_variable;
Java: rset.close();
and so on.
1.4.1.2 Closing cursor in PLSQL
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx> create or replace package types
2 as
3 type rc is ref cursor;
4 end;
5 /
Package created.
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx> create or replace function foo return
types.rc
2 as
3 l_cursor types.rc;
4 begin
5 open l_cursor for select * from dual;
6 return l_cursor;
7 end;
8 /
Function created.
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx> create or replace procedure bar
2 as
3 l_cursor types.rc;
4 l_rec dual%rowtype;
5 begin
6 l_cursor := foo;
7 loop
8 fetch l_cursor into l_rec;
9 exit when l_cursor%notfound;
10 dbms_output.put_line( l_rec.dummy );
11 end loop;
12 close l_cursor;
13 end;
14 /
Procedure created.
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxxxxx> exec bar
X
PL/SQL procedure successfully completed.
1.4.1.3 Closing cursor in Java
Cursors will remain there until you run out of slots in your OPEN CURSOR
array --
at which point they are flushed if not currently being used (plsql lets them
"go
away" if and when the server needs that slot)
They do not count against you, they are there for performance. It is an
EXCELLENT reason why most java programs entire suite of SQL should consist
of
nothing more then begin .... end; -- never any actual DML of its own. More
manageable, more flexible.
You can test this out yourself by using this:
create or replace package demo_pkg
as
type refcur is ref cursor;
procedure get_cur( x in out refcur );
end;
/
create or replace package body demo_pkg
as
g_first_time boolean default true;
procedure get_cur( x in out refcur )
is
l_user varchar2(1000);
begin
open x for select USER from dual THIS_IS_A_JAVA_CURSOR;
if ( g_first_time )
then
select user
into l_user
from dual THIS_IS_PLSQL where rownum = 1;
select user
into l_user
from dual THIS_TOO_IS_PLSQL where rownum = 1;
g_first_time := false;
end if;
end;
end;
/
that plsql only needs the cursors for a bit -- we don't need them everytime.
.
Now I modified the java to be:
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query =
"begin demo_pkg.get_cur( :1 ); end;";
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn=
DriverManager.getConnection
("jdbc:oracle:oci8:@ora817dev",
"scott", "tiger");
showOpenCnt( conn, "Before Anything" );
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
for( int j = 0; j < 100; j++ )
{
cstmt.execute();
showOpenCnt( conn, j + ") After prepare and execute" );
ResultSet rset = (ResultSet)cstmt.getObject(1);
for(int i = 0; rset.next(); i++ );
}
cstmt.close();
showOpenCnt( conn, "After CallableStatement closes" );
}
I don't close the result sets - we just let them leak all over the place. I
have open_cursors set to 50 and run:
> !java
java curvar
====================================
Before Anything
====================================
1 opened cursors current
-----------------------
Open Cursors Currently
SID***8 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
SID***8 ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
-----------------------
====================================
0) After prepare and execute
====================================
5 opened cursors current
-----------------------
Open Cursors Currently
SID***8 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 SELECT USER FROM DUAL THIS_IS_PLSQL WHERE ROWNUM = 1
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
SID***8 ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_TOO_IS_PLSQL WHERE ROWNUM = 1
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------
====================================
1) After prepare and execute
====================================
6 opened cursors current
-----------------------
Open Cursors Currently
SID***8 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 SELECT USER FROM DUAL THIS_IS_PLSQL WHERE ROWNUM = 1
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
SID***8 ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_TOO_IS_PLSQL WHERE ROWNUM = 1
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------
note that after each iteration I got more and more "this is a java cursor".
The plsql guys stayed in there.... UNTIL:
====================================
45) After prepare and execute
====================================
50 opened cursors current
-----------------------
Open Cursors Currently
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 SELECT USER FROM DUAL THIS_IS_PLSQL WHERE ROWNUM = 1
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_TOO_IS_PLSQL WHERE ROWNUM = 1
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
<lots of those chopped out>
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------
====================================
46) After prepare and execute
====================================
49 opened cursors current
-----------------------
Open Cursors Currently
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
<lots chopped NOTE: PLSQL cursors *gone*>
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------
====================================
47) After prepare and execute
====================================
50 opened cursors current
-----------------------
Open Cursors Currently
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
..
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at java.lang.Throwable.<init>(Compiled Code)
at java.lang.Exception.<init>(Compiled Code)
at java.sql.SQLException.<init>(Compiled Code)
at oracle.jdbc.dbaccess.DBError.throwSqlException(Compiled Code)
at oracle.jdbc.oci8.OCIDBAccess.check_error(Compiled Code)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteDescribe(Compiled Code)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(Compiled Code)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(Compiled
Code)
at oracle.jdbc.driver.OracleStatement.executeQuery(Compiled Code)
at curvar.showOpenCnt(Compiled Code)
at curvar.main(Compiled Code)
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
