h1.08. 애플리케이션 커서 캐싱

• 세션 커서를 캐싱하면 SGA이 공유 커서를 빠르게 찾아서 커서를 오픈 할 수 있다.
  *하지만 세션 커서 캐시에 있는 SQL을 수행하더라도 공유 커서 힙을 Pin 하고 실행에 필요한 메모리 공간을
  PGA에 할당하는 등의 작업은 반복하게 한다. 이 과정마저 생략하고 빠르게 SQL을 수행하는 방법이 있는데,
  이를 '애플리케이션 커서 캐싱(Application Cursor Caching' 이라고 한다*.

h2.Pro*C
Pro*C 에서는 SQLㅇ르 수행하는 부분을 아래처럼 두 개 옵션으로 감싸면 커서를 놓지 않고 반복 재사용한다.

For(;;-) \{
EXEC ORACLE OPTION (HOLD_CURSOR=YES) ;
EXEC ORACLE OPTION (RELEASE_CURSOR=NO) ;
EXEC SQL INSERT .... ; // SQL 수행
EXEC ORACLE OPTION ( RELASE_CURSOR=YES) ;
\}

• HOLDER_CURSOR 옵션은 애플리케이션 커서와 세션 커서와의 관계를 다루는 반면 RELASE_CURSOR 옵션은
  공유커서와 세션 커서와의 관계를 다룬다.
  -일반적으로 Execute Call 횟수만큼 Parse Call 이 반복되지만 *애플리케이션 커서 캐싱 기능을 이용하면
  공유 커서를 Pin한 채 반복 수행하므로 Parse Call 이 최소 한번만 발생하고 이후로는 발생하지 않는다.*

아래는 애플리케이션에서 커서를 캐싱한 채 같은 SQL을 5,000번 반복 수행했을 때의 SQL 트레이스 결과다.

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 이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드 파싱을 수행한
  사실도 라이브러리 캐시 Miss 항목을 통해 읽을 수 있어야 한다. )

h2.JAVA
Java 에서 이를 구현하려면 묵시적 캐싱(Implicit Caching)옵션을 사용하거나 Statement 를 닫지 않고 재사용하면 된다.
구체적인 사용법을 익혀 보자. SQL을 아래 4가지 패텬으로 작성하고, 각각에 대한 수행속도를 비교해 볼 것이다.

-* 패턴 1 : 바인드 변수를 사용하지 않을 때
-* 패턴 2 : 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때
-* 패턴 3 : 커서를 닫지 않고 재사용할 때
-* 패턴 4 : 묵시적 캐싱 기능을

1. 바인드 변수를 사용하지 않을 때

