메뉴 건너뛰기

bysql.net

7._세션_커서_캐싱

2012.04.22 02:01

박영창 조회 수:7786

Chapter 04. 라이브러리 캐시 최적화 원리

07. 세션 커서 캐싱

* 개요
이전 세션의 설명은 'Hard Parsing 최소화' 에 초점을 맞춘 이야기

hash evaluation + library cache latch + library cache search
=> 시스템 부하 !!!
=> SQL 수행 횟수가 많아질 경우 racial condition 발생


그림 4-9. 초당 SQL 수행횟수

그림 4-10. 파싱관련 경합 발생 추이


파싱 경합 회피를 위해서 SQL 수행을 제한하기는 힘든일.

* 세션 커서 캐싱

세션커서 :
Shared Pool 에 위치한 Shared Cursor [공유커서] 를 실행하기 위해서 PGA 로 인스턴스화 시킨 것이 Session Cursor [세션 커서]

세션커서캐싱 :
세션커서를 사용하고 커서를 닫을 경우 해당 커서를 위해서 할당된 메모리와 Shared Cursor 를 가리키는 포인터까지 해제
이후 동일 SQL을 수행하면 오라클은 커서를 오픈하기 위해서 라이브러리 캐시를 재 탐색함.
이런 과정을 회피하기 위해서 수행 빈도가 높은 SQL을 캐시상에 저장하는 기법을 의미함.

기능 활성화
커서를 닫는 순간 해당 커서의 Parse Call 카운트를 확인하여 3 이상인 경우 Session Cursor를 Session Cursor Cache 로 이동

Session Cursor Cache = [ (SQL TEXT, SHARED CURSOR POINTER) , ..... ]


커서는 닫힌 상태이지만 Shared Cursor Pointer 를 가지고 있기 때문에 빠르게 커서를 오픈 가능함.

강점 및 특징
하드 파싱에서 발생하는 CPU 자원의 경감.
소프트 파싱에서 발생하는 latch 요청 횟수 감소.
LRU 알고리즘 사용으로 캐시된 커서를 aging
탐색을 위한 latch는 해당 기능으로 회피가 가능하지만 찾은 커서에 pin 을 설정/해제하는 과정이 있으므로 100% 회피는 하지 못함.

session_cached_cursors 커서 캐싱 항목수를 설정하는 파라메터

v$sql
- users_opening
공유 커서를 참조하고 있는 세션 커서의 수
수행을 마쳐 커서를 닫았더라도 참조를 버리지 않은 채 세션 커서 캐시에 옮겨지면 이 곳에 집계

- users_executing
해당 SQL을 현재 실행중인 세션 커서의 수를 보여줌
DML일 경우에는 수행을 마칠때 자동으로 커서가 닫히지만 select 문의 경우 EOF (end of fetch)에 도달 했을 때 커서가 닫힌다.
쿼리툴로 대량 데이터를 array 단위로 fetch 하는 시점에 조회를 해보면 1이상의 값을 보인다.
데이터를 조회하는게 끝난뒤에는 1만큼 줄어듬
SQL 수행중에는 공유 커서에 대한 참조를 유지한 상태이어야 하므로 값은 users_opening 보다 항상 작거나 같다.

SQL> select instance_name, status, logins from v$instance;

INSTANCE_NAME STATUS LOGINS
-------------------------------- ------------------------ --------------------
ORARACT1 OPEN ALLOWED

SQL> show parameter session_cached_cursors

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
session_cached_cursors integer 50

SQL> select * from scott.emp where empno = 7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

SQL> select parse_calls, users_opening, users_executing from v$sql
2 where sql_text = 'select * from scott.emp where empno = 7788'
3 ;

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

SQL> select * from scott.emp where empno = 7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

SQL> select * from scott.emp where empno = 7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

SQL> select parse_calls, users_opening, users_executing from v$sql
2 where sql_text = 'select * from scott.emp where empno = 7788'
3 ;

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

SQL>


동일 SQL이 3회 PARSE CALL 발생하면서 users_opening 값이 0에서 1로 바뀐 것을 볼 수 있음.
users_executing 이 계속 0 인 것으로 보아서 커서를 닫자마자 shared cursor 에 대한 참조까지 곧바로 해지되는 것을 알 수 있음.

※ users_opening 의 open 이 커서가 열린 상태를 의미하지 않는 다는 점에 주의.

