08 애플리케이션 커서 캐싱

   애플리케이션 커서 캐싱이란?

• 오라클 공식 용어는 아니며, Parse Call을 발생시키지 않고 SQL을 반복 수행하는 기능에 대해 필자(조시형)가 붙인 이름입니다.
  1. 세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아서 커서를 오픈할수 있습니다.
  2. 세션 커서 캐시에 있는 SQL을 수행 하더라도 공유 커서 힙을 Pin하고 실행에 필요한 메모리
     공간을 PGA에 할당하는 등의 작업은 반복하게 됩니다.
  3. 이 과정마저 생략하고 빠르게 SQL을 수행하는 방법이 있는데, 이를 `애플리케이션 커서 캐싱`이라고 합니다.
  4. 개발 언어마다 구현 방법이 다르므로 이 기능을 활용하려면 API를 잘 살펴봐야합니다.

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

 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 옵션은 애플리케이션 커서와 세션 커서와의 관계를 다루는 반면
  RELEASE_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에서 이를 구현하려면 묵시적 캐싱(Implicit Caching) 옵션을 사용하거나 Statement를 닫지 않고 재사용하면 됩니다.
  SQL을 아래 4가지 패턴으로 작성하고, 각각에 대한 수행속도를 비교해 보겠습니다.
  • 패턴1 : 바인드 변수를 사용하지 않을때
  • 패턴2 : 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때
  • 패턴3 : 커서를 닫지 않고 재사용할 때
  • 패턴4 : 묵시적 캐싱 기능을 사용할 때
• 아래는 각 패턴별로 작성된 메서드를 호출하는 main() 메서드 부분입니다.

 public static void main(String[] arr) throws SQLException, Exception{
	// (1) Bind 변수를 사용하지 않을 때
	noBinding(connMgr.getConnectionMethod(), 5000);

	// (2) Bind 변수를 사용하지만, Caching 옵션을 사용하지 않을 때
	noCaching(connMgr.getConnectionMethod(), 5000);

	// (3) Cursor를 닫지 않고 반복적으로 재사용할 때
	cursorHolding(connMgr.getConnectionMethod(), 5000);

	// (4) Caching 옵션을 사용할 때
	cursorCaching(connMgr.getConnectionMethod(), 5000);
}

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

• 아래부터 나오는 JAVA 소스는 교재의 내용을 그대로 표기 했으며, 실제 테스트를 위한 전체 소스는 첨부파일을 확인하여 테스트가 가능합니다.

 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();
	}
}

• 바인드 변수를 사용하지 않았으므로 매번 하드 파싱을 반복 수행하게 됩니다.
  아래는 SQL 트레이스에서 처음 2번 실행한것과 맨 마지막 것만을 추출한 것입니다.
• 테스트를 해보니 첫번째 Parse시에 elapsed 값이 0.01을 보였던거 외에는 5000번을 수행할때까지 동일한 결과가 나왔음을 확인 했습니다.
  한가지 더 비교 하자면 교재에서는 2로 표기되었던 Fetch시에 query 부분의 값이 제가 테스트를 했을때는 모두 7로 나오는걸 확인했습니다.

********************************************************************************

SELECT /* no_binding */1, 1, 'test', a.*
FROM
 emp a WHERE a.ename LIKE 'W%'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.01          0          7          0           1

Misses in library cache during parse: 1
********************************************************************************

SELECT /* no_binding */2, 2, '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          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
********************************************************************************
.
.
. 5000번까지 동일
********************************************************************************

SELECT /* no_binding */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          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
********************************************************************************

(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();
	}
}

• 따라서 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.00       0.28          0          0          0           0
Execute   5000      0.01       0.84          0          0          0           0
Fetch     5000      0.00       1.11          0      35000          0        5000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    15000      0.01       2.24          0      35000          0        5000

Misses in library cache during parse: 1
********************************************************************************

(3) 커서를 닫지 않고 재사용할 때

• 이번에는 아래처럼 PreparedStatement를 루프문 바깥에 선언하고 루프 내에서 반복 사용하다가 루프를 빠져 나왔을 때 닫습니다.
  JAVA PreparedStatement 객체가 앞에서 설명했던 `애플리케이션 커서`에 해당합니다.

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();
}

