커서공유

(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 커서를 공유하지 못하는 경우

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

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