1. 세션 커서 캐싱
   1. users_opening
   2. user_executing
   3. session cursor cache hits
   4. 테스트 결론

세션 커서 캐싱

• 앞서, 다룬 내용들과 일맥 상통한 이야기로... 하드파싱으로 인한 시스템의 부하로 인하여 발생되는 문제를 DBMS 벤더 차원에서 제공하는 기능이라고보면된다.
• 자주수행하는 SQL에 대한 세션 커서를 세션커서 캐쉬에 저장할수있는 기능을 제공한다.
• 해당 기능이 활성화화면 커서를 닫는순간 Parse Call 횟수가 3보다 크면 세션 커서 캐쉬로 옮긴다.
• 그리하여, 나중에 SQL을 읽어오는 속도를 감소시킬수가 있다.
• 세션 커서 캐시 내에서 알고리즘은 LRU 알고리즘을 사용한다.

users_opening

• 공유 커서를 참조하고 있는 세션 커서의 수를 보여준다.
• 수행을 마쳐 커서를 닫았더라도 참조를 버리지 않은 채 세션 커서 캐시로 옮겨진다면 여기에 집계.

-- test

SQL> alter session set session_cached_cursors = 10;

세션이 변경되었습니다.

SQL> alter system flush shared_pool;

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

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select parse_calls, users_opening, users_executing
  2   from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%*v$sql*%';

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

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select parse_calls, users_opening, users_executing
  2   from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%*v$sql*%';

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

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select parse_calls, users_opening, users_executing
  2   from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%*v$sql*%';

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

-- session_cached_cursors = 0으로 해봄...

SQL> alter session set session_cached_cursors = 0;

세션이 변경되었습니다.

SQL> alter system flush shared_pool;

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

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select parse_calls, users_opening, users_executing
  2   from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%*v$sql*%';

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

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select parse_calls, users_opening, users_executing
  2   from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%*v$sql*%';

PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          4             0               0

user_executing

• 해당 SQL을 실행 중으로 생긴 커서가 열려있는 세션 커서의 수를 보여준다.

session cursor cache hits

- 파라미터 값을 0으로 설정후 테스트

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                               649
parse count (total)                                                    1888

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                               649
parse count (total)                                                   11910

위 테스트로 parse call 이 10000번 가량 발생한걸 확인할수있다.

- 파라미터 값을 100으로 설정후 테스트

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                              9955
parse count (total)                                                   21923

parse count가 올라가는건 확인하지만
session_cursor_cache_hits 율이 올라간걸 확인할수있다.

- 세션커서 캐시 히트율
SQL> select a.value "session cursor cache hits",
  2          b.value "total parse call count",
  3          round(a.value/b.value*100,2) "session cursor cache hits%"
  4  from v$sysstat a, v$sysstat b
  5  where a.name = 'session cursor cache hits'
  6  and b.name = 'parse count (total)';

session cursor cache hits total parse call count session cursor cache hits%
------------------------- ---------------------- --------------------------
                   127595                 139385                      91.54

테스트 결론

• 세션 커서 캐싱은 Parse Call 부하를 감소시키는 기능
• 애플리케이션단에서 동일한 SQL을 자주 실행시 파라미터를 조정하여서 사용하면 큰효과
• 10g이후엔 0보다 크게설정시에 작동하므로 반드시 기능활성화를 위해서 0보다 높은 파라미터 값을 설정해야한다.