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

• 오라클 공식 용어는 아니며, Parse Call을 발생시키지 않고 SQL을 반복 수행하는 기능에 대해 오라클 성능 고도화의 저자가 붙인 이름이다.

(1) 세션 커서 캐싱

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

(2) 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 옵션은 공유커서와 세션 커서와의 관계를 다룬다.

(3) ⭐️애플리케이션 커서의 실행 Call 횟수⭐️

• ⭐️일반적으로는 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 항목을 통해 읽을 수 있어야 한다.

[2] JAVA에서의 커서 캐싱

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

[JAVA에서의 커서 캐싱 예제 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(); } }

• 바인드 변수를 사용하지 않았으므로 매번 하드 파싱을 반복 수행한다.

[JAVA에서의 커서 캐싱 예제 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
********************************************************************************

[JAVA에서의 커서 캐싱 예제 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();
}

• 앞에서 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
********************************************************************************

[JAVA에서의 커서 캐싱 예제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
********************************************************************************

[3] ⭐️ PL/SQL에서의 커서 캐싱

• ⭐️PL/SQL에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱한다.⭐️
  • 단, Static SQL을 사용할 때만 해당된다!!
  • Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다.
• PL/SQL에서 오라클 9i까지는 open_cursors 파라미터에 의해 최대 몇 개 SQL을 내부적으로 캐싱할까가 결정되기 때문에 세션 커서 캐싱 기능을 비활성화하면(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 항목
  • Execute to Parse = (1 - parse/execute)*100
  • 즉, Parse Call 없이 SQL을 수행한 횟수를 의미한다.
• n-Tier 환경에서는 DB Connection을 사용하고 나서 곧바로 커넥션 풀에 반환 하므로 애플리케이션에 커서 캐싱 기법을 제대로 활용하기가 쉽지 않다.
  따라서 요즘 같은 웹 애플리케이션 환경에서는 대개 이 항목이 50% 미만의 낮은 수치를 보이기 마련인데 위처럼 89.31%로 비교적 높게 나타난 이유는 ⭐️PL/SQL로 작성한 함수/프로시저를 적극적으로 많이 사용한 시스템에서 자주 나타나는 현상⭐️이다.

[4] 결론

⭐️애플리케이션 커서 캐싱 기법을 잘 활용하면 라이브러리 캐시 효율에 매우 긍정적인 효과를 가져다 준다!⭐️

• 파싱 부하의 감소 덕분 ^^