08 어플리케이션 커서 캐싱

세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아서 커서를 오픈할 수 있다. 하지만 세션 커서 캐시에 있는 SQL을 수행하더라도 공유 커서 힙을 Pin하고 실행에 필요한
메모리 공간을 PGA에 할당하는 등의 작업은 반복하게 된다. 이 과정마저 생략하고 빠르게 SQL을 수행하는 방법이 있는데 이를 '애플리케이션 커서 캐싱(Application Cursor Caching)' 이라고 한다.
개발 언어마다 구현방법이 다르므로 이 기능을 활용하려면 API를 잘살펴봐야한다.

Pro*C

• SQL을 수행하는 부분을 아래처럼 두 개 옵션으로 감싸면 커서를 놓지 않고 반복 재사용한다.
• HOLD CURSOR 옵션은 애플리케이션 커서와 세션 커서와의 관계를 다루는 반면 RELEASE_CURSOR 옵션은 공유커서와 세션 커서와의 관계를 다룬다.

for(;;){
    EXEC ORACLE OPTION (HOLD_CURSOR=YES);
    EXEC ORACLE OPTION (RELEASE_CURSOR=NO);
    EXEC SQL INSERT ...... ;      // SQL 수행
    EXEC ORACLE OPTION (RELEASE_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

JAVA

• 묵시적 캐싱 (Implicit Caching) 옵션을 사용하거나 Statement를 닫지 않고 재사용하면 된다.

• 패턴 1: 바인드변수를사용하지 않을때

public static void noBinding(Connection conn, int count)throws Exception{
	PreparedStatement stmt;
	ResultSet rs;

	for(int i = 1; i <= count; i++){
		stmt = conn.prepareStatement(
			"SELECT /* no_binding */" + i + ", " + i + ", 'test', a.* " +
			"FROM emp a WHERE a.ename LIKE 'W%'");
		rs = stmt.executeQuery();

		rs.close();
		stmt.close();
	}
}

• 패턴 2: 바인드 변수를 사용하지만 커서를 캐싱하지 않을 때

public static void noCaching(Connection conn, int count)throws 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.setString(3, "test");
		rs = stmt.executeQuery();

		rs.close();
		stmt.close();
	}
}

• 패턴 3: 커서를닫지 않고 재사용할때

public static void cursorHolding(Connection conn, int count)throws Exception{
	// PreparedStatement를 루프문 바깥에 선언.
	PreparedStatement stmt = conn.prepareStatement(
		"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.setString(3, "test");
		rs = stmt.executeQuery();

		rs.close();
	}

	// 루프를 빠져 나왔을 때 커서를 닫는다.
	stmt.close();
}

• 패턴 4: 묵시적 캐싱 기능을사용할때 -

public static void cursorCaching(Connection conn, int count)throws Exception{

	// 캐시 사이즈를 1로 지정
	((OracleConnection)conn).setStatementCacheSize(1);

	// 묵시적 캐싱 기능을 활성화
	((OracleConnection)conn).setImplicitCachingEnabled(true);

	for(int i = 1; i <= count; i++){
		PreparedStatement stmt = conn.prepareStatement(
			"SELECT /* implicit_caching */ ?, ?, ?, a.* " +
			"FROM emp a WHERE a.ename LIKE 'W%'");
		stmt.setInt(1, i);
		stmt.setInt(2, i);
		stmt.setString(3, "test");
		ResultSet rs = stmt.executeQuery();

		rs.close();

		// 커서를 닫지만 내부적으로는 닫히지 않은 채 캐시에 보관
		stmt.close();
	}
}

PL/SQL

• 자동적으로 커서를 캐싱한다. 단, Static SQL을 사용할 때만 그렇다. Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다

SQL> alter session set session_cached_cursors = 100; 

SQL> create table t ( x number); 

SQL> alter system flush shared_pool; 

SQL> declare 
     i number; 
     begin 
       for i in 1..100 
       loop 
         execute immediate 'insert into t values(' || mod(i, 10) || ')'; 
       end loop; 
     
     commit; 
     end;  

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> declare 
     i number; 
     begin 
       for i in 1..100 
       loop 
         execute immediate 'insert into t values(' || ceil(i/10) || ')'; 
       end loop; 
     
     commit; 
     end; 



SQL> select count(distinct sql_text) sql_cnt 
          , sum(parse_calls) parse_calls 
          , sum(executions) executions 
       from v$sql 
      where sql_text like 'insert into t values%'; 

  SQL_CNT PARSE_CALLS EXECUTIONS 
--------- ----------- ----------
       10          10        100         
       

SQL> alter session set session_cached_cursors = 0; 

SQL> alter system flush shared_pool; 

SQL> declare 
     i number; 
     begin 
       for i in 1..100 
       loop 
         execute immediate 'insert into t values(' || ceil(i/10) || ')'; 
       end loop; 
     
     commit; 
     end; 
     
SQL> select count(distinct sql_text) sql_cnt 
          , sum(parse_calls) parse_calls 
          , sum(executions) executions 
       from v$sql 
      where sql_text like 'insert into t values%'; 

  SQL_CNT PARSE_CALLS EXECUTIONS 
--------- ----------- ----------
       10         100        100