On Oracle: open_cursors, session_cached

1. Parse

Oracle (11.2.0.3.0) document wrote:

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once (Oracle® Database Reference 11g Release 2 (11.2)).

SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor (Oracle® Database Reference 11g Release 2 (11.2)).

Popular Oracle books invested a large paragraph on both subjects:

Effective Oracle by Design (Thomas Kyte): Page 287-302
Oracle Core: Essential Internals for DBAs and Developers (Jonathan Lewis): Page 173-178
Oracle Performance Firefighting (Craig Shallahamer): Page 261
Troubleshooting Oracle Performance (Christian Antognini) (2nd Edition): Page 436-438

In summary, one cursor experiences:

hard parse: not yet existed in SGA
soft parse: globally available in SGA's shared pool (library cache)
softer parse: locally available in PGA's Session Cached Cursor (controlled by SESSION_CACHED_CURSORS)
no parse: executed in PL/SQL and found in PL/SQL cached type (specially optimized for PL/SQL)

and its life stage undergoes from one phase to next.

Classifying by its locality (similar to NUMA memory):

hard parse: first-time creating(or re-creating)
soft parse: global shared_pool cache
softer parse: local session UGA cache
no parse: running thread heap

If we model them in 3 dimensional Qualitative Physics, each phase can be represented as:

hard parse: parse_count_hard(+), parse_count_total(+), lock_count(+), pin_count(+)
soft parse: parse_count_hard(0), parse_count_total(+), lock_count(+), pin_count(+)
softer parse: parse_count_hard(0), parse_count_total(+), lock_count(0), pin_count(+)
no parse: parse_count_hard(0), parse_count_total(0), lock_count(0), pin_count(0)

where + for increase, 0 for no change(or little change).
(Note:lock_count: library cache lock; pin_count: library cache pin)

So we can use 4 metrics to determine a cursor's parse stage (see queries of next sesction).

2. Parse Identifying

With following example queries, we can determine the types of parsing: (Note: 'parse count (total)' is same as 'parse_calls').

select n.name, s.value 
  from v$statname n, V$SESSTAT s 
 where n.statistic# = s.statistic# 
   and n.name in ('execute count', 'parse count (hard)', 'parse count (total)', 'session cursor cache hits')
   and s.sid in (:sid);
 
select executions, parse_calls, locked_total, pinned_total, loads, invalidations, child_number, last_load_time, v.* 
  from V$SQL v 
 where sql_id in (':SQL_ID') or lower(sql_text) like '%:sql_text%'
  order by v.last_load_time desc;

select timestamp, locked_total, pinned_total, loads, invalidations, v.* 
  from V$DB_OBJECT_CACHE v 
 where lower(name) like '%:name%' 
 order by v.timestamp desc;

select cursor_type, v.* 
  from V$OPEN_CURSOR v 
 where v.cursor_type in ('SESSION CURSOR CACHED', 'PL/SQL CURSOR CACHED') 
   and v.sid in (:sid) and sql_id in (':sql_id')
   --and lower(sql_text) like '%:sql_text%'
 order by v.sid, v.cursor_type;

The difference among them are signified by the columns' values of those views:

1. hard parse:   
     V$SESSTAT 'parse count (hard)' value
     V$SQL.invalidations and loads
     V$DB_OBJECT_CACHE.invalidations and loads
2. soft parse:  
     V$SQL.locked_total 
     V$DB_OBJECT_CACHE.locked_total     
3. softer parse: 
     V$SESSTAT 'session cursor cache hits' value
     V$SQL.pinned_total 
     V$DB_OBJECT_CACHE.pinned_total    
     V$OPEN_CURSOR 'SESSION CURSOR CACHED'
4. no parse:
     V$OPEN_CURSOR 'PL/SQL CURSOR CACHED'
     V$SQL.parse_calls (not increasing)
     V$DB_OBJECT_CACHE.parse_calls (not increasing)

Only in case of "no parse", 'parse count (total)' or parse_calls are not increased.

3. Cusrordump

A cusrordump by:

alter session set open_cursors=400;
alter session set session_cached_cursors=600;
alter session set tracefile_identifier = "cursordump_4";
alter session set events 'immediate trace name cursordump level 4';

reveals certain aspects of internal implementation (irrelevant details are removed):

----- Session Cursor Dump -----
Current cursor: 4, pgadep=0

Open cursors(pls, sys, hwm, max): 4(0, 2, 64, 400)
NULL=3 SYNTAX=0 PARSE=0 BOUND=1 FETCH=0 ROW=0
Cached frame pages(total, free):
4k(50, 50), 8k(1, 1), 16k(1, 1), 32k(0, 0)
----- Session Open Cursors -----
----------------------------------------
Cursor#1(0x1108e1b38) state=NULL curiob=0x110906698
  ......
Cursor#6(0x1108e1e08) state=NULL curiob=0x110e38c70
  ......
Cursor#5(0x1108e1d78) state=NULL curiob=0x11090a528
  ......
Cursor#4(0x1108e1ce8) state=BOUND curiob=0x110908068
  ......

----- Session Cached Cursor Dump -----
----- Generic Session Cached Cursor Dump -----
-----------------------------------------------------------
hash table=1108e4100 cnt=539 LRU=1108d5168 cnt=536 hit=2263 max=600 NumberOfTypes=6
type#0 name=DICTION count=0
type#1 name=BUNDLE count=13
type#2 name=SESSION count=38
type#3 name=PL/SQL count=485
type#4 name=CONSTRA count=0
type#5 name=REPLICA count=0
Bucket#001 seg=1108e4128 nit=8 nal=8 ips=8 sz=56 flg=3 ucnt=0
Bucket#008 seg=1108e4278 nit=8 nal=8 ips=8 sz=56 flg=3 ucnt=1
    0 cob=110caaa28 idx=8 flg=0 typ=3 cur=110e27f90 lru=1 fl=15
......
Bucket#109 seg=1108e5568 nit=8 nal=8 ips=8 sz=56 flg=3 ucnt=2
    0 cob=110907140 idx=6d flg=0 typ=2 cur=11092ac40 lru=1 fl=1
    1 cob=110907178 idx=1006d flg=0 typ=2 cur=110c796f0 lru=1 fl=1
......
Bucket#256 seg=1108e70f8 nit=8 nal=8 ips=8 sz=56 flg=3 ucnt=0

In the above dump, max number of Open cursors is noted with 400 due to open_cursors=400;
and indicated with state info:

NULL=3 SYNTAX=0 PARSE=0 BOUND=1 FETCH=0 ROW=0

Max number of Session Cached Cursor is 600 because of session_cached_cursors=600.

Internally Session Cached Cursor is a hash table consisting of 256 Buckets, classified into 6 types, and located in PGA. Since Session Cached Cursor is limited with 256 Buckets, setting session_cached_cursors bigger than 256 incurs more chance of hash collision (Bucket#109 in above example).

Open cursors and Session Cached Cursors by each session are exposed by V$OPEN_CURSOR which lists cursors that each user session currently has opened and parsed, or cached. Internally they are the kgl locks (x$kgllk) imposed by the session.

Joining v$open_cursor with v$libcache_locks (or dba_kgllock) displays information (LOCK_HELD, REFCOUNT, MODE_HELD, MODE_REQUESTED) about the locks:

   select * from v$open_cursor c, v$libcache_locks l
where c.saddr = l.holding_user_session
    and c.address = l.object_handle;

On Oracle

Wednesday, January 7, 2015

open_cursors, session_cached_cursors: cursordump

1. Parse

2. Parse Identifying

3. Cusrordump