parameter SESSION_CACHED_CURSORS

--------------------------------------------------------------------------------
Syntax  SESSION_CACHED_CURSORS 
설정방법  Parameter File
ALTER SESSION SET SESSION_CACHED_CURSORS = 100;
ALTER SYSTEM SET SESSION_CACHED_CURSORS = 100;
--------------------------------------------------------------------------------
 
SESSION_CACHED_CURSORS 파라미터 값이 0보다 크면 Session Cursor Caching 기능이 사용된다. Session Cursor Caching 기능이란 하나의 Session 내에서 3회 이상 parse call한 커서 SQL Statement를 PGA 영역에 Cache하는 것을 의미한다.

sql문이 실행될 때마다 세션의 파싱 단계에서 library cache를 검색한다. 하지만 자신이 수행하고자 하는 Execute plan이 없으면 'Hard parsing'을 할 것이고, 있으면 'Soft parsing'을 하게 될 것이다.
"Hard parsing 뿐만 아니라 "Soft parsing"도 librrary cache latch 와 cpu 오버 헤드를 발생시킨다.

일반적으로 Soft Parse가 왕성하고 한번에 많은 수의 Cursor를 사용하는 Application에서는 SESSION_CACHED_CURSORS 파라미터의 값을 크게 함으로써 library cache 래치 경합을 줄일 수 있다.

각각의 커서가 고정된 경우는 이 영역에 대해서 할당할 수 있는 더 많은 shared pool 영역을 요구할 수도 있다. 일반적인 shared pool에서의 커서는 2개의 컴퍼넌트로 구성되어 있다.
a)heap 0 - 1 KB 크기
b)SQL AREA - 4KB 배수 크기

Session Cursor Caching에 의해 PGA에 Cache된 Cursor는 다음과 같은 방법으로 Shared Pool에 Pin된다.

   1. Cursor를 구성하는 Heap0(Cursor 기본 정보)은 Pin된다.
   2. Cursor를 구성하는 Heap6(Cursor의 실행 계획 정보)는 Pin되지 않는다. 

Pin된 영역은 Flush되지 않는다. 따라서 Session Curors Caching에 의해 Cache된 Cursor의 기본 정보는 Shared Pool에 계속 상주하게 된다. 이런 이유 때문에 Cache된 Cursor에 대해서는 Shared Pool의 특정 영역으로 직접 Access가 가능하고 그 만큼 library cache 래치를 점유하는 시간이 줄어든다. 반면 이렇게 Cache된 Cursor의 개수가 지나치게 많으면 그만큼 Flush되지 않는 Cursor(Heap0)의 수가 증가한다. 따라서 Shared Pool의 Fragmentation 현상이 발생할 수 있다.

이런 이유 때문에 Hard Parse가 왕성한 시스템에서는 Flush가 원활하게 이루어져야 하기 때문에 SESSION_CACHED_CURSORS 파라미터 값을 낮게 설정하는 것이 좋다. 보통 50 정도의 값에서 시작하는 것이 권장되며, Hard Parse와 Soft Parse의 발생 정도, Shared Pool의 크기에 따라 가감하는 방식을 사용한다.

  이 파라미터의 동작유무를 체크하려면 다음과 같이 수행한다.

SQL> select max(VALUE) from v$sesstat
where STATISTIC# in(select STATISTIC# from v$STATNAME where NAME = 'session cursor cache count');

MAX(VALUE)
-----------
         20

1 rows selected.

  과거에 session_cached_cursors의 최대값을 보여주는데, 이 값이 init.ora에서 value = "session_cached_cursors" 라면 이 값을 증가할지를 고려해보야 한다.

  이 session cache가 얼마나 얼마나 동작하는지를 보고싶다면 다음과 같이 수행한다.

SQL> select cache/tot*100 "Session cursor cache%"
from
(select value tot from v$sysstat where name = 'parse count (total)'),
(select value cache from sys.v$sysstat where name = 'session cursor cache hits');

Session cur
-----------
 8.849e+001

1 rows selected.

결론적으로, OLTP 어플리케이션에서 같은 SQL 세트는 많은 횟수로 실행되기 때문에 반드시 이 파라미터 값을 Default 값이 50 이상으로 세팅할 필요가 있다.