Application cursor caching

5. Application cursor caching

# cursor 란?
총 3가지 종류
1. 공유 커서 (shared cursor) : library cache에 공유되어 있는 Shared SQL Area   <-- 알티베이스의 SQL Plan cache와 매칭
2. 세션 커서 (session cursor) : 오라클의 server process 상의 PGA 영역에 속한 Private SQL Area에 저장된 커서
3. 애플리케이션 커서 ( application cursort ) : java나 visual basic과 같은 프로그램 단에서 세션 커서를 가리키는 핸들




<공유 커서 (shared cursor) >
- 다른 세션과 공유
- 수행되는 SQL이 이미 library cache 상에 공유되어 있다면, Hard parsing이 발생하지 않는다.


< 세션 커서 (session cursor) >
- 다른 세션과 공유 안됨.
- shared cursor를 실행할 때 server process 상의 PGA 영역에 Private SQL Area 할당하여 shared cursor를 가리키는 포인터 생성
- Private SQL Area는 다시 Persistent Area(바인드 변수 저장, cursor close 후 해제),
  Runtime Area(쿼리문 저장) 으로 나뉨
- cursor를 오픈한다고 함은 library cache상의 shared cursor를 참조하여 PGA에 cursor를 위한 Private SQL Area를 할당하고, 실제 데이터 추출을 시작할 수 있도록 하는 준비작업을 말함.

# 새션 커서 캐싱
-  쿼리 수행을 종료하면 cursor가 close되면서 Private SQL Area는 해제되고, shared cursor를 가리키는 포인터도 해제
- 이후 동일한 쿼리 수행시  다시 session cursor를 오픈하기 위해 shared cursor를 탐색해야 되므로,
이에 대한 부하를 줄이고자 세션 커서를 캐싱함.(session_cached_cursors 파라미터를 0이상으로 설정)




< 애플리케이션 커서 (application cursor) >
- 다른 세션과 공유 안됨.
- PGA에 있는 session cursor를 핸들링 위한 포인터


# Application cursor caching
- 최초 한번만 parse 과정을 거치고, 이후 추가적인 parse call이 발생하지 않는다.

< 예시 >

- Pro*C
 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

 1) Hold cursor 옵션은 application cursor와 session cursor 관계를 다룸
 2) release cursor 옵션은 shared cursor와 session cursor와 관계를 다룸


 
- JAVA의 묵시적 캐싱 (Implicit caching)
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를 루프문 안쪽에 선언
PreparedStatement stmt = conn.prepareStatement( "SELECT ?,?,?,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();
}

}


- cursor를 닫지 않고 재사용 : 루프문 바깥에 PreparedStatement 선언하고, 루프문 이후 cursor close

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

// PreparedStatement를 루프문 바깥에 선언
PreparedStatement stmt = conn.prepareStatement( "SELECT ?,?,?,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();
}


PL/SQL에서는 자동으로 커서를 캐싱하지만, Static SQL 일때만 그렇다.
Dynamic SQL이더라도 session_cached_cursor를 0이상 설정하면 경우에 따라 커서를 캐싱한다
(오라클 성능 고도화 1권 304 페이지 참고)

REF cursor는 어떤 경우에도 캐싱 효과를 볼 수 없다.

REF cursor란?