• 아래 트레이스 결과를 보면, 앞에서 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        1      0.00       0.00          0          0          0           0
Execute   5000      0.01       1.43          0          0          0           0
Fetch     5000      0.01       0.70          0      35000          0        5000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      0.02       2.15          0      35000          0        5000

Misses in library cache during parse: 1
********************************************************************************

(4) 묵시적 캐싱 기능을 사용할 때

• 마지막으로, PreparedStatement를 루프문 안쪽에 선언하고 루프 내에서 쿼리를 수행 하자마자 곧바로 닫습니다.
  하지만 setStatementCacheSize를 0보다 크게 설정하고, setImplicitCachingenabled 메소드를 true로 설정하였습니다.

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();
	}
}

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

********************************************************************************

SELECT /* implicit_caching */ :1, :2, :3, a.*
FROM
 emp a WHERE a.ename LIKE 'W%'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.04          0          0          0           0
Execute   5000      0.01       1.35          0          0          0           0
Fetch     5000      0.00       0.68          0      35000          0        5000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      0.01       2.08          0      35000          0        5000

Misses in library cache during parse: 1
********************************************************************************

• 단지 프로그램 개발 패턴만 바꿨을 뿐인데 뚜렷한 성능차이를 보이고 있습니다.
  java 프로그램으로 테스트했기 때문에 네트워크를 통한 Roundtrip 영향이 커,
  파싱에 의한 부하는 상대적으로 작게 나타난 점을 감안해야합니다.
  같은 테스트를 PL/SQL에서 수행한다면 성능 차이는 더 확연히 드러납니다.
• PL/SQL에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱합니다.
  단, Static SQL을 사용할 때만 그렇습니다. Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는
  커서를 자동으로 캐싱하는 효과가 사라진다는 사실을 명심하기 바랍니다.
• 그렇다면, PL/SQL에서는 최대 몇 개 SQL을 내부적으로 캐싱할까? 오라클 9i까지는 open_cursors 파라미터에 의해 결정됩니다.
  따라서 세션 커서 캐싱 기능을 비활성화하면(session_cached_cursor=0) PL/SQL의 자동 커서 캐싱 기능까지 비활성화되므로 주의 해야 합니다.
• 테스트 내용을 확인해 봅니다.

•   아래는 어떤 회사에서 수집한 AWR 리포트 중 Instance Efficiency 부분만을 발췌한 것입니다.

 Instance Efficiency Percentages (Target 100%)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         Buffer Nowait %:  99.98    Redo NoWait %:   100.00
         Buffer   Hit  %:  97.93 In-memory Sort %:   100.00
         Library  Hit  %:  99.55     Soft-Parse %:    98.34
      Execute to Parse %:  89.31      Latch Hit %:    98.67
Parse CPU to Parse Elapsd %: 61.11  % Non-Parse CPU:  97.58

• 이 중 Execute to Parse 항목을 주목하기 바랍니다. 이는 아래 공식으로 구해진 값입니다.(3장에서 이미 다룬 바가 있습니다. )
  Execute to Parse = (1 - parse/execute)*100

• 즉, Parse Call 없이 SQL을 수행한 횟수를 의미합니다.
  그런데 n-Tier 환경에서는 DB Connection을 사용하고 나서 곧바로 커넥션 풀에 반환 하므로 애플리케이션에 커서 캐싱 기법을 제대로 활용하기가 쉽지 않습니다.
  따라서 요즘 같은 웹 애플리케이션 환경에서는 대개 이 항목이 50% 미만의 낮은 수치를 보이기 마련입니다.
  그런데도 위처럼 89.31%로 비교적 높게 나타난 이유는 PL/SQL로 작성한 함수/프로시저를 적극적으로 많이 사용한 시스템에서 자주 나타나는 현상입니다.
  이처럼 애플리케이션 커서 캐싱 기법을 잘 활용하면 라이브러리 캐시 효율에 매우 긍정적인 효과를 가져다 줍니다.

참조 문서

이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.

첨부 자료

Parse Call 테스트(JAVA)