关于session_cached_cursors，metalink上有如下解释：

Support and Historical Notes for "SESSION_CACHED_CURSORS"

  SESSION_CACHED_CURSORS that can be set that will help in situations where a 
  user repeatedly parses the same statements.  This can occur in many 
  applications including FORMS based application if users often switch between 
  forms.  Every time a user switches to a new form. all the sql statements opened
  for the old form. will be closed.  The session_cached_cursors parameter will 
  cause closed cursors to be cached within the session so that a subsequent 
  call to parse the statement will bypass the parse phase.  This is similar to 
  HOLD_CURSORS in the precompilers.  One thing to be careful about is that if 
  this parameter is set to a high value, the amount of fragmentation in the 
  shared pool may be increased. 

测试如下：

1.查看当前数据库中此参数设置

SQL> show parameter session_cached_cursors;
 
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
session_cached_cursors               integer    0

2.以scott用户登陆数据库，执行

SQL> select count(*) from test;
 
  COUNT(*)
----------
      7943

SQL> select count(*) from dept;
 
  COUNT(*)
----------
         4

3.查看open_cursor视图

SQL> l
  1* select sid,user_name,sql_text from v$open_cursor where user_name='SCOTT'
SQL> /
 
       SID USER_NAME                      SQL_TEXT
---------- ------------------------------ ------------------------------------------------------------
        15 SCOTT                          select count(*) from dept

可见，此时scott用户只保留一个打开的游标。

查看此时的cursor 被cache情况

SQL> l
  1  select sid,n.name,s.value  used
  2  from
  3    sys.v_$statname  n,
  4    sys.v_$sesstat  s
  5  where
  6    n.name in ('opened cursors current', 'session cursor cache count') and
  7    s.statistic# = n.statistic#
  8*   and sid=15
SQL> /
 
       SID NAME                                           USED
---------- ---------------------------------------- ----------
        15 opened cursors current                            1
        15 session cursor cache count                        0

可见，此时有1个游标被打开，0个游标被cache

4.修改session_cached_cursors参数值

SQL>  alter session set session_cached_cursors=3;
 
Session altered.
 
SQL> select count(*) from test;
 
  COUNT(*)
----------
      7943
 
SQL> select count(*) from dept;
 
  COUNT(*)
----------
         4
 
SQL> select count(*) from tab;
 
  COUNT(*)
----------
         4

SQL> select count(*) from stats_tab;
 
  COUNT(*)
----------
        14
 

5.此时再次查看v$open_cursor视图

SQL> /
 
       SID USER_NAME                      SQL_TEXT
---------- ------------------------------ ------------------------------------------------------------
        15 SCOTT                          select count(*) from tab
        15 SCOTT                          select count(*) from stats_tab
        15 SCOTT                          select count(*) from dept
        15 SCOTT                          select count(*) from test

SQL> l
  1  select sid,n.name,s.value  used
  2  from
  3    sys.v_$statname  n,
  4    sys.v_$sesstat  s
  5  where
  6    n.name in ('opened cursors current', 'session cursor cache count') and
  7    s.statistic# = n.statistic#
  8*   and sid=15
SQL> /
 
       SID NAME                                           USED
---------- ---------------------------------------- ----------
        15 opened cursors current                            1
        15 session cursor cache count                        3

由此可见