Re: v$open_cursor and session_cached

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 15 Oct 2001 16:22:38 -0700
Message-ID: <9qfr3u0rb3@drn.newsguy.com>

In article <ce31c410.0110151155.6a68ba26_at_posting.google.com>, crappygolucky_at_hotmail.com says...
>
>all;
>
>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 ..

even more confusing then that.

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> alter session set session_cached_cursors = 0;

Session altered.

ops$tkyte_at_ORA717DEV.US.ORACLE.COM>
ops$tkyte_at_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$tkyte_at_ORA717DEV.US.ORACLE.COM>
ops$tkyte_at_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$tkyte_at_ORA717DEV.US.ORACLE.COM>
ops$tkyte_at_ORA717DEV.US.ORACLE.COM> exec foo

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA717DEV.US.ORACLE.COM>
ops$tkyte_at_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 (tkyte@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

Received on Mon Oct 15 2001 - 18:22:38 CDT

Re: v$open_cursor and session_cached_cursors