메뉴 건너뛰기

bysql.net

8._애플리케이션_커서_캐싱

2012.04.21 17:48

박영창 조회 수:5438

Chapter 04. 라이브러리 캐시 최적화 원리
08. 애플리케이션 커서 캐싱

* 개요
Session Cursor Caching 기법을 이용하더라도 Parsing 단계의 부하를 감소를 시킬뿐
공유 커서의 Heap 을 Pin 등록하고 실행 메모리 공간을 PGA에 할당하는 등의 작업은 반복됨.

이 부분을 개선할 수 있는 기법이 Application Cursor Caching 기법임.
※ 언어마다 구현 방법이 다르므로 해당 언어의 API를 잘 살펴야함.


Proc*C 에서의 적용 방법
for(;;) {
EXEC ORACLE OPTION (HOLD_CURSOR=YES);
EXEC ORACLE OPTION (RELEASE_CURSOR=NO);
EXEC SQL INSERT .... ; // SQL 수행
EXEC ORACLE OPTION (RELEASE CURSOR=YES);
}


HOLD_CURSOR : Application cursor , Session cursor 관계를 다룸.
RELEASE_CURSOR : Shared cursor, Session cursor 관계를 다룸.

일반적으로는 Execute Call 횟수 만큼 Parse Call 이 반복되지만 이 기능을 이용하면 공유 커서를 Pin 한채로 반복 수행하기 때문에
Parse Call 이 최초 한번만 발생하고 이후로는 발생하지 않음.
*

※ 어플리케이션에서 해당 기능을 ON 시키고 5000번 반복 수행 했을때의 SQL 트레이스 결과


Parse Call 이 최초 한번만 발생하고 이후 4999번 수행할때에는 Parse Call 이 전혀 발생하지 않았음.
-> 최초 Parse 단계에서 library cache 에서 해당 SQL을 찾지못해 miss 하드 파싱을 수행한 사실도 miss 항목을 통해 확인 가능.


Java 에서의 적용 방법

Implicit Cache (묵시적 캐싱) 옵션을 사용하거나 Statement 를 닫지 않고 재사용

* 패턴 1: 바인드 변수를 사용하지 않을 때
public static void noBinding(Connection conn, int count) throws Exception {
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
for (int i = 1 ; i <= count; i++ ) {
sql = "SELECT /* no_biding */ " + i + "," + i + ",'test',a.* FROM scott.emp a WHERE a.ename LIKE 'W%'";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
rs.close();
stmt.close();
}
}

-> Bind 변수를 이용해서 SQL 을 수행하지 않았기 때문에 매번 Hard Parsing 하게됨.
-> count 변수의 숫자 만큼의 Hard Parsing 발생



* 패턴 2 : 바인드 변수를 사용하지만, 커서를 캐싱하지 않을때
public static void noCaching(Connection conn, int count) throws Exception {
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
for (int i = 1 ; i <= count; i++ ) {
sql = "SELECT /* no_biding */ ?,?,?,a.* FROM scott.emp a WHERE a.ename LIKE 'W%'";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, i);
stmt.setInt(2, i);
stmt.setString(3, "test");
rs = stmt.executeQuery();
rs.close();
stmt.close(); // 커서를 닫음.
}
}

-> Bind 변수를 이용하기 때문에 SQL은 한개만 사용해서 수행. Bind 값만 변경됨.
-> 하드 파싱은 최초 1회만 수행됨.
-> Loop 내에서 커서를 닫기 때문에 항상 커서를 생성함.



* 패턴 3 : 커서를 닫지 않고 재사용할 때
public static void cursorHolding(Connection conn, int count) throws Exception {
PreparedStatement stmt = null;
String sql = "SELECT /* no_biding */ ?,?,?,a.* FROM scott.emp a WHERE a.ename LIKE 'W%'";
stmt = conn.prepareStatement(sql);
ResultSet rs = null;
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(); // 루프가 끝나면 커서를 닫는다.
}

-> Bind 변수를 이용하기 때문에 SQL은 한개만 사용해서 수행. Bind 값만 변경됨.
-> 하드 파싱은 최초 1회만 수행됨.
-> 커서를 Loop 밖에서 닫음. 이것을 어플리케이션 커서라고 부름. Parse Call 이 1회만 발생함.



* 패턴 4 : 묵시적 캐싱 기능을 사용할 때
public static void cursorCaching(Connection conn, int count) throws Exception {
// 캐시 사이즈를 1로 지정
((OracleConnection) conn).setStatementCacheSize(1);

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

PreparedStatement stmt = null;
ResultSet rs = null;
for (int i = 1 ; i <=count; i++ ) {
String sql = "SELECT /* no_biding */ ?,?,?,a.* FROM scott.emp a WHERE a.ename LIKE 'W%'";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, i);
stmt.setInt(2, i);
stmt.setString(3, "test");
rs = stmt.executeQuery();
rs.close();
stmt.close(); // 루프가 끝나면 커서를 닫는다. 그러나, 내부적으로는 닫지않고 캐시에 보관
}
}

-> Bind 변수를 이용하기 때문에 SQL은 한개만 사용해서 수행. Bind 값만 변경됨.
-> 하드 파싱은 최초 1회만 수행됨.
-> 커서를 Loop 안에서 닫음. 하지만 내부적으로는 닫지 않고 캐시에 보관함.
-> 패턴2의 형태를 유지하면서도 패턴3의 형태로 동작하도록 만들어준다. (내부적으로는 세션 커서 캐싱)



정리

Java 로 작성한 예제에서는 네트워크 layer 의 roundtrip 영향이 크기때문에 파싱에 의한 부하는 상대적으로 작게 나타난 점을 감안.
PL/SQL에서 수행하면 성능 차이는 확연함.
PL/SQL에서는 Static SQL 을 사용할 경우 자동으로 커서를 캐싱. Dynamic SQL, Cursor Variable (=Ref Cursor)를 사용할 경우 캐싱하는 효과는 사라짐.

최대 캐싱 SQL 숫자는 9i 에서는 open_cursors 파라메터가 10g 부터는 session_cached_cursors 파라미터가 결정함.
session_cached_cursors = 0 으로 세션 커서 캐싱을 비활성화 할 경우 PL/SQL 커서 캐싱도 비활성화 되므로 주의 필요,

이 게시물을
목록
목록
쓰기
태그