ORACLE SESSION CACHED CURSORS
from WIKI(EXEM) | SESSION CACHED CURSORS |
http://wiki.ex-em.com/index.php/SESSION_CACHED_CURSORS
from UKJA | SESSION CACHED CURSORS | http://ukja.tistory.com/99
from ORACLE FAQ’s | v$open_cursor | http://www.orafaq.com/node/758
1. SESSION CACHED CURSORS
CURSOR를 CACHE 한다는 뜻. 본래 커서를 사용하기 위해서는 LIBRARY CACHE를
탐색해 해당하는 LCO에서 커서(작업영역) 정보를 얻어야 한다. 여기서 커서정보는
실데이타가 아니라 정보를 가리키는 어드레스인데 이 정보를 PGA에 캐싱한다. 그렇게
되면 LIBRARY CACHE 탐색없이 PGA 에 저장된 어드레스로 엑세스가 가능해진다.
2. SESSION CACHED CURSOR 설정
10.2.0.1.0 : 기본이 20으로 설정되어있다.
9.2.0.4.0 : 기본이 0으로 설정되어있다.
<<시스템 LEVEL 에서 변경>>
SQL> alter system set session_cached_cursors=10 scope=memory;
alter system set session_cached_cursors=10 scope=memory
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL> alter system set session_cached_cursors=10 scope=spfile;
System altered.
spfile을 사용하는 경우 위와같이 scope=spfile 로 설정할 수 있다. scope=memory 는
에러가 난다. 다시말해 파라미터 변경 후 DB의 restart 가 필요하다.
<<세션 LEVEL 에서 변경>>
SQL> alter session set session_cached_cursors=10;
Session altered.
3. SESSION CACHED CURSORS 의 효과
커서정보에 접근하기 위해선 해당하는 LCO에 엑세스 해야 되고 이에 다다르기 위해서는
LIBRARY CACHE를 탐색해야 한다. LIBRARY CACHE를 탐색하기 위해서는 LATCH를
획득해야 한다. 이 경합은 latch : library cache 로 관측된다. 해당 파라미터의 설정은
이 경합을 줄일 수 있다.
4. SESSION CACHED CURSORS 의 역효과
캐싱된 커서의 기본정보는 PIN 상태가 되고 실행계획정보는 PIN 되지 않는다. SHARED_POOL 을 FLUSH 하더라도 PIN 되어있는 정보는 그대로 남아있게 된다.
다시말해 커서의 기본정보가 그대로 남게된다. 이는 열려있는 상태의 커서의 개수가 지나치게 많아지면 메모리 단편화의 원인이 될 수 있다.
<<CACHED CURSOR & FLUSH SHARED POOL>>
A. CACHED 되지 않은 경우
아래 수행한 스크립트에 대해 간단하게 설명하겠다.
f_o_cur v$open_cursor 를 조회
f_hval v$sql 을 조회해 hash_value 를 반환
f_plan v$sql_plan 을 조회 (여기서는 플랜 존재여부 확인으로 사용)
진행하기에 앞서 session_cached_cursors 값을 0으로 변경하였고
select count(*) from scott.emp; 명령을 2회 날리고 진행하였다.
(3회째 캐싱된다.)
SQL> @f_o_cur
SID USER_NAME HASH_VALUE SQL_TEXT
---- ---------- ---------- ------------------------------------------------------------
149 SYS 4010258057 SELECT USER FROM SYS.DUAL
149 SYS 1268992308 select sid,user_name,hash_value,sql_text from v$open_cursor
사전에 scott.emp에 대해 수행했던 작업은 캐싱되어있지 않다. 캐싱되면 위 결과에서
확인할 수 있다.
SQL> @f_hval
Enter value for sql_text: select count(*) from scott.emp
old 2: where sql_text = '&sql_text'
new 2: where sql_text = 'select count(*) from scott.emp'
HASH_VALUE
----------
2575340915
SQL> @f_plan
Enter value for hash_value: 2575340915
old 3: where hash_value = &hash_value
new 3: where hash_value = 2575340915
ID OPERATION OPTIONS OPTIMIZER PARENT_ID
--- -------------------- -------------------- ---------- ----------
0 SELECT STATEMENT ALL_ROWS
1 SORT AGGREGATE 0
2 INDEX FULL SCAN 1
수행했던 sql 에 대해 v$sql에서 그 존재를 확인했다.
여기서 두번 수행했던 쿼리를 한번 더 수행한 후 f_o_cur.sql 로 확인했지만 두번 수행했던
경우와 결과가 동일했다.
SQL> alter system flush shared_pool;
System altered.
SQL> @f_hval
Enter value for sql_text: select count(*) from scott.emp
old 2: where sql_text = '&sql_text'
new 2: where sql_text = 'select count(*) from scott.emp'
no rows selected
shared_pool을 flush 하자 해당 sql 에 대한 정보가 사라졌음을 확인하였다.
B. CACHED 된 경우
진행에 앞서 shared pool 을 flush 했고 조회 스크립트를 3회씩 사전 수행하였다.
(session cursor cache count 값에 대한 영향을 피하기 위해)
f_cur_stat 은 v$statname 과 v$sesstat 을 조인한 스크립트로 cursor 에 관한
statistic 을 반환한다.
SQL> @f_cur_stat
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2
session cursor cache hits 179
session cursor cache count 9
SQL> @f_o_cur
SID USER_NAME HASH_VALUE SQL_TEXT
---- ---------- ---------- ------------------------------------------------------------
149 SYS 4010258057 SELECT USER FROM SYS.DUAL
149 SYS 1268992308 select sid,user_name,hash_value,sql_text from v$open_cursor
149 SYS 3643949098 select vstat.name,vses.value from v$sesstat vses,v$statname
SQL> select max(sal) from scott.emp;
MAX(SAL)
----------
5000
SQL> /
MAX(SAL)
----------
5000
SQL> @f_cur_stat
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2
session cursor cache hits 181
session cursor cache count 9
SQL> @f_o_cur
SID USER_NAME HASH_VALUE SQL_TEXT
---- ---------- ---------- ------------------------------------------------------------
149 SYS 1268992308 select sid,user_name,hash_value,sql_text from v$open_cursor
149 SYS 3643949098 select vstat.name,vses.value from v$sesstat vses,v$statname
동일한 쿼리를 두번 수행 후 뷰를 조회해 보았고 아무런 변화가 확인되지 않았다.
SQL> select max(sal) from scott.emp;
MAX(SAL)
----------
5000
SQL> @f_cur_stat
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2
session cursor cache hits 182
session cursor cache count 10
SQL> @f_o_cur
SID USER_NAME HASH_VALUE SQL_TEXT
---- ---------- ---------- ------------------------------------------------------------
149 SYS 486788784 select max(sal) from scott.emp
149 SYS 1268992308 select sid,user_name,hash_value,sql_text from v$open_cursor
149 SYS 3643949098 select vstat.name,vses.value from v$sesstat vses,v$statname
위에 표시한 부분에 변화가 생겼다.
SQL> @f_hval
Enter value for sql_text: select max(sal) from scott.emp
old 2: where sql_text = '&sql_text'
new 2: where sql_text = 'select max(sal) from scott.emp'
HASH_VALUE
----------
486788784
SQL> @f_plan
Enter value for hash_value: 486788784
old 3: where hash_value = &hash_value
new 3: where hash_value = 486788784
ID OPERATION OPTIONS OPTIMIZER PARENT_ID
--- -------------------- -------------------- ---------- ----------
0 SELECT STATEMENT ALL_ROWS
1 SORT AGGREGATE 0
2 TABLE ACCESS FULL 1
SQL> alter system flush shared_pool;
System altered.
SQL> @f_cur_stat
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2
session cursor cache hits 222
session cursor cache count 9
SQL> @f_o_cur
SID USER_NAME HASH_VALUE SQL_TEXT
---- ---------- ---------- ------------------------------------------------------------
149 SYS 486788784 select max(sal) from scott.emp
149 SYS 4010258057 SELECT USER FROM SYS.DUAL
149 SYS 1268992308 select sid,user_name,hash_value,sql_text from v$open_cursor
SQL> @f_hval
Enter value for sql_text: select max(sal) from scott.emp
old 2: where sql_text = '&sql_text'
new 2: where sql_text = 'select max(sal) from scott.emp'
no rows selected
SQL> @f_plan
Enter value for hash_value: 486788784
old 3: where hash_value = &hash_value
new 3: where hash_value = 486788784
no rows selected
shared pool 을 flush 했음에도 불구하고 cursor 정보가 남아있다.
다만 실행계획정보는 사라진걸 알 수 있다.
SQL> select max(sal) from scott.emp;
MAX(SAL)
----------
5000
SQL> @f_plan
Enter value for hash_value: 486788784
old 3: where hash_value = &hash_value
new 3: where hash_value = 486788784
ID OPERATION OPTIONS OPTIMIZER PARENT_ID
--- -------------------- -------------------- ---------- ----------
0 SELECT STATEMENT ALL_ROWS
1 SORT AGGREGATE 0
2 TABLE ACCESS FULL 1
SQL> @f_o_cur
SID USER_NAME HASH_VALUE SQL_TEXT
---- ---------- ---------- ------------------------------------------------------------
149 SYS 4010258057 SELECT USER FROM SYS.DUAL
149 SYS 1268992308 select sid,user_name,hash_value,sql_text from v$open_cursor
scott.emp 에 대해 동일한 쿼리를 실행 후 v$open_cursor 를 조회하면 캐싱되었던 정보가
사라진 걸 확인할 수 있다.
5. session_cached_cursors 예제
캐싱된 cursor 는 library cache 탐색없이 PGA 에 저장된 주소를 가지고 직접 접근한다.
다시 말해 LATCH : LIBRARY CACHE 경합을 피할 수 있다.
/*+ LATCH : LIBRARY CACHE 유발 쿼리 제작 및 예제로 추가 -_-; */
<<session_cached_cursors = 0>>>
<<session_cached_cursors <> 0>>
