Wednesday, January 7, 2015

open_cursors, session_cached_cursors: cursordump

Oracle ( 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:
  1. hard parse:   not yet existed in SGA;
  2. soft parse:    globally available in SGA's shared pool (library cache);
  3. softer parse: locally available in PGA's Session Cached Cursor.
  4. 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.
If we model them in 3 dimensional Qualitative Physics, each phase can be represented as:
  1. hard parse:   parse_count_hard(+), parse_count_total(+), latch_count(+)
  2. soft parse:    parse_count_hard(0), parse_count_total(+), latch_count(+)
  3. softer parse: parse_count_hard(0), parse_count_total(+), latch_count(-)
  4. no parse:      parse_count_hard(0), parse_count_total(0), latch_count(-)
where + for increase, 0 for no change(or little change), - for decrease.

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)
 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:


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;