07. 세션 커서 캐싱

커서를 공유할 수 있는 형태로 SQL을 작성하면(바인드변수사용) 하드파싱을 최소화해
궁극적으로 시스템 확장성을 높일 수 있다.
그런데 하드파싱을 하지 않더라도 SQL 구믄을 분석해서 해시 값을 계산하고,
libarary cache 래치를 획득한 후 라이브러리 캐시에서 커서를 탐색하는 과정 자체도 부담스러운 작업이다.
Shared Pool 에 위치한 공유커서(shared cursor)를 실행하려고 PGA로 인스턴스화한 것이
세션 커서(session cursor)라고 설명했다. 쿼리를 수행한 후에 커서를 닫으면 세션 커서를 위해 할당된 메모를
물론 공유 커서를 가리키는 포인터까지 바로 해제된다.
그 다음에 동일한 SQL을 수행하면 커서를 오픈하기 위한 라이프러리 캐시 탐색작업을 다시 해야 한다.
오라클은 자주 수행하는 SQL에 대한 세션 커서를 세션 커서 캐시(Session Cursor Cache)에 저장할 수 있는 기능{}을
제공하는데,이를 '세션 커서 캐싱(Session Cursor Caching)' 이라고 한다.
이 기능을 활성화 하면, 커서를 닫는 순간 커서의 Parse Call 횟수를 확인해 보고 그 값이 3보다 크거나 같으면
세션 커서를 세션 커서 캐시로 옮긴다. 세션 커서 캐시에는 SQL 텍스트와 함께 공유 커서를 가리키는 포인터를 저장한다.
커서는 닫힌 상태지만 공유 커서에 대한 참조를 유지하기 때문에 다음 수행 시 더 빨리 커서를 오픈할 수 있다.
SQL문을 파싱해서 구믄을 분석하고 라이브러리 캐시에서 커서를 찾는 과정에서 소모되는 CPU 사용량을 줄일 수 있음은
물론, 소프트 파싱 과정에 발생하는 래치 요청 횟수를 감소시키는 효과를 가져온다. 세션 커서 내에서도 LRU 알고리즘{}을
사용함으로써 새로운 엔트리를 위한 공간이 필요할 때마다 기존 세션 커서 중 사용 빈도가 낮은 것부터 밀어낸다.
Session_cached_cursors 는 얼마나 많은 세션 커서를 캐싱할지를 지정하는 파라미터로서,
이 값을 0보다 크게 설정하면 Parse Call이 발생할 때마다 라이브러리 캐시를 탐색하기 전에 세션 커서 캐시를 먼저 살펴본다.
거기서 커서를 찾으면 라이브러리를 탐색하지 않고 곧바로 공유 커서를 찾아 커서를 오픈할 수 있다.
참고로, 탐색을 위한 래치 획득은 피할 수 있지만, 찾은 커서를 Pin 을 설정하고 나중에 다시 해제하는 과정에서 발생할 수 있는
충돌을 막아야 하므로 Library cache 래치를 완전히 회피하지는 못한다.

( 참고 딕셔네리 )
- V$sql.uses_opening : 공유 커서를 참조하고 있는 세션 커서의 수를 보여준다.
  수행을 마쳐 커서를 닫았더라도 참조를 버리지 않은 채 세션 커서 캐시로 옮겨진다면
  (Parse Call 횟수가 3보다 크거나 같다면) 여기에 집계된다.
- V$sql.users_executiong : 해당 SQL 을 현재 실행 중인, 즉 커서가 열려 있는 세션 커서의 수를 보여준다.
  DML일 때는 수행을 마칠 때 커서가 자동으로 닫히지만,
  select 문은 EOF(End Of Fetch)에 도달했을 때 커서가 닫힌다.

( Session Cached Cursor 테스트 )


SQL> alter session set session_cached_cursors=10 ;
Session altered
SQL> alter system flush shared_pool ;
System altered.
SQL> select * from emp where empno=7788 ;
...
SQL> select parse_calls, users_opening, users_executing from v$sql
2 where sql_text='select * from emp where empno=7788 ';

PARSE_CALLS USERS_OPENING USERS_EXECUTING
\--------\--\- --\--------\--\- --\------------\-
1 0 0
SQL> select * from emp where empno=7788 ;
...
SQL> select parse_calls, users_opening, users_executing from v$sql
2 where sql_text='select * from emp where empno=7788 ';
PARSE_CALLS USERS_OPENING USERS_EXECUTING
\--------\--\- --\--------\--\- --\------------\-
2 0 0
SQL> select * from emp where empno=7788 ;
...
SQL> select parse_calls, users_opening, users_executing from v$sql
2 where sql_text='select * from emp where empno=7788 ';
PARSE_CALLS USERS_OPENING USERS_EXECUTING
\--------\--\- --\--------\--\- --\------------\-
3 1 0

Users_opening 에서 'open' 의 의미가 실제 커서가 열려 있음을 의미하는 것이 아님을 기억할 필요가 있다.
커서는 닫힌 상태지만, 공유 커서에 대한 참조를 유지하기 때문에 다음 수행 시 더 빨리 커서를 오픈할 수 있는 것이다.
톰 카이트(Tomas Kyte)는 저서인 'Effective Oracle By Design)'에서 세션 커서 캐싱 기능을 "Softer Soft Parses'
라고 표현한 바 있다. 같은 SQL 문을 매번 하드파싱하지 않고 라이브러리 캐시에 공유된 커서를 반복 재사용하는
소프트 파스(Soft Parse)보다 더 소프트(Soft)하다는 것을 잘 표현해 주고 있다.

( Session Cached Cursor hits 테스트 )


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# ;

NAME VALUE
\--------------------------------------------------------\--\- --\-------\-
session cursor cache hits 312
parse count (total) 186

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 commit ;
9 end ;
10 /
PL/SQL procedure successfully completed.
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# ;
NAME VALUE
\-------------------------------------------------------\--\- --\-------\-
session cursor cache hits 312
parse count (total) 10197

Parse count(Total) 항목의 증가량을 통해, PL/SQL 문에서 100개의
다른 Insert 문을 10,000 번 수행하는 동안 10,000 번가량 Parse Call 이 발생 한 것을 알 수 있다.
Session_cached_cursors 파라미터를 0으로 설정했으므로,
session cursor cache hits 항목을 전혀 값이 증가 하지 않았다.


SQL> alter session set session_cached_cursors = 100 ;
Session altered.
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 commit ;
9 end ;
10 /
PL/SQL procedure successfully completed.
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# ;
NAME VALUE
\------------------------------------------------------\--\- --\-------\-
session cursor cache hits 9715
parse count (total) 20209

session_cached_cursor 파라미터를 100으로 설정하니까 100개의
다른 inset 문을 10,000번 수행하는 동안 session cursor cache hits 항목도 10,000 번 가까이 증가한
것을 볼 수 있다. 여기서 Parse Call 도 여전히 10,000번 가량 증가한 것을 볼 수 있다.
이를 통해, 커서를 세션 커서에 캐싱한다고 Parse Call 까지 줄지는 않는 것을 알 수 있다.
*다시 얘기하지만, 세션 커서 캐싱 기능은 Parse Call을 대체하기보다 Parse Call 부하를 감소키시는
기능으로 이해해야 한다*.
PL/SQL 에서는 SQL 커서를 자동으로 캐싱해 주는데, 10g 부터는 이 기능이 session_cached_cursors
파라미터를 0보다 크게 설정할 때만 작동한다.

문서에 대하여

최초작성자 : ~darkturtle
최초작성일 : 2009년 12월 05일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*