[1] 세션 커서

• 공유커서를 실행하려고 PGA로 인스턴스화 한 것이다.
• 쿼리 수행 후에 커서를 닫으면 세션커서를 위해 할당된 메모리 및 공유커서를 가리키던 포인터도 해제된다. 이후 같은 Sql을 재수행 할때 라이브러리 캐시를 재탐색한다.

[2] 세션 커서 캐싱

(1) 특징

• 세션 커서 캐시에는 SQL문장과 함께 공유커서를 가리키는 포인터가 저장된다.
• 커서는 닫힌 상태지만 공유커서에 대한 참조를 유지하기 때문에 커서를 빨리 오픈할 수 있다.
• 자주 사용되는 Sql문에 의한 라이브러리 캐시 부하를 경감시킨다.
• 소프트 파싱 과정에서 발생하는 래치 요청 횟수를 감소시킨다.
• LRU 알고리즘에 의해 사용빈도 낮은것부터 밀어낸다.

(2) v$sql

• users_opening : 공유커서를 차지하고 있는 세션커서의 수
• users_executing : 실행중인 세션 커서의 수

(3) 세션 커서 관련 sql

세션 변경

SQL> ALTER SESSION SET SESSION_CACHED_CURSORS = 10;

시스템 변경

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

[예시]

SQL> SELECT empno, ename FROM emp WHERE empno = 7369;
SQL> SELECT parse_calls , users_opening , users_executing
     FROM v$sql
     WHERE sql_text = 'SELECT empno, ename FROM emp WHERE empno = 7369';

=========================================
PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          1             0               0
=========================================

[예시] session cursor caching hits

SQL> CREATE TABLE t (x NUMBER);
SQL> SELECT a.name, b.value
   FROM v$statname a, v$mystat b
         WHERE a.name IN ('session cursor cache hits'
         ,'parse count (total)')
         AND b.statistic# = a.statistic#;
         
=========================================
NAME                          VALUE
---------------------------- ------
session cursor cache hits     26181
parse count (total)           43509
=========================================         

SQL> ALTER SESSION SET SESSION_CACHED_CURSORS = 0; -- 세션이 변경되었습니다.
SQL> DECLARE
  2    i NUMBER;
  3  BEGIN
  4    FOR i IN 1 .. 10000
  5    LOOP
  6      EXECUTE IMMEDIATE 'INSERT INTO t VALUES(' || MOD(i, 100) || ')';
  7    END LOOP;
  8  END;
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.name IN ('session cursor cache hits','parse count (total)') AND b.statistic# = a.statistic#;;
                =========================================
                NAME                          VALUE
                ---------------------------- ------
                session cursor cache hits     26181 <=== 그대로
                parse count (total)           53521 <=== 10012 증가
                =========================================

SQL> ALTER SESSION SET SESSION_CACHED_CURSORS = 0; -- 세션이 변경되었습니다.
SQL> DECLARE
  2    i NUMBER;
  3  BEGIN
  4    FOR i IN 1 .. 10000
  5    LOOP
  6      EXECUTE IMMEDIATE 'INSERT INTO t VALUES(' || MOD(i, 100) || ')';
  7    END LOOP;
  8  END;
  
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.name IN ('session cursor cache hits','parse count (total)')
      AND b.statistic# = a.statistic#;
 
 =========================================
 NAME                          VALUE
 ---------------------------- ------
 session cursor cache hits     26181 <=== 그대로
 parse count (total)           53521 <=== 10012 증가
 =========================================

SQL> ALTER SESSION SET SESSION_CACHED_CURSORS = 100;--세션이 변경되었습니다.
SQL> DECLARE
  2    i NUMBER;
  3  BEGIN
  4    FOR i IN 1 .. 10000
  5    LOOP
  6      EXECUTE IMMEDIATE 'INSERT INTO t VALUES(' || MOD(i, 100) || ')';
  7    END LOOP;
  8  END;
  9  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.name IN ('session cursor cache hits','parse count (total)')
  4     AND b.statistic# = a.statistic#
  5  ;
  
 =========================================
 NAME                          VALUE
 ---------------------------- ------
 session cursor cache hits     35883 <===  9702 증가
 parse count (total)           63530 <=== 10009 증가
 =========================================

[4] 결론

• session_cached_cursors 파라미터를 0에서 100으로 변경해도 파싱횟수은 줄지 않는다.
• 즉, 세션 커서 캐싱 기능은 Parse call을 대체하는 것이 아니라 Parse call의 부하를 감소시키는 기능
• PL/SQL 에서는 SQL 커서를 자동으로 캐싱해 주는데 10g 부터는 이 기능이 session_cached_cursors 파라미터를 0보다 크게 설정할 때만 작동 > 따라서 이 기능을 반드시 활성화