v$open_cursor and session_cached_cursors
crappy
i've read a number of postings, documentation and such on the subject,
but something is still ocnfusing me. please forgive if this is a dumb
question.
oracle 8.1.7: my understanding is that one is supposed to use
session_cached_cursors to control the number of cursors that get
cached by the plsql engine. however, it doesn't seem to be working
the way i'm expecting it to. if you open/fetch/close a cursor, with
session_cached_cursors set to 0, shouldn't the cursor *not* show up in
v$open_cursor (resulting in a hard + soft parse for each execution)?
i'm trying to differentiate which sql_text's in v$open_cursor
represent cached cursors as opposed to cursors that are actually being
left open by the (java) application, and i thought that i would be
able to essentially turn off the cursor caching. what am i missing?
thanks in advance ..
Thomas Kyte
crappy...@hotmail.com says...
even more confusing then that.
ops$tk...@ORA717DEV.US.ORACLE.COM> alter session set session_cached_cursors = 0;
Session altered.
ops$tk...@ORA717DEV.US.ORACLE.COM>
ops$tk...@ORA717DEV.US.ORACLE.COM> select sql_text from v$open_cursor where
user_name = user;
SQL_TEXT
------------------------------------------------------------
SELECT * FROM DUAL
select sql_text from v$open_cursor where user_name = user
ops$tk...@ORA717DEV.US.ORACLE.COM>
ops$tk...@ORA717DEV.US.ORACLE.COM> create or replace procedure foo
2 as
3 begin
4 for x in ( select * from dual )
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
ops$tk...@ORA717DEV.US.ORACLE.COM>
ops$tk...@ORA717DEV.US.ORACLE.COM> exec foo
PL/SQL procedure successfully completed.
ops$tk...@ORA717DEV.US.ORACLE.COM>
ops$tk...@ORA717DEV.US.ORACLE.COM> select sql_text from v$open_cursor where
user_name = user;
SQL_TEXT
------------------------------------------------------------
SELECT * FROM DUAL
select sql_text from v$open_cursor where user_name = user
PLSQL maintains a cache itself and will automatically flush this cache out if
you get to the point where open_cursors would be exceeded.
The session_cached_cursors exposes the logic PLSQL always uses to other
environments that do not support it or have not made use of it. You can use
this to get the same sort of performance tuning that PLSQL provides out of the
box with you VB application for example.
It does not affect the native behavior of PLSQL (or pro*c with hold_cursor=yes
and so on).
To see some more info on this, including how to measure the effectiveness of
this setting search for
session_cached_cursors
on my site http://asktom.oracle.com/ I have a couple of articles there on this.
--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Ricky Sanchez
session_cached_cursors is not intended to be normally used. That is, it
serves as a sort of stop-gap when an application cannot be modified to
maintain full control over cursors. It is not implemented by the pl/sql
engine. It is a cache of recently used cursors for each session, and is
constructed from the UGA. The UGA is, in a dedicated server environment,
part of the PGA. It is specific to each session.
The idea is that if your application closes cursors, either implicitly
or explicitly, you can set a non-zero value for session_cached_cursors
and after a few executions a cursor will be placed in that cache and
will not be closed. When you use the feature, cursors are not
immediately placed in the cache. That is, you have to execute them two
or three times - I forgot the exact number - before they become eligible
to be cached. If the cursor is not shareable, it will be executed only
once and won't be cached.
Every time you go to parse a sql statment, your code looks through its
cursor cache to see if that statement is present in the cache. The cache
is an array and has to be scanned in a linear fashion. So, a big cursor
cache can cost you more than it might save you. Most shops I have
encountered who find session_cached_cursors helpful have a setting of
around 50 or less. Never more than 100 in any case.
If you don't have the cache enabled, or have it set to zero, you don't
necessarly hard parse each cursor. The normal operation is to derive the
hash value from a sql statement, use that value to look up the cursor in
the library cache (soft parse) and lock it / execute it if it is found,
otherwise construct a new cursor (hard parse). Soft parsing will happen
if a cursor is closed but otherwise still in the library cache in a
useable form. So, shareable sql will see lots of hard parses but
probably few or no hard parses, except for perhaps reloads when older
cursors get flushed out because of memory demands.
The best approach is to modify your application to keep cursors open
that will be used again. That way you avoid even the soft parses. Soft
parses, while much cheaper than hard parses, can still get expensive.
v$open_cursor shows cursors for each session. I am not sure what you are
seeing there. Regardless of the cursor cache setting, if the cursor is
open by a session it will show up in the view. Moreover, you may have
the cursor open by a session other than your own and it will still be in
the library cache, although the v$open_cursor view will indicate the
session id of that session.
- ricky
crappy
session_cached_cursors bit now. however, i'm still wondering about
the problem that got me on this track in the first place, and that is,
trying to differentiate what cursors are properly closed but cached,
and which were inadvertently left open by the java app.
from docs and articles at asktom, i understand that v$open_cursor
shows sql_text's that aren't necessarily open cursors, just ones that
have been opened at some point: so if i flush the shared pool the ones
that had been closed explicitly do go away.
e.g., i have the same kind of simple procedure (much like Thomas',
does an open/fetch/close of a "select * from dual" cursor), and after
executing it i get
SQL> select sql_text from v$open_cursor;
SQL_TEXT
------------------------------------------------------------
SELECT * FROM DUAL
select sql_text from v$open_cursor
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text from v$open_cursor;
SQL_TEXT
------------------------------------------------------------
select sql_text from v$open_cursor
... which i expected. then, i thought that using v$mystat.statistic#
(= 3) would show the "real" number of cursors that are truly opened,
but it showed a count of 1 before the execution of the proc, and then
2 afterwards, and also after the flush. why is that? is there some
other cursor popping up? tia ..