1) 세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아서 커서를 오픈할 수 있다.
하지만, 공유커서 힙을 Pin -> 실행에 필요한 PGA 메모리 공간을 할당 등의 작업은 필요하다.
2) 이러한 과정마저 생략고 수행하는 방법을 애플리케이션 커서 캐싱이라고 한다.
(Parse Call을 발생 시키지 않고 SQL을 반복 수행 가능으로 오라클 정식 용어는 아니다)
3) 개발언어에 따라 구현방법이 다를 수 있다.
아래 두개 옵션으로감싸면 커서를 놓지 않고 반복 재사용한다.
For(;;){
EXEC ORACLE OPTION (HOLD_CURSOR=YES) ;
EXEC ORACLE OPTION (RELEASE_CURSOR=NO) ;
EXEC SQL INSERT .... ; // SQL 쿼리문 수행
EXEC ORACLE OPTION ( RELASE_CURSOR=YES) ;
}
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.18 0.14 0 0 0 0
Fetch 5000 0.17 0.23 0 10000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.35 0.37 0 10000 0 5000
Misses in library cache during parse: 1
Parse Call 이 한번만 발생했고, 이후 4,999 번 수행할 때는 Parse Call 이 전혀 발생하지 않음
( 최초 Parse Call 이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드파싱)
- 일반적으로 Execute Call 횟수만큼 Parse Call이 반복되지만 애플리케이션 커서 캐싱 기능을 이용하면
공유커서를 pin한채 반복수행하므로 Parse Call이 최초 한번만 발생하고 이후는 발생하지 않는다.
- HOLD_CURSOR는 프로그램 내에 있는 SQL들이 client context area에 캐싱되는가를 지정하는 파라메터
- RELEASE_CURSOR는 수행한 sql이 private sql area 내에서 release 될 수 있음을 지정하는 파라메터
Context area란?
context area는 sql관련 처리가 수행되는곳의 메모리 덩어리이다.
sql query 결과 또한 context area에 fetch될때까지 유지된다.
결과set이 fetch가 끝나면, context area는 메모리를 다시 해제하기 위해 비워진다.
http://docs.oracle.com/cd/B10500_01/server.920/a96524/c08memor.htm#17717 참고
public class ParseCall
{
public static void NoBinding(Connection conn, int count)
throws Exception(
PreparedStatement pstmt;
ResultSet rs;
for( int i = 1; i <= count; i ++){
pstmt = conn.prepareStatement(
" SELECT /* no_binding */ "+i+","+i+" ,'test' ,a.* FROM EMP a WHERE a.ENAME LIKE 'W%' ");
rs = pstmt.executeQuery();
rs.close();
pstmt.close();
}
}
}
********************************************************************************
SELECT /* no_binding */ 5000, 5000, 'test', a*
FROM emp a
WHERE a.ename LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
********************************************************************************
바인드 변수를 사용하지 않아 하드 파싱이 반복적으로 발생(별도의 쿼리로 보고 있음)
public static void NoCaching(Connection conn, int count)
throws Exception(
PreparedStatement pstmt;
ResultSet rs;
for( int i = 1; i <= count; i ++){
pstmt = conn.prepareStatement(
" SELECT /* no_caching */ ?,?,?,a.* " +
" FROM EMP a WHERE a.ENAME LIKE 'W%' ");
pstmt.setInt(1,i);
pstmt.setInt(2,i);
pstmt.setString(3,"test");
rs = pstmt.executeQuery();
rs.close();
pstmt.close();
}
}
********************************************************************************
SELECT /* no_caching */ :1,:2,:3, a*
FROM emp a
WHERE a.ename LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5000 0.06 0.05 0 0 0 0
Execute 5000 0.14 0.13 0 0 0 0
Fetch 5000 0.20 0.24 0 10000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15000 0.40 0.42 0 10000 0 5000
Misses in library cache during parse: 1
********************************************************************************
하드 파싱은 발생되지 않으나 parse call과 excute call은 for 문 횟수 만큼 발생된다.
public static void CursorHoiding(Connection conn, int count)
throws Exception(
//PreparedStatment를 루프문 바깥에 선언
PreparedStatement pstmt = conn.prepareStatement(
" SELECT /* cursor_holding */ ?,?,?,a.* " +
" FROM EMP a WHERE a.ENAME LIKE 'W%' ");
ResultSet rs;
for( int i = 1; i <= count; i ++){
pstmt.setInt(1,i);
pstmt.setInt(2,i);
pstmt.setString(3,"test");
rs = pstmt.executeQuery();
rs.close();
}
//루프를 빠져 나왔을 때 커서를 닫는다.
pstmt.close();
}
********************************************************************************
SELECT /* cursor_holding */ :1,:2,:3, a*
FROM emp a
WHERE a.ename LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.18 0.14 0 0 0 0
Fetch 5000 0.17 0.23 0 10000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.35 0.37 0 10000 0 5000
Misses in library cache during parse: 1
********************************************************************************
parse call을 한번만 호출되는 것으로 보아 LIBARARY CAHCE 안에 있는 쿼리문을 실행 단계
(excute call)에서 다시 변수 값을 매핑 하여 실행 하고 있다.
public static void CursorCaching(Connection conn, int count)
throws Exception(
//캐시 사이즈를 1로 지정
((OracleConnection)conn).setStratmentCacheSize(1);
//묵시적 캐싱 기능을 활성화
((OracleConnection)conn).setImplicitCachingEnabled(true);
for( int i = 1; i <= count; i ++){
//PreparedStatment를 루프문 안쪽에 선언
PreparedStatement pstmt = conn.prepareStatement(
" SELECT /* Implicit_caching */ ?,?,?,a.* " +
" FROM EMP a WHERE a.ENAME LIKE 'W%' ");
pstmt.setInt(1,i);
pstmt.setInt(2,i);
pstmt.setString(3,"test");
ResultSet rs = pstmt.executeQuery();
rs.close();
//커서를 닫지만 내부적으로는 닫히지 않는 채 캐시에 보관
pstmt.close();
}
}
********************************************************************************
SELECT /* Implicit_caching */ :1,:2,:3, a*
FROM emp a
WHERE a.ename LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 5000 0.23 0.14 0 0 0 0
Fetch 5000 0.23 0.22 0 10000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.48 0.36 0 10000 0 5000
Misses in library cache during parse: 1
********************************************************************************
for문안에서 PreparedStatement를 반복적으로 열고 닫았지만 묵시적 캐싱을 사용하여 (3)번과 비슷한 결과를 보여준다
1.실제 수행한결과..
cnt = 1000
prePareNoBinding() : 4806 ms elapsed...
preNoCaching() : 2528 ms elapsed...
preCursorHoding() : 2856 ms elapsed...
preCursorCaching() : 3276 ms elapsed...
count = 5000
prePareNoBinding() : 18674 ms elapsed...
preNoCaching() : 11919 ms elapsed...
preCursorHoding() : 12341 ms elapsed...
preCursorCaching() : 12964 ms elapsed...
2. JAVA로 테스트했기 때문에 네트워크를 통한 Roundtrip 영향이 커, 파싱에 의한 부하는 상대적으로 작게 나타난 점을 감안해야 한다.
PL/SQL에서는 JAVA와 같이 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱한다.
단, Static SQL을 사용할 때만 그렇다.
Static SQL은 자동으로 커서 캐싱이 발생하지만 Dynamic SQL, Cursor Variable(Ref Cursor)일때는 커서를 자동으로 캐싱하는 효과가 사라진다.
오라클 9i까지는 OPEN_CURSORS의 파라미터수에 결정이 되었지만 10g는 SESSION_CACHE_CURSORS 파라미터에 의해 결정되어진다. SESSION_CACHE_CURSORS=0 으로 하여 세션 커서 캐싱 기능을 비활성화하면 PL/SQL의 자동 커서 캐싱 기능도 비활성화 된다.
10g 이후는 같은 sql문을 연속적으로 실행하면, Dynamic SQL이더라도 바로 직전 커서를 캐싱한다.
SQL> alter session set session_cached_cursors=100;
--3번 이상 수행한 같은 SQL 문장은 자동으로 수행이 된다. 현재는 수동으로 100을 입력
세션이 변경되었습니다.
SQL> create table t ( x number);
테이블이 생성되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 execute immediate 'insert into t values('|| mod(i, 10) ||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count(distinct sql_text) sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
10 100 100
-> 10개의 SQL을 불연속적으로 실행했더니 Parse Call이 SQL 수행횟수만큼 발행
*****************************************************************
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 execute immediate 'insert into t values('|| ceil(i/10) ||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
- CEIL(n) : CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수
SQL> select count(distinct sql_text) sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
10 10 100
-> 10개 SQL을 연속적으로 입력했더니 Parse Call이 SQL개수만큼만 발생
*****************************************************************
SQL> alter session set session_cached_cursors=0;
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 execute immediate 'insert into t values('|| ceil(i/10) ||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
- CEIL(n) : CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수
SQL> select count(distinct sql_text) sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
10 100 100
-> 10개 SQL을 연속적으로 입력했지만 세션커서캐싱기능을 비활성화시켜 Parse Call이 SQL개수만큼만 발생
h4.AWR 리포트 중 Execute to Parse 항목
Instance Efficienct Percentages (Target100)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait : 99.98 Redo NoWait : 100.00
....
Execute to Parse : 89.31
으로 구해지며, Parse Call없이 SQL을 수행한 횟수를 의미한다.
n-Tier환경에서는 DB Connection을 사용하고 나서 곧바로 커넥션풀에 반환하므로 애플리케이션에 커서 캐싱 기법을 제대로 활용하기가 쉽지 않다. 이러한 환경에서는 대개 50%미만의 수치를 보인다.위의 경우는 PL/SQL을 많이 사용한 시스템에서 자주 나타나는 현상이다.