The secret of session_cached_cursors

Session_cached_cursors is one of the most confusing and misunderstood parameters in Oracle.

To summarize (at least in 10g):

1. Oracle caches the cursor of the SQL statement executed more than 2 times in the same server process. Yes, the cursor is cached in PGA.
2. Oracle caches the cursor of the PL/SQL block regardless of the execution count also in the PGA.
3. The cache has pointer to the shared SQL area. Absolutely shared SQL area is located in SGA.
4. With cached cursors, Oracle does not release the cursor even when the cursor is considered closed. <– This is the key trick of session cursor sharing.
5. Because the cursor is not closed, Oracle does not need to check the syntax and search the library cache. It just can go directly to the shared SQL area. Very fast and light.

Decipher? Let me show you a simple but concrete demonstration.

1. oc2.sql : Displays current open cursors for given session id and sql text

define __SID = &1

begin
  print_table('
select
  c.sid,
  c.sql_text
from 
  v$open_cursor c
where
  c.sid in (&__SID)  
  and c.sql_text like ''%/*+ session_cache */%''
'
  );
end;
/

2. Observe the open cursors while executing the statements

UKJA@ukja102> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

UKJA@ukja102> create table t1(c1 int);

Table created.

-- Current session id 
       SID                                                                                                              
----------                                                                                                              
       149                                                                                                              

UKJA@ukja102> 
UKJA@ukja102> alter session set session_cached_cursors = 50;

Session altered.

UKJA@ukja102> -- First execution of SQL statement
UKJA@ukja102> select /*+ session_cache */ * from t1;

UKJA@ukja102> select * from dual;

UKJA@ukja102> @oc2 149   -- The cursor was closed 

PL/SQL procedure successfully completed.

UKJA@ukja102> -- Second execution of SQL statement
UKJA@ukja102> select /*+ session_cache */ * from t1;

UKJA@ukja102> select * from dual;

UKJA@ukja102> @oc2 149   -- The cursor was closed 

PL/SQL procedure successfully completed.

UKJA@ukja102> -- Thrid!!! execution of SQL statement
UKJA@ukja102> select /*+ session_cache */ * from t1;

UKJA@ukja102> select * from dual;

UKJA@ukja102> @oc2 149  -- Yes, the cursor is not closed even when I explicitly closed it.
-- It's cached
SID                           : 149                                                                                     
SQL_TEXT                      : select /*+ session_cache */ * from t1                                                   
-----------------                                                                                                       

PL/SQL procedure successfully completed.

UKJA@ukja102> -- First execution of PL/SQL block
UKJA@ukja102> begin /*+ session_cache */ dbms_lock.sleep(0.01); end;
  2  /

PL/SQL procedure successfully completed.

UKJA@ukja102> select * from dual;

UKJA@ukja102> @oc2 149  -- It's cached!
SID                           : 149                                                                                     
SQL_TEXT                      : select /*+ session_cache */ * from t1                                                   
-----------------                                                                                                       
SID                           : 149                                                                                     
SQL_TEXT                      : begin /*+ session_cache */ dbms_lock.sleep(0.01); end;                                  
-----------------                                                                                                       

PL/SQL procedure successfully completed.

I can confirm that the library cache lock is hold on the cached cursors. This is a tautology of the fact that cursors are not closed. For open cursors, Oracle holds the library cache lock(LK) on the corresponding shared SQL area(LCO) in null mode(1).

UKJA@ukja102> @lock_internal 149


149 LK 27CC5CDC            1          0 select /*+ session_cache */ *
                                        from t1

149 LK 27D9D4E8            1          0 select /*+ session_cache */ *
                                        from t1
                                        
                                        
149 LK 27EA6AC0            1          0 begin /*+ session_cache */ dbm
                                        s_lock.sleep(0.01); end;

149 LK 27FE362C            1          0 begin /*+ session_cache */ dbm
                                        s_lock.sleep(0.01); end;
                                        

The source of lock_internal script is here.

The easy but not elegant way to observe how Oracle caches the cursor is dumping process state.

 
UKJA@ukja102> alter session set events 'immediate trace name processstate level 10';

Session altered.

I would not post the result of process state dump, just because of it’s readibility and length. :)