4. 커서 공유
1. 커서(Cursor)란 ?
커서를 설명하자면, 세 종류의 커서가 있는데 이들을 모두 커서라고 부른다.
각 커서들은 상황에 따라 의미도 다르고 저장되는 위치도 다르다.
공유 커서 : 라이브러리 캐시에 공유돼 있는 Shared SQL Area
세션 커서 : Private SQL Area에 저장된 커서
애플리케이션 커서 : 세션 커서를 가리키는 핸들
1. 공유 커서(Shared Cursor)
Java, VB, Pro*C, PL/SQL 등에서 SQL을 실행하면, 서버 프로세스는 해당 SQL이 라이브러리 캐시에 공유돼 있는지 먼저 확인한다. 그렇게 라이브러리 캐시에 공유돼 있는 Shared SQL Area를 '공유 커서'라고 부른다.
2.세션 커서(Session Cursor)
라이브러리 캐시에 공유돼 있는 커서를 실행할 때는 우선 PGA 영역에 메모리를 할당한다.이를 'Private SQL Area'라고 하는데, Persistent Area와 Runtime Area로 나뉜다.
Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SQL Area에 담고, 공유 커서를 가리키는 포인터를 유지하고 커서의 상태정보도 관리한다.
커서를 실행하기 위한 이런 준비과정을 "커서를 오픈한다"라고 표현하고, PGA에 저장된 커서 정보를 '세션 커서'라고 부른다.
오라클에서도 커서를 오픈하면 라이브러리 캐시에 공유돼 있는 커서(공유 커서)를 인스턴스화함으로써 PGA에 커서(세션 커서)를 위한 메모리 공간을 할당하고, 실제 데이터 추출을 시작할 수 있도록 준비작업을 한다.
3. 애플리케이션 커서(Application Cursor)
PGA에 있는 커서를 핸들링하려면 Java, VB, Pro*C, PL/SQL 같은 클라이언트 애플리케이션에도 리소스를 할당해야 하는데, 이 커서가 '애플리케이션 커서' 이다.
그림으로 설명하면, 라이브러리 캐시에 있는 커서가 '공유 커서'이고 PGA에 있는 커서가 '세션 커서'이고 Java, VB에 있는 커서가 '애플리케이션 커서' 이다.
2. 커서 공유
오라클에서 "커서를 공유한다"는 표현을 자주 사용하는데, 여기서 말하는 커서는 라이브러리 캐시의 '공유 커서'를 말한다. 세션 커서, 애플리케이션 커서는 다른 프로세스와 공유할 수 없다.
다른 세션에서 같은 SQL을 수행할 때도 이전 세션에서 적재한 커서를 재사용할 수 있다.
공유된 커서를 사용할 때는 최적화 및 Row-Source Generation 단계를 생략하고 곧바로 실행 단계로 넘어가므로 보다 효율적이고 빠르게 SQL을 수행한다.
커서가 공유되려면 커서를 식별하는 키 값이 같아야 하는데, 라이브러리 캐시에서 커서를 식별하기 위해 사용되는 키 값은 'SQL 문장 그 자체'이다
3. Child 커서를 공유하지 못하는 경우
실제 수행에 필여한 정보는 Child 커서에 담기므로 적어도 한 개의 Child 커서를 갖는다.
SQL 문장이 100% 동일한대도 SQL 커서를 공유하지 못하고 별도의 SQL 커서를 생성해야 할 때도 있다. 이럴 때 오라클은 별도의 Child 커서를 생성한다.
이렇기 때문에 SQL마다 하나의 Parent 커서를 가지며, Child 커서는 여러 개일 수 있지만, 바람직한 것은 아니다. Child 커서가 많을 수록 커서를 탐색하는 데 더 많은 시간이 걸리고 library cache 래치에 대한 경합 발생 가능성을 증가시킨다.
하나의 SQL 문장이 여러 개의 Child 커서를 갖는 이유들
-
SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가르킬 때
-
참조 오브젝트가 변경돼 커서가 무효화돠면 이후 그 커서를 처음 사용하려는 세션에의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용 중일 때
-
옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
-
입력된 바인드 값의 길이가 크게 다를 때
-
NLS 파라미터를 다르게 설정했을 때
-
SQL 트레이스를 활성화했을 때
4. Parent 커서를 공유하지 못하는 경우
의미적으로 같고 실행환경이 같은데도 커서를 공유하지 못해 Parent 커서 자체가 여러 개 생성되는 경우가 있다.
- 공백 또는 줄바꿈
select * from table;
select * from table;- 대소문자 구분
select * from table;
SELECT * FROM table;- 테이블 소유자 명시
select * from table;
select * from ham.table;- 주석
select * from table;
select/* hamji */ * from table;- 옵티마이저 힌트 사용
select * from table;
select /*+ all_rows*/ * from table;- 조건절 비교 값
select * from table where id = 1;
select * from table where id = 2;5. 바인드 변수의 중요성
바인드 변수의 사용은 n번의 반복적인 같은 SQL을 실행할 때 하드 파싱을 단 1번만 하고 나머지는 소프트 파싱으로 라이브러리 캐시 내의 메모리를 재사용한다.
커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여준다.
궁극적으로, 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는데 기여하고, 특히 동시 사용자 접속이 많은 때는 그 영향력이 절대적이다.
바인드 변수 사용 원칙을 잘 지키지 않으면 라이브러리 캐시 경합 때문에 시스템 정상 가동이 어려운 경우가 있다. 이 때 cursor_sharing 파라미터를 변경하는 것을 고려해 볼 수도 있지만 급할 때만 사용해야 한다.
6. 바인드 변수의 부작용과 해법
바인드 변수의 부작용
바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고, 실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 된다.
여기서, 변수를 바인딩하는 시점은 실행시점이다.(최적화 시점보다 나중)
즉, SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점을 갖는다.
-> 바인드 변수를 사용해 다른 통계정보들은 활용하지만 컬럼 히스토그램은 활용하지 못함
따라서, 바인드 변수를 사용할 때 옵티마이저는 평균 분포를 가정한 실행계획을 생성한다. 평균 분포가 균일할 때는 문제가 되지 않지만 그렇지 않을 때는 실행 시점에 바인딩되는 값에 따라 최적이 아닌 실행계획일 수 있기 때문이다.
카디널리티 = 선택도 x 전체 레코드 수
바인드 변수를 사용할 때는 옵티마이저가 카디널리티를 예측하지만, 상수 조건식을 사용하면 정확한 카디널리티를 계산할 수 있다.
이처럼 바인드 변수를 사용할 때는 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 높다.
바인드 변수를 사용하면 컬럼 히스토그램 뿐만 아니라 파티션 레벨 통계 정보도 이용하지 못하게돼서 파티션 테이블을 쿼리할 때 부작용이 있다. 파티션 레벨 통계 정보보다 다소 부정확한 테이블 레벨 통계를 이용함으로써 옵티마이저가 가끔 악성 실행계획을 수립한다.
바인드 변수 부작용의 해법
해법 1 : 바인드 변수 Peeking (SQL Sever - Parameter Sniffing)
바인드 변수의 부작용을 극복하려고 오라클은 9i부터 바인드 변수 Peeking 기능을 도입하였다.
'Peeking' 이라는 단어가 의미하듯이 이 기능은 SQL이 첫 번째 수행되면서 하드파싱될 때 함께 딸려 온 바인드 변수 값을 살짝 훔쳐 보고, 그 값에 대한 컬럼 분포를 이용해 실행계획을 결정하는 것이다.
10g 부터는 dbms_stats의 기본 설정이 히스토그램을 생성할지 여부를 오라클이 판단하는 쪽으로 바뀌어서 더 많은 히스토그램이 생성되고, Explain Plan 명령을 통해 실행계획을 확인할 때는 바인드 값을 제공하지 않으므로 바인드 변수 Peeking 기능은 대부분 운영시스템에서는 비활성화시킨 상태이다.
해법 2: 적응형 커서 공유 (Adaptive Cursor Sharing)
10g에서 '바인드 변수 Peeking 기능'이 또 다른 부작용을 일으키자 11g에서 입력된 바인드 변수 값의 분포에 따라 다른 실행계획이 사용되는 '적응형 커서 공유' 라는 기능을 소개했다.
이 기능이 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성돼 있어야 한다.
그 상태에서, 옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져갈 필요가 있다 판단하는 SQL 커서에 대해 이 기능을 활성화한다. 이런 커서를 'Bind Sensitive 커서'라고 부른다.
오라클은 Bind Sensitive 커서에 대해 내부적으로 별도의 히스토그램과 수행 통계 관리하며, 특정 값으로 실행했을 때 이전에 비해 많은 일량을 처리한 것으로 판단되는 순간 해당 커서를 Bind Aware 모드로 전환한다.
이때 기존 커서의 사용이 중지되고 새로운 커서의 생성이 시작된다.
커서가 바인드 값 별로 따로 만들어지는 것은 아니며, 선택도가 비슷한 것 끼리 같은 커서를 공유한다. Bind Aware 모드에서 생성된 커서를 'Bind Aware 커서' 라고 부른다.
데이터 딕셔너리에 저장되는 히스토그램에는 여러 버킷이 존재하고, 옵티마이저가 실행계획을 수립할 때 이 정보를 이용해 입력 값에 대한 선택도를 산정한다.
각 Bind Aware 커서는 히스토그램상 자신이 처리해야 할 버킷이 내부적으로 결정된다.
따라서 이 모드에서 SQL을 실행하면, 현재 입력된 바인드 값을 처리할 커서가 캐싱돼 있는지를 먼저 찾는다. 이때 생성된 커서를 찾지 못하면 하드파싱을 통해 새로운 실행계획을 만들고 새로 생성된 커서의 실행계획이 기존에 캐싱돼 있던 커서의 실행계획과 결과가 같다면, 그 중 하나만 사용하고 나머지는 버린다.
해법 3: 입력 값에 따라 SQL 분리
union all을 이용해 SQL을 길게 작성하면, 라이브러리 캐시 효율을 떨어뜨리고 Parse 단계의 CPU 사용률을 높일 뿐 아니라 Execute 단계에서도 CPU 사용률을 높이는 결과를 초래한다. 실행계획 분기 조건에 의해 제외되는 부분은 실행되지 않는다고 생각하겠지만 I/O를 일으키지 않을 뿐 실제 실행은 일어나기 때문이다.
이처럼 union all을 사용해 SQL을 길게 작성하는 패턴을 많이 사용하면 시스템 전판의 CPU 사용률을 높이고 라이브러리 캐시 부하를 가중시키며 네트워크를 통한 메시지 전송량도 증가시킨다.
따라서, 배치 프로그램이나 DSS/OLAP 시스템이라면 상관없겠지만 OLTP 시스템이라면 union all을 사용하는 것보다 애플리케이션 단에서 조건에 따라 SQL을 분기하는 것이 바람직하다.
- 배치 프로그램, DSS/OLAP -> union all 권장
- OLTP -> 조건에 따라 SQL 분기
해법 4: 예외적으로, Literal 상수값 사용
조건절 컬럼의 값 종류가 적을 때는 바인드변수보다 오히려 Literal 상수를 사용하는 게 더 나은 선택일 수도 있다. 입력 값의 종류가 몇 개에 불과하다면 하드파싱 부하가 미미할테고, Literal 상수를 사용함으로써 옵티마이저가 더 나은 선택을 할 가능성이 커지기 때문이다.
- 부등호나 Between 같은 검색조건
- 배치 프로그램, OLAP/DW 등 정보계 시스템(거의 범위 검색조건)
위의 조건에는 바인드 변수보다는 Literal 상수 값을 사용하는게 낫다.
OLTP성 애플리케이션이라도 사용빈도가 아주 낮다면 예외적으로 Literal 상수 값을 사용하는 것을 고려해볼 수 있다.
