07 세션 커서 캐싱

세션 커서 캐싱(Session Cursor Caching)

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

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

• v$sql
  1. users_opening : 공유커서를 차조하고 있는 세션커서의 수
  2. users_executing : 실행중인 세션 커서의 수

• 세션 커서 캐싱 테스트
  {section}
  {column:width=50}

SQL> ALTER SESSION SET SESSION_CACHED_CURSORS = 10;

세션이 변경되었습니다.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

시스템이 변경되었습니다.

SQL> SELECT empno, ename FROM emp WHERE empno = 7369;

     EMPNO ENAME
---------- --------------------
      7369 SMITH

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

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

SQL> SELECT empno, ename FROM emp WHERE empno = 7369;

     EMPNO ENAME
---------- --------------------
      7369 SMITH

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

PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          2             0               0

SQL> SELECT empno, ename FROM emp WHERE empno = 7369;

     EMPNO ENAME
---------- --------------------
      7369 SMITH

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

PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          3             1               0

{column}
{column:width=50}{column}
{section}

• session cursor caching hits
  {section}
  {column:width=50}

SQL> CREATE TABLE t (x NUMBER);

테이블이 생성되었습니다.

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     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;
  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     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 증가

{column}
{column:width=50}{column}
{section}

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