Thomas Kyte @Effective Oracle By Design
-> session cursor caching 기능을 softer soft parse
-> 소프트 파싱 보다 더욱 소프트 !!!

활용 빈도 추적
SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.name in ('session cursor cache hits', 'parse count (total)' )
and b.statistic# = a.statistic# ; 2 3 4

NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
session cursor cache hits 0
parse count (total) 4

SQL> alter session set session_cached_cursors = 0;

Session altered.

SQL> declare
i number;
begin
for i in 1..10000
loop
execute immediate 'insert into scott.t values (' || mod(i,100) || ')';
end loop;
commit;
end;
/ 2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.name in ('session cursor cache hits', 'parse count (total)' )
and b.statistic# = a.statistic# ;
2 3 4
NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
session cursor cache hits 0
parse count (total) 10012

SQL> alter session set session_cached_cursors = 100;

Session altered.

SQL> declare
i number;
begin
for i in 1..10000
loop
execute immediate 'insert into scott.t values (' || mod(i,100) || ')';
end loop;
commit;
end;
/
2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL>
SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.name in ('session cursor cache hits', 'parse count (total)' )
and b.statistic# = a.statistic# ;
2 3 4
NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
session cursor cache hits 0
parse count (total) 20018

SQL> show parameter session_cached_cursors

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
session_cached_cursors integer 100

SQL> alter session set session_cached_cursors = 20000;

Session altered.

SQL> declare
i number;
begin
for i in 1..10000
loop
execute immediate 'insert into scott.t values (' || mod(i,100) || ')';
end loop;
commit;
end;
/ 2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.name in ('session cursor cache hits', 'parse count (total)' )
and b.statistic# = a.statistic# ;
2 3 4
NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
session cursor cache hits 500
parse count (total) 30069

SQL> declare
i number;
begin
for i in 1..10000
loop
execute immediate 'insert into scott.t values (' || mod(i,100) || ')';
end loop;
commit;
end;
/
2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL> SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.name in ('session cursor cache hits', 'parse count (total)' )
and b.statistic# = a.statistic# ; 2 3 4

NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
session cursor cache hits 505
parse count (total) 40071


책에서 나온 예제




세션 커서 캐싱과 무관하게 Parse Call은 증가하며, 세션 커서 캐싱은 Parse Call 을 대체한다기 보다는 Parse Call 부하를 감소하는 기능으로 이해해야함.

* 세션 커서 hit ratio 계산
SQL> select a.value "session cursor cache hits"
2 , b.value "total parse call count"
, round (a.value / b.value * 100, 2 ) "session cursor cache hits%"
from v$sysstat a, v$sysstat b
where a.name = 'session cursor cache hits'
and b.name = 'parse count (total)'
/ 3 4 5 6 7

session cursor cache hits total parse call count session cursor cache hits%
------------------------- ---------------------- --------------------------
2757429 458143 601.87

SQL>


※ 마찬가지로 11G에서는 정상적인 계산이 이루어 지지 않았음.
책에 나온 예제



























이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
66 Front Page file 운영자 2012.02.21 131201
65 6._문장수준_읽기_일관성 file 정찬호 2012.03.12 57274
64 3._SQL트레이스 sapius 2012.04.04 26849
63 5._Undo dasini 2012.03.11 25073
62 2._SQL_처리과정 dasini 2012.04.06 21666
61 6._RAC_캐시_퓨전 file 남송휘 2012.05.21 17525
60 4._커서_공유 file 남송휘 2012.04.27 16091
59 5._오라클_Lock file 시와처 2012.03.26 12577
58 8._블록_클린아웃 시와처 2012.03.19 12003
57 4._동시성_구현_사례 [1] dasini 2012.03.27 11694
56 8._Statspack_AWR 시와처 2012.04.01 11483
55 2._DB_버퍼_캐시 file 시와처 2012.03.04 10429
54 10._대기_이벤트 박영창 2012.03.19 10372
53 9._Snapshot_too_old 박영창 2012.03.19 9734
52 1._기본_아키텍처 file AskZZang 2012.03.02 8346
» 7._세션_커서_캐싱 박영창 2012.04.22 7786
50 5._Direct_Path_IO 남송휘 2012.05.21 7533
49 3._버퍼_Lock 박영창 2012.02.24 7245
48 7._PLSQL_함수의_특징과_성능_부하 file 남송휘 2012.05.15 6637
47 10._V$SQL file 정찬호 2012.04.09 6543
쓰기
태그