Public class Parsecall
\{
Public static void nobining(connection conn, int count)
Throw Exception(
PreparedStatement stmt;
ResultSet rs ;
For ( int i = 1 ; I <= count ; I \+\+ ) \{
Stmt=conn.preparestatement(
"SELECT /\* no_biding */ " + i + "," + I + ",'test',a.* ' +
"FROM emp a WHERE a.ename LIKE 'W%');
*Rs = stmt.executequery();*
Rs.close();
Stmt.close();
\}
\}

• 바인드 변수를 사용하지 않았으므로 매번 하드 파싱을 반복 수행하게 된다.
  아래는 SQL 트레이스에서 맨 마지막 것만을 추출할 것이다

\-----------------------------------------------------------\-
SELECT /\* no_biding */ 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 0 0 0
\----\--\- --\-\--\- --\---\--\- --\-----\--\- --\-----\--\- --\-----\--\- --\-----\--\- --\-\-
total 10001 0.35 0.37 0 2 0 1
Misses in library cache during parse: 1

1. 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때

Public static void nocaching(connection conn, int count)
Throw Exception(
PreparedStatement stmt;
ResultSet rs ;
For ( int i = 1 ; I <= count ; I \+\+ ) \{
Stmt=conn.preparestatement(
"SELECT /\* no_caching */ ?,?,?,,a.* " +
"FROM emp a WHERE a.ename LIKE 'W%');
Stmt.setInt(1,i);
Stmt.setInt(2,i);
Stmt.setInt(3,"test")
*Rs=stmt.executequery();*
\\
Rs.close();
Stmt.close();
\}
\}

• Parse Call 이 Execute Call 횟수만큼 발생하게 된다.
  하지만 하드파싱은 전혀 발생하지 않거나 한번쯤 발생한다.

\-----------------------------------------------------------\-
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

1. 커서를 닫지 않고 재사용할 때

Public static void CursorHolding(connection conn, int count)
Throw Exception(
// PreparedStatment 를 루프문 바깥에 선언
PreparedStatement stmt = conn.preparedstatment (
"SELECT /\* cursor_holding */ ?,?,?,,a.* " +
"FROM emp a WHERE a.ename LIKE 'W%');
ResultSet rs ;
For ( int i = 1 ; I <= count ; I \+\+ ) \{
Stmt.setInt(1,i);
Stmt.setInt(2,i);
Stmt.setInt(3,"test")
*Rs=stmt.executequery();*

Rs.close();
\}
// 루프를 빠져 나?을 때 커서를 닫는다.
Stmt.close();
\}

• 아래 트레이스 결과를 보면, 앞에서 HOLD_CURSOR 와 RELEASE_CURSOR 옵션을 사용한
  Pro*C 사례에서 보았듯이 Parse Call 이 한번만 발생했다.

\-----------------------------------------------------------\-
SELECT /\* cursor_holding */ :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.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 15000 0.35 0.37 0 10000 0 5000
Misses in library cache during parse: 1

1. 묵시적 캐싱 기능을 사용할 때

• 마지막으로, PreparedStatement 를 루프문 안쪽에 선언하고 루프 내에서 퀴리를 수행하자마자 곧바로 닫는다.
  하지만 setStatementCachesize 를 0보다 크게 설정하고,setimplicitCachingEnabled 메소드를 True로 설정하였다.

Public static void CursorHolding(connection conn, int count)
Throw Exception(
// 캐시 사이즈를 1로 지정
*((OracleConnection)conn).setStatementCacheSize(1);*
// 묵시적 캐시 기능을 활성화
*((OracleConnection)conn).setImplicitCachingEnabled(true);*
\\
For ( int i = 1 ; I <= count ; I \+\+ ) \{
PreparedStatement stmt = conn.preparedstatment (
"SELECT /\* implicit_caching */ ?,?,?,,a.* " +
"FROM emp a WHERE a.ename LIKE 'W%');
\\
Stmt.setInt(1,i);
Stmt.setInt(2,i);
Stmt.setInt(3,"test")
*Rs=stmt.executequery();*
\\
Rs.close();
// 커서를 닫지만 내부적으로는 닫히지 않은채 캐시에 보관
Stmt.close();
\}
\}

• 루프 내에서 PreparedStetement를 매번 닫았지만 아래 트레이스 결과에서 보듯 Parse Call 은 단 한번만 발생했다.
  묵시적 캐싱(Implicit Caching)옵션을 활성화했기 때문이다.

\-----------------------------------------------------------\-
SELECT /\* implicit_cacing */ :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

• PL/SQL 에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱 한다.
• 단. Static SQL 을 사용할 때만 그렇다. *Dynamic SQL을 사용하거나 Cursor Variable (=Ref Cursor)을
  사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다는 사실을 명심하기 바란다.*
• PL/SQL 에서는 최대 몇 개 SQL 을 내부적으로 캐싱할까? 오라클 9i까지는 open_cursors 파라미터에
  의해 결정되지만, 10g 부터는 앞 절에서 설명한 session_cache_cursors 파라미터에 의해 결정된다.
  따라서 세션 커서 캐싱 기능을 비활성화하면(session_cached_cursor=0) PL/SQL 의 자동 커서 캐싱
  기능까지 비화성화하므로 주의해야 한다.

-( Dynamic SQL 사용시, 커서 캐싱(10g 이후 )

SQL> alter session set session_cached_cursors = 100 ;
Session altered.
SQL> create table t ( x number );
Table created.
SQL> alter system flush shared_pool ;
System altered.
SQL> declare
2 i number ;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'insert into t values(' \|\| mod(i, 10) \|\| ')';
7 end loop;
8 commit ;
9 end ;
10 /
PL/SQL procedure successfully completed.
SQL> select count(distinct sql_text) sql_cnt
2 , sum(parse_calls) parse_calls
3 , sum(executions) executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
\-------\--\- --\------\--\- --\-------\-
10 100 100
SQL> select distinct sql_text from v$sql where sql_text like 'insert into t values%';
SQL_TEXT
\-------------------------------------------------------------------------------\-
insert into t values(3)
insert into t values(6)
insert into t values(5)
insert into t values(0)
insert into t values(1)
insert into t values(8)
insert into t values(9)
insert into t values(2)
insert into t values(4)
insert into t values(7)
10 rows selected.

• 0부터 9까지의 값을 입력하는 10개의 SQL을 불연속적으로 실행했더니 Parse Call 이 SQL 수행횟수만큼 발생했다.

SQL> alter system flush shared_pool ;
System altered.
SQL> declare
2 i number ;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'insert into t values(' \|\| ceil(i/10)\|\| ')';
7 end loop ;
8 commit ;
9 end ;
10 /
PL/SQL procedure successfully completed.
SQL> select count(distinct sql_text) sql_cnt
2 , sum(parse_calls) parse_calls
3 , sum(executions) executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
\-------\--\- --\------\--\- --\-------\-
10 10 100
SQL> select distinct sql_text from v$sql where sql_text like 'insert into t values%';
SQL_TEXT
\-------------------------------------------------------------------------------\-
insert into t values(3)
insert into t values(6)
insert into t values(5)
insert into t values(10)
insert into t values(1)
insert into t values(8)
insert into t values(9)
insert into t values(2)
insert into t values(4)
insert into t values(7)
10 rows selected.

• 1부터 10까지의 값을 입력하는 10개의 SQL 을 연속적으로 입력했더니 Parse Call 이 SQL 개수만큼만 발생했다.

SQL> alter session set session_cached_cursors = 0 ;
Session altered.
SQL> alter system flush shared_pool ;
System altered.
SQL> declare
2 i number ;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'insert into t values(' \|\| ceil(i/10) \|\|')';
7 end loop ;
8 commit ;
9 end ;
10 /
PL/SQL procedure successfully completed.
SQL> select count(distinct sql_text) sql_cnt
2 , sum(parse_calls) parse_calls
3 , sum(executions) executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
\-------\--\- --\------\--\- --\-------\-
10 100 100
SQL> select distinct sql_text from v$sql where sql_text like 'insert into t values%';
SQL_TEXT
\-------------------------------------------------------------------------------\-
insert into t values(3)
insert into t values(6)
insert into t values(5)
insert into t values(10)
insert into t values(1)
insert into t values(8)
insert into t values(9)
insert into t values(2)
insert into t values(4)
insert into t values(7)
10 rows selected.

• 1부터 10까지의 값을 입력하는 10개의 SQL 을 연속적으로 입력했지만 세션 커서 캐싱 기능을 비활성화시켰더니
  Parse Call 이 SQL 수행횟수만큼 발생했다.
• 최초작성자 : ~darkturtle
• 최초작성일 : 2009년 12월 05일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*