|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink
There
are two kinds of open cursors: implicit and explicit. Here is some
background on how cursors work:
To process a SQL statement,
Oracle opens a work area called a private SQL area.This private SQL
area stores information needed to execute a SQL statement. Cursors are
stored in this area to keep track of information. An IMPLICIT cursor
is declared for all data definition and data manipulation statements.
These are internal to Oracle. For queries that return more than one
row, you must declare an EXPLICIT cursor to retrieve all the
information. The number of open cursors can limit operation in PL/SQL
procedures and SQL*Plus sessions. While the parameter
open_cursors sets the limit, programming issues can cause the
following error:
ORA-1000 maximum open cursors exceeded
Monitoring open cursors:
The query below can be used to
find out how many cursors are open for each of the session connected
to the database:
select
sid,count(*) from v$open_cursor
group by sid
order by 2 desc;
This query
gives details on the cursors that are currently open. The details
include the usename who has the cursor open and the actual SQL
statement that's currently being kept open:
SELECT oc.user_name, st.sql_text
FROM v$open_cursor oc, v$sqltext st
WHERE oc.address = st.address
AND oc.hash_value = st.hash_value
ORDER BY oc.user_name, st.piece;
One caveat with using the above queries to
monitor the open cursors is this :
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. Please be aware
that
this
will not contribute in hitting the ORA-1000 errors. Once you reach
max_open_cursor ,these "used up" cursor in LRU list will be closed and
new requested cursor will be opened. To get an accurate count of the
number of open cursors that are open for a given session, use this
query:
select sum(a.value), b.name from v$sesstat a, v$statname b
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
group by b.name;
Diagnostic Steps for ORA-1000:
(1) Check the value of the OPEN_CURSORS parameter. This parameter determines
the number of cursors a user can open in a session.
(2) Make sure that the applications and PLSQL code close all explicit and dynamic open cursors
when it is done using the cursor.
(3) For pre-compilers, make sure the parameters RELEASE_CURSOR, HOLD_CURSOR
and MAXOPENCURSORS are set correctly at the precompiler level.
(4) If it is proving difficult to identify the reason for the ORA-1000 error then it is possible to get the
user session to generate a trace file when the error occurs by adding the following event to the
init.ora.
event="1000 trace name errorstack level 3"
This will cause a trace file to be written by any session when it hits an ORA-1000. This trace should
help identify what all cursors in the session are being used for and hence help identify the cause
of the ORA-1000.
|
