SESSION_CACHED_CURSORS: If an application repeatedly issues parse calls on the same set of SQL statements, then the reopening of the session cursors can affect system performance. To minimize the impact on performance, session cursors can be stored in a session cursor cache. These cursors are those that have been closed by the application and can be reused.

Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.

Below is test case by using java program:

1. SESSION_CACHED_CURSORS =100
<!--java code -->
   sql = "alter session set SESSION_CACHED_CURSORS =100";
   ps = aconnection.prepareStatement(sql);
   ps.executeQuery();

      sql = "SELECT COUNT(*) FROM TT";
for (int i=0;i<30;i++ )
{
   ps = aconnection.prepareStatement(sql);      --repeatedly issue parse calls
   rs = ps.executeQuery();
   Thread.sleep(1000,0);
   ps.close();                                  --close statement(cursor)
}
<!--java code -->

--get open cursor, and only one sql statement
select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
---------------------------------------------------------------------------------------------------------------
3432EB34 145 SYS 27FA93D0 560338920 294ugjshqc5z8 SELECT COUNT(*) FROM TT

--parse count (total) is 33, and session cursor cache hits is 29
        RUN DATE                SID     NAME                                  VALUE
-----------------------------------------------------------------------------------------
before 2008-07-20 22:45:16 145 STAT...session cursor cache hits 2
before 2008-07-20 22:45:16 145 STAT...parse count (total)         29
before 2008-07-20 22:45:16 145 STAT...parse count (hard)         3
after 1 2008-07-20 22:45:46 145 STAT...session cursor cache hits 32
after 1 2008-07-20 22:45:46 145 STAT...parse count (total)         62
after 1 2008-07-20 22:45:46 145 STAT...parse count (hard)         4

2. SESSION_CACHED_CURSORS =0

select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
--------------------------------------------------------------------------------------------------------------
no rows

--parse count (total) is 38, and session cursor cache hits is 0
        RUN DATE                SID     NAME                                  VALUE
-----------------------------------------------------------------------------------------
before 2008-07-20 22:54:06 145 STAT...session cursor cache hits 754
before 2008-07-20 22:54:06 145 STAT...parse count (total)         350
before 2008-07-20 22:54:06 145 STAT...parse count (hard)         99
after 1 2008-07-20 22:54:37 145 STAT...session cursor cache hits 754
after 1 2008-07-20 22:54:37 145 STAT...parse count (total)         388
after 1 2008-07-20 22:54:37 145 STAT...parse count (hard)         102

: If an application repeatedly issues parse calls on the same set of SQL statements, then the reopening of the session cursors can affect system performance. To minimize the impact on performance, session cursors can be stored in a session cursor cache. These cursors are those that have been closed by the application and can be reused.

Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.

Below is test case by using java program:

1. SESSION_CACHED_CURSORS =100
<!--java code -->
   sql = "alter session set SESSION_CACHED_CURSORS =100";
   ps = aconnection.prepareStatement(sql);
   ps.executeQuery();

      sql = "SELECT COUNT(*) FROM TT";
for (int i=0;i<30;i++ )
{
   ps = aconnection.prepareStatement(sql);      --repeatedly issue parse calls
   rs = ps.executeQuery();
   Thread.sleep(1000,0);
   ps.close();                                  --close statement(cursor)
}
<!--java code -->

--get open cursor, and only one sql statement
select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
---------------------------------------------------------------------------------------------------------------
3432EB34 145 SYS 27FA93D0 560338920 294ugjshqc5z8 SELECT COUNT(*) FROM TT

--parse count (total) is 33, and session cursor cache hits is 29
        RUN DATE                SID     NAME                                  VALUE
-----------------------------------------------------------------------------------------
before 2008-07-20 22:45:16 145 STAT...session cursor cache hits 2
before 2008-07-20 22:45:16 145 STAT...parse count (total)         29
before 2008-07-20 22:45:16 145 STAT...parse count (hard)         3
after 1 2008-07-20 22:45:46 145 STAT...session cursor cache hits 32
after 1 2008-07-20 22:45:46 145 STAT...parse count (total)         62
after 1 2008-07-20 22:45:46 145 STAT...parse count (hard)         4

2. SESSION_CACHED_CURSORS =0

select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
--------------------------------------------------------------------------------------------------------------
no rows

--parse count (total) is 38, and session cursor cache hits is 0
        RUN DATE                SID     NAME                                  VALUE
-----------------------------------------------------------------------------------------
before 2008-07-20 22:54:06 145 STAT...session cursor cache hits 754
before 2008-07-20 22:54:06 145 STAT...parse count (total)         350
before 2008-07-20 22:54:06 145 STAT...parse count (hard)         99
after 1 2008-07-20 22:54:37 145 STAT...session cursor cache hits 754
after 1 2008-07-20 22:54:37 145 STAT...parse count (total)         388
after 1 2008-07-20 22:54:37 145 STAT...parse count (hard)         102