커서공유

(1) 커서란?

- 공유 커서(shared cursor) : 라이브러리 캐시에 공유돼 있는 Shared SQL Area
- 세션 커서(session cursor) : Private SQL Area에 저장된 커서
- 애플리케이션 커서(application cursor) : 세션 커서를 가리키는 핸들

공유 커서 (shared cursor)
- JAVA, VB, Pro*C, PL/SQL 등에서 SQL을 수행하면, 서버 프로세스는 해당 SQL이
  라이브러리 캐시에 공유돼 있는지를 먼저 확인하며，최적화 과정을 통해 실행계
  획을 만들고，라이브러리 캐시에 공유돼 있는 Shared SQL Area를 '커서'라고 한다.

세션 커서 (session cursor)
- 라이브러리 캐시에 공유돼 있는 커서를 실행할 때는 우선 PGA영역에 메모리를 할당한다. 이를 'Private SQL Area' 라고 하며, Persistent Area와 Runtime Area로 나뉜다.
- Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SAL Area 에 담고, 공유 커서를 가리키는 포인터를 유지한다. 그리고 커서의 상태 정보도 관리한다.
- PGA에 저장된 커서종보(즉, 파싱된 SQL문과 문장을 수행하는데 필요한 기타 정보)를 또한 '커서'라고 부른다.
- 커서를 오픈하면 라이브러리 캐시에 공유돼 있는 커서를 인스턴스화함으로써 PGA에 커서를 위한 메모리 공간(Persistent Area와 Runtime Area)을 할당하고, 실제 데이터 추출을 시작할 수 있도록 준비작업을 해야한다.

애플리케이션 커서 (application cursor)
- PGA에 있는 커서를 핸들링하려면 JAVA, VB, Pro*C, PL/SQL 같은 클라이언트 애플리케이션에도 리소스를 할당해야한다.

(2)커서 공유

- 오라클에서 "커서를 공유하다"는 표현을 자주하는데 이는 라이브러리 캐시의 공유 커서를 일컫는다.
- 라이브러리 캐시에 공유돼 있는 커서의 수행 통계를 v$sql을 통해 조회해 볼수 있다.

- parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려고 요청한 횟수.
- loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수.
- executions : SQL을 수행한 횟수.
- invalidations : 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함.

다른 세션끼리도 같은 커서를 공유해서 재사용할 수 있는지 확인하자.

커서를 공유하지 못하는 경우
- 오브젝트에 컬럼이 추가/삭제될 경우!21.jpg!
- 새로운 인덱스가 만들어길 경우
- 오브젝트 통계를 새로 수지하는 경우

라이브러리 캐시에 있는 커서들이 여러 세션에 의해 공유되면서 반복 재사용되는 것을 직접 확인해 보았다. 공유된 커서를 사용할 때는 최적화 및 Row-Source Generation 단계를 생략하고 곧바로 실행 단계로 넘어가므로 보다 효율적이고 빠르게 SQL을 수행한다고 설명한다.
커서가 공유되려면 커서를 식별하는 키 값이 같아야 되는데, 이 식별자는 'SQL 문장'임(V$SQL.SQL_FULLTEXT)

(3) Child 커서를 공유하지 못하는 경우

SQL 문장이 100% 동일한데도 SQL 커서를 공유하지 못하고 별도의 SQL 커서를 생성해야 할 때가 있으며, 만약 EMP 테이블이 SCOTT과 HR 스키마에 각각 생성되어 있다면 SCOTT과 HR로 로긴할 때 다른 테이블을 액세스해야 하고 실행계획도 달라져야 하며 이 때, Child Cursor를 생성한다
영구적으로 보관되는 Stored Object(테이블, 인덱스, 함수, 프로시저, 패키지 등)는 이름으로 식별이 가능하므로 Child 오브젝트가 필요없지만, SQL 커서는 Transient Object는 전체 문자열이 LCO를 식별하는 식별자이므로 이에 따라 Child 커서가 생성될 수 있다.
V$SQLAREA는 Parent 커서 정보를 보여주고, V$SQL은 Child 커서를 보여준다. (V$SQLAREA는 V$SQL을 GROUP BY 한 결과임)

아래는 옵티마이저 모드를 변경하여 테스트 한 결과임

SQL 하나당 여러 개의 Child 커서를 갖는 것은 바람직한 현상은 아님.
VERSION_COUNT 수치가 높은 SQL일수록 커서를 탐색하는 데 더 많은 시간을 소비하므로 라이브러리 캐시 래치에 대한 경합 발생 가능성을 증가시킴.

하나의 SQL 문장에 여러 개 Child 커서를 갖게 되는 이유
- SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
- 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용중(pin)일 때
- 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
- 입력된 바인드 값의 길이가 크게 다를 때
- NLS 파라미터를 다르게 설정했을 때
- SQL 트레이스를 활성화했을 때

(4) Parent 커서를 공유하지 못하는 경우

공백 문자 또는 줄바꿈
대소문자 구분
테이블 Owner 명시
주석(Comment)
옵티아져 힌트 사용
조건절 비교 값

- 이 중 가장 라이브러리 캐시 효율과 직접적으로 관련된 예는 6)번 예제임
- 이와 같이 조건절에 바인드 변수를 사용하지 않고 서로 다른 Literal 값으로 문자열을 사용하게 되면 이는 시스템을 장애 상황으로 몰고 갈 수 있다.