04.라이브러리 캐시 최적화 원리 - 04.커서 공유

(1)커서란?
우리가 흔히 말하는 커서는 아래 3가지를 모두 일컫는 말입니다.
공유 커서(shared cursor): 라이브러리 캐시에 공유 돼 있는 shared SQL area
세션 커서(session cursor): Private SQL area에 저장된 커서
애플리케이션 커서(application cursor): 세션 커서를 가리키는 핸들

공유커서
java,vb,pro*c,pl/sql 등에서 sql을 수행하면 서버 프로세스는 해당 sql이 라이브러리 캐시에 공유돼 있는지를 먼저 확인합니다. 없으면 최적화 과정을 통해 실행계획을 만들고, 라이브러리 캐시에 공유합니다. 그렇게 라이브러리 캐시에 공유돼 있는 shared SQL area를 커서라고 부릅니다.
세션커서
라이브러리 캐시에 공유돼 있는 커서를 실행할 때는 우선 PGA영역에 메모리를 할당합니다. 이를 private SQL area라고 합니다.
Shared SQL area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SQL area에 담고, 공유 커서를 가리키는 포인터를 유지합니다.
그리고 커서의 상태정보도 관리합니다. PGA에 저장된 커서정보(파싱된 sql문과 문장을 수행하는데 필요한 기타 정보)를 또한 커서라고 부릅니다.
애플리케이션 커서
PGA에 있는 커서를 핸들링하려면 JAVA,VB,Pro*C,PL/SQL같은 클라이언트 애플리케이션에도 리소스를 할당해야 하는데 이또한 커서라는 용어를 사용합니다.

(2)공유커서
오라클에서 커서를 공유한다는 표현을 자주하는데 여기서말하는 커서는 라이브러리 캐시의 공유커서를 일컫습니다.
세션커서,애플리케이션커서를 다른 프로세스와 공유하기는 어렵기 때문입니다.
라이브러리 캐시에 공유돼 있는 커서의 수행 통계를 v$sql을 통해 조회해 볼 수 있습니다.
다른 세션에서도 같은 SQL을 수행할때도 커서를 재사용합니다.
커서가 공유되려면 커서를 식별하는 키 값이 같아야 하는데, 라이브러리 캐시에서 커서를 식별하기 위해 사용되는 키 값은 SQL문장 그 자체입니다.
오라클 10g부터 sql_id라는 별도의 식별자 컬럼을 두고 있지만 이것은 고객테이블에서 고객번호(PK)의 대체키로써 주민번호를 사용할 수 있는 것과 같습니다. 고객번호와 주민번호가 1:1 대응관계를 갖는것 처럼 sql_id와 sql_fulltext는 1:1로 대응됩니다. 따라서 SQL문 중간에 작은 공백문자 하나만 추가하더라도 서로 다른 SQL로 인식해 새로운 sql_id를 발급받게 됩니다. 즉 커서는 공유되지 않습니다.

(3) Child 커서를 공유하지 못하는 경우
SQL문장이 100%동일한대도 SQL커서를 공유하지 못하고 별도의 SQL커서를 생성해야 할 때도 있습니다. 예를들면 scott스키마의 emp테이블 조회와 hr 스키마 emp 테이블 조회에 대해서 SQL이 동일하다 하더라도 각각 다른 테이블을 액세스해야 하고 실행계획도 달라져야합니다.
이럴때 오라클은 별도의 Child커서를 생성합니다. Drop 하기 전까지 영구적으로 보관되는 Stored Object(테이블,인덱스,함수,프로시저,패키지 등)는 생성될 때부터 유일하게 식별 가능한 이름이 부여되므로 Child오브젝트를 사용할 필요가 없습니다. 하지만 Sql커서 처럼 실행시점에 생성되서 인스터스가 떠 있는 동안에만 존재하는 Transient Object는 이름을 따로 지정하지 않고 문장을 구성하는 전체 문자열 그대로가 LCO를 식별하는 이름 역할을 합니다. 그래서 전체 문자열은 같지만 다른 방식으로 실행해야 하거나, 파싱 스키마에 따라 다른 오브젝트를 참조하는 상황에서 Child커서가 필요해집니다.
SQL마다 하나의 parent커서를 가지며 여러개의 child커서를 가질 수 있습니다. 실제 수행에 필요한 정보는 child커서에 담기므로 적어도 한 개의 child커서를 갖습니다.
v$sqlarea는 parent커서 정보를 보여주고, v$sql은 child커서정보를 보여줍니다.

SQL하나당 여러개의 child커서를 갖는 것은 바람직한 현상은 아닙니다. version count수치가 높은 SQL일 수록 커서를 탐색하는 데 더 많은 시간을 소비하므로 library cache래치에 대한 경합 발생 가능성을 증가시킵니다.
하나의 SQL문장이 여러개 child커서를 갖게 되는 이유는 여러가지가 있습니다.
- SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오보젝트를 가리킬때
- 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데 특정 세션이 아직 기존 커서를 사용 중(pin)일때
- 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
- 입력된 바인드 값의 길이가 크게 다를 때
- NLS파라미터를 다르게 설정했을 때
- SQL트레이스를 활성화했을때
v$sql_shared_cursor뷰는 새로운 child커서가 왜 기존 child커서와 공유되지 못했는지 이유를 설명해 줍니다.

(4)Parent 커서를 공유하지 못하는 경우
앞에서는 SQL문장 자체는 같지만 의미적으로 다른 오브젝트를 참조하거나 실행환경이 달라 다른 실행계획을 고려해야 하는 상황에서 발생할 수 있는 상황입니다.
이제는 의미적으로 같고 실행환경이 같은데도 커서를 공유하지 못해 parent 커서 자체가 여러 개 생성되는 경우입니다.
1.공백 또는 줄바꿈
select * from customer;
select * from customet;
2.대소문자 구분
select * from customer;
select * from CUSTOMER;
3.테이블 owner명시
select * from customer;
select * from hr.customer;
4.주석
select * from customer;
select /* 주석 */ * from customer;
5.옵티마이저 힌트
select * from customer;
select /*+all_rows */ * from customer;
6.조건절 비교 값
select * from customer where cust_id=‘0000001’;
select * from customer where cust_id=‘0000002’;

이외에도 다양한 케이스가 있으면 1,2,4번은 실행계획이 100%같습니다. 그럼에도 불구하고 서로 다른 SQL로 각각 하드파싱을 일으키고 서로 다른 공간을 차지하면서 shared pool을 낭비하게 됩니다. 이를 방지하려면 개발 초기에 sql 작성 표준을 정해 이를 준수하도록 합니다.