<오라클 성능 고도화 원리와 해법1> Ch04-08 애플리케이션 커서 캐싱
오라클 성능 고도화 원리와 해법1 - Ch04-08 애플리케이션 커서 캐싱
세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아서 커서를 오픈할 수 있다. 하지만 세션 커서 캐시에 있는 SQL을 수행하더라도 공유 커서 힙을 Pin 하고 실행에 필요한 메모리 공간을 PGA에 할당하는 등의 작업은 반복하게 된다. 이 과정마저 생략하고 빠르게 SQL을 수행하는 방법이 있는데, 이를 ‘애플리케이션 커서 캐싱42)(Application Cursor Caching)’이라고 한다. 개발 언어마다 구현 방법이 다르므로 이 기능을 활용하려면 API를 잘 살펴봐야 한다.
Pro*C에서는 SQL을 수행하는 부분을 아래처럼 두 개 옵션으로 감싸면 커서를 놓지 않고 반복 재사용한다.
HOLD_CURSOR 옵션은 애플리케이션 커서와 세션 커서와의 관계를 다루는 반면, RELEASE_CURSOR 옵션은 공유 커서와 세션 커서와의 관계를 다룬다.
일반적으로는 Execute Call 횟수만큼 Parse Call이 반복되지만 애플리케이션 커서 캐싱 기능을 이용하면 공유 커서를 Pin 한 채 반복 수행하므로 Parse Call이 최초 한 번만 발생하고 이후로는 발생하지 않는다. 아래는 애플리케이션에서 커서를 캐싱한 채 같은 SQL을 5,000번 반복 수행했을 때의 SQL 트레이스 결과다.
Parse Call이 한 번만 발생했고, 이후 4,999번 수행할 때는 Parse Call이 전혀 발생하지 않았음을 알 수 있다. (최초 Parse Call이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드 파싱을 수행한 사실도 라이브러리 캐시 Miss 항목을 통해 읽을 수 있어야 한다.)
JAVA에서 이를 구현하려면 묵시적 캐싱(Implicit Caching) 옵션을 사용하거나 Statement를 닫지 않고 재사용하면 된다. 구체적인 사용법을 익혀보자. SQL을 아래 4가지 패턴으로 작성하고, 각각에 대한 수행 속도를 비교해볼 것이다.
- 패턴 1: 바인드 변수를 사용하지 않을 때
- 패턴 2: 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때
- 패턴 3: 커서를 닫지 않고 재사용할 때
- 패턴 4: 묵시적 캐싱 기능을 사용할 때
단지 프로그램 개발 패턴만 바꿨을 뿐인데 뚜렷한 성능 차이를 보이고 있다. JAVA 프로그램으로 테스트했기 때문에 네트워크를 통한 Roundtrip 영향이 커, 파싱에 의한 부하는 상대적으로 작게 나타난 점을 감안해야 한다. 같은 테스트를 PL/SQL에서 수행한다면 성능 차이는 더 확연히 드러난다.
PL/SQL에서는 위와 같은 옵션을 별도로 적용하지 않아도 자동적으로 커서를 캐싱한다. 단, Static SQL을 사용할 때만 그렇다. Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다는 사실을 명심하기 바란다.
그렇다면, PL/SQL에서는 최대 몇 개의 SQL을 내부적으로 캐싱할까? 오라클 9i까지는 open_cursors 파라미터에 의해 결정되었지만, 10g부터는 앞 절에서 설명한 session_cached_cursors 파라미터에 의해 결정된다. 따라서 세션 커서 캐싱 기능을 비활성화하면(session_cached_cursors=0) PL/SQL의 자동 커서 캐싱 기능까지 비활성화되므로 주의해야 한다.
아래는 어떤 회사에서 수집한 AWR 리포트 중 Instance Efficiency 부분만을 발췌한 것이다.
이 중 Execute to Parse 항목을 주목하기 바란다. 이는 아래 공식으로 구해진 값이다. (3장에서 이미 다룬 바 있다.)
Execute to Parse = (1 - parse/execute)*100
즉, Parse Call 없이 SQL을 수행한 횟수를 의미한다. 그런데 n-Tier 환경에서는 DB Connection을 사용하고 나서 곧바로 커넥션 풀에 반환하므로 애플리케이션에 커서 캐싱 기법을 제대로 활용하기가 쉽지 않다. 따라서 요즘 같은 웹 애플리케이션 환경에서는 대개 이 항목이 50% 미만의 낮은 수치를 보이기 마련이다. 그런데도 위처럼 89.31%로 비교적 높게 나타난 이유는 무엇일까?
이는 PL/SQL로 작성한 함수/프로시저를 적극적으로 많이 사용한 시스템에서 자주 나타나는 현상이다. 이처럼 애플리케이션 커서 캐싱 기법을 잘 활용하면 라이브러리 캐시 효율에 매우 긍정적인 효과를 가져다준다.