오라클 성능 고도화 원리와 해법 I (2012년)
세션 커서 캐싱 0 0 57,763

by 구루비스터디 커서 [2018.03.20]


  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보다 높은 파라미터 값을 설정해야한다.
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3102

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입