세션 커서 캐싱

앞서, 다룬 내용들과 일맥 상통한 이야기로...
하드파싱으로 인한 시스템의 부하로 인하여 발생되는 문제를
DBMS 벤더 차원에서 제공하는 기능이라고보면된다.

자주수행하는 SQL에 대한 세션 커서를 세션커서 캐쉬에 저장할수있는 기능을 제공한다.

해당 기능이 활성화화면
커서를 닫는순간 Parse Call 횟수가 3보다 크면 세션 커서 캐쉬로 옮긴다.
그리하여, 나중에 SQL을 읽어오는 속도를 감소시킬수가 있다.
세션 커서 캐시 내에서 알고리즘은 LRU 알고리즘을 사용한다.

LRU 알고리즘 : http://aozjffl.tistory.com/260

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

– 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 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 ||
|{code:sql}
해당 SQL을 실행 중으로 생긴 커서가 열려있는 세션 커서의 수를 보여준다.

session cursor cache hits
{code:sql}

파라미터 값을 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보다 높은 파라미터 값을 설정해야한다.

세션 커서 캐싱 (by bshman) [2012.05.18]

세션 커서 캐싱