by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

애플리케이션 커서 캐싱




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

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

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 항목을 통해 읽을 수 있어야 한다. )

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 수행횟수만큼 발생했다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.