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

07. 세션 커서 캐싱

커서를 공유할 수 있는 형태로 SQL을 작성하면 하드파싱을 최소화해 궁극적으로 시스템 확장성을 높일 수 있습니다.
그런데 하드파싱을 하지 않더라도 SQL구문을 분석해서 해시 값을 계산하고, library cache래치를 획득한 후 라이브러리 캐시에서 커서를 탐색하는 과정 자체도 부담스러운 작업입니다.(소프트파싱) 특히 SQL 동시 수행이 많을 때는 경합까지 발생하므로 시스템에 부하를 주게 됩니다.
당연한 이야기이지만 SQL수행횟수가 높을 때 파싱관련 경합도 함께 증가합니다. 하지만 파싱경합을 줄여서 라이브러리 캐시 효율을 높이기 위해서는 SQL수행횟수를 줄여야 할까요? 아닙니다. 이것은 근본적 해결은 아닙니다.
Shared Pool에 위치한 공유 커서를 실행하려고 PGA로 인스턴스화 한것이 세션 커서입니다.쿼리를 수행한 후 커서를 닫으면 세션 커서를 위해 할당된 메모리는 물론 공유 커서를 가리키는 포인터까지 바로 해제됩니다. 그 다음에 같은 SQL을 수행하면 커서를 오픈하기 위한 라이브러리 캐시 탐색작업을 다시 시작해야 합니다. 이에 오라클은 자주 수행하는 SQL에 대한 세션 커서를 세션 커서 캐시에 저장할 수 있는 기능을 제공하는데 ‘세션 커서 캐싱’이라고 합니다.
이 기능을 활성화하면 커서를 닫는 순간 커서의 Parse Call 횟수를 확인해 보고 3보다 크거나 같으면 세션 커서를 세션 커서 캐시로 옮깁니다. 세션 커서 캐시에는 SQL텍스트와 함께 공유 커서를 가리키는 포인터를 저장합니다. 커서는 닫힌 상태지만 공유 커서에 대한 참조를 유지하기 때문에 다음 수행 시 더 빨리 커서를 오픈할 수 있습니다.(따라서 자주 수행되는 SQL에 발생하는 라이브러리 캐시 부하를 줄일 수 있습니다)
세션 커서 캐시 내에서도 LRU알고리즘을 사용함으로써 새로운 엔트리를 위한 공간이 필요할 때마다 기존 세션 커서 중 사용빈도가 낮은 것부터 밀어냅니다.
session_cached_cursors는 얼마나 많은 세션 커서를 캐싱할지를 지정하는 파라미터로써 이 값을 0보다 크게 하면 Parse Call이 발생할때마다 라이브러리 캐시를 탐색하기 전에 세션 커서 캐시를 먼저 살펴봅니다. 거기서 커서를 찾으면 라이브러리를 탐색하지 않고도 곧바로 공유 커서를 찾아 커서를 오픈할 수 있습니다.
(v$sql 의 users_opening(세션 커서 캐시로 옮겨진 세션 커서의 수)와 users_executing(해당 SQL을 현재 실행중인 세션 커서의 수)로 작동 원리를 파악할 수 있습니다.)
세션 커서 캐싱기능은 Parse Call을 대체하기보다 Parse Call 부하를 감소시키는 기능입니다.
이 파라미터는 세션 레벨에서도 변경이 가능하므로 정해진 개수의 SQL을 반복 수행하는 프로그램 모듈에만 설정 값을 늘려주더라도 라이브러리 캐시 부하를 줄이는 데 도움이 됩니다.

08.애플리케이션 커서 캐싱

세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아서 커서를 오픈할 수 있습니다. 하지만 세션 커서 캐시에 있는 SQL을 수행하더라도 공유 커서 힙을 Pin하고 실행에 필요한 메모리 공간을 PGA에 할당하는 등의 작업은 반복하게 됩니다. 이 과정마저 생략하고 빠르게 SQL을 수행하는 방법이 있는데 이를 ‘애플리케이션 커서 캐싱’이라고 합니다. 개발언어마다 구현방법이 다르므로 이 기능을 활용하려면 API를 잘 살펴봐야 합니다.
일반적으로 Execute Call 횟수만큼 Parse Call이 반복되지만 애플리케이션 커서 캐싱 기능을 이용하면 공유 커서를 Pin한채 반복 수행하므로 Paese Call은 최초 한번만 일어나고 그 이후로는 발생하지 않습니다.

JAVA에서 이를 구현하려면 묵시적 캐싱옵션을 사용하거나 Statement를 닫지 않고 재사용하면됩니다.

PL/SQL에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱합니다. 단 static SQL을 사용할 때만 그렇습니다. Dynamic SQL을 사용하거나 Cursor Variable을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라집니다.
10g부터는 앞절에서 설명한 session_cached_cursors 파라미터에 의해 최대 몇개 SQL을 내부적으로 캐싱할지 결정합니다. 세션 커서 캐싱을 비활성화하면 PL/SQL의 자동 커서 캐싱기능까지 비활성화 되므로 주의하여야 합니다.