| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: V$OPEN_CURSORS in pl/sql
On Tue, 15 Jul 1997 18:40:30 GMT, "Andrew Smalera" <andrew.smalera_at_cplc.com> wrote:
>Hi, I have statements like these in some pl/sql code I wrote:
>
>SELECT USER INTO v_UserID FROM DUAL;
>
>They're part of a short stored procedure that runs when the user logs in.
>After a number of users log in, the server slows down, people can't log in
>any more, and server resources get used up. Checking the V$OPEN_CURSORS, I
>see that there are tons of open cursors related to the above group of
>statements still open. The statements are all just SQL 'select into' type
>commands. Why do they leave cursors open, and more importantly, how do I
>make sure they get closed?? I've never seen anything like this documented,
>and to the contrary, I've seen many examples where a select into is done in
>a block and there is no type of statement that tries to close the cursor.
>I would really appreciate any suggestions asap. Thanks.
>
>
PL/SQL maintains a cache of cursors and keeps them open for you in the hopes you reuse lots of them. In many applications, the cost of reopening and parsing a query is more then actually running the queries.
In order for PL/SQL to physically close all open cursors on every commit and rollback you can use the init.ora parameter:
<quote>
CLOSE_CACHED_OPEN_CURSORS
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. </quote>
You can also look at cursor_space_for_time and open_cursors init.ora settings...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |