오라클 adaptive cursor sharing (ACS) 정리

앞선 포스팅에서 소프트 파싱과 하드파싱을 정리했다. 

[oracle] 소프트 파싱 과 하드파싱

[oracle] 소프트 파싱 과 하드파싱

 

바인드 피킹 기능의 한계 및 보완으로 ACS 가 새로 도입이 되었다고 했는데 좀 더 알아보자

---

1. ACS 등장 배경 

 1) 리터럴 SQL
  - 동일 구조 쿼리에서 입력값이 동일하지 않은 경우 별개의 SQL로 처리되어, 하드파싱 부하 발생  
 
 2) bind 변수 사용 SQL 
 - 바인드변수 사용으로 동일 구조 SQL은 동일 SQL ID 를 사용 => 하드파싱 감소
 - 데이터 분포를 고려하지 않은 실행계획이 생성, 실행됨

 3) bind peeking 기능 도입
 - 최초 실행시점 데이터 입력 값에 기반한 실행계획이 생성됨.
 - 분포도가 다른 입력값이 들어오면 비효율 실행계획 가능성 발생.

 4) adaptive cursor share
 - 바인드 변수 값에 따른 실행계획의 성능 차이가 클 경우, 동일 SQL이라도 바인드 값별로 다른 child cursor 를 생성하여 사용  
 - 오버헤드는 발생  => ** 글 하단 참조

 

---

2. ACS 사용하기

 ACS 를 적절히 사용하려면 우선 컬럼에 히스토그램이 생성되어 있어야 한다. 
통계정보 수집 할때 method_opt 옵션에서 지정할수 있고 문법과 예시는 아래와 같다. 

 - METHOD_OPT 옵션 문법

FOR COLUMNS [column_clause] [size_clause]
size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column_clause := column_name | extension name | extension

 - 예시

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SYSTEM',
    tabname            => 'TB_DPS_TRSC_BASE',
    estimate_percent => 100,
    method_opt       => 'FOR COLUMNS ACCT_NO SIZE 2'
);

* SIZE 뒤의 숫자만큼 버킷이 지정되어 수집되고 1부터 2048 까지 지정 할수 있다.
0 은 지정할수 없고, 1 은 히스토그램을 수집하지 않겠다는 것을 의미.
NDV (Number of Distinct Value) 를 고려해서 버킷사이즈를 적절하게 선택.

- 관련 파라미터 확인 

col KSPPINM for a40
col KSPPSTVL for a10

SELECT KSPPINM, KSPPSTVL
  FROM X$KSPPI X, X$KSPPCV Y
 WHERE X.INDX = Y.INDX
   AND X.KSPPINM = '_optimizer_adaptive_cursor_sharing'
;

-- true 가 아니라면 아래처럼 변경해준다. (ACS 를 사용할거라면)

alter [ system | session ] set "_optimizer_adaptive_cursor_sharing" = true;  -- default 

* acs 는 전역적으로 제어되고 개별 SQL 단위로 적용 할수 없다.

---

3. ACS 의 실행계획 분화 프로세스

 하나의 SQL 이 자식커서를 만들고 실행계획이 분화되는 프로세스는 아래와 같다. 

  1) 통계 수집 (통계가 없다면 acs 는 작동하지 않고, 히스토그램이 없다면 균일하다고 가정 됨.)

  2) 옵티마이저가 바인드변수에 따라 실행계획이 변경될수 있다고 판단되면 => IS_BIND_SENSITIVE = Y 

  3) 유저 입력값의 변경에 따라 이전에 비해 많은 일량 처리 됨 => IS_BIND_AWARE = Y

  4) 3번의 바인드 값 다시 들어오면 기존 커서 중지, 새로운 커서 생성

* V$SQL 의 관련 컬럼 (is_bind..) 및 값의 의미

 - IS_BIND_SENSITIVE 
 => Y : 옵티마이저가 히스토그램을 분석, 바인드 값 변경 시 실행계획이 달라질 수 있음을 감지 
 => N : 통계가 없거나, 균일한 분포(임계를 넘지 않은) 

 - IS_BIND_AWARE
 => Y : (SENSITIVE already marked 상태) 바인드 변수 값에 따라 다중 실행계획 사용 중
 => N : 1) SENSITIVE 가 N 이거나
            2) SENSITIVE 가 Y 이지만 아직 extended cursor sharing 사용되지 않은 상태 

 

---

4. ACS 한계

 히스토그램을 반영하는 실행계획을 생성해주는 장점이 있지만 아래와 같은 추가적인 오버헤드는 발생한다.  

 - 실행계획 평가 비용 증가
 각 실행 시점마다 옵티마이저가 바인드 값과 기존 실행계획의 효율성을 지속적으로 평가
 
 - 커서 관리 부하
새로운 Child Cursor 생성 시 추가적 관리 부하 발생

 - 메모리 할당(library cache) 및 통계 정보 수집(v$sql_cs_histogram) 발생
 Child Cursor 유지로 인한 메모리 관리 비용 증가  ( 최대 '_cursor_obsolete_threshold' 값 만큼 생성 가능)

 - 파싱 오버헤드
Bind-Aware 상태 전환 시 추가적인 Soft Parsing 발생 -> 라이브러리 캐시 래치 경합 가능성 증가

즉, IS_BIND_AWARE = Y 이면 이후 해당 SQL 은 매번 바인드 값을 확인하고, Child Cursor 선택 혹은 (필요하다면) 생성 로직을 거치며, 통계 정보와 히스토그램을 참조하는 추가적인 내부 연산이 발생한다. 그래서, Child Cursor가 많아질수록 커서 관리 비용, 라이브러리 캐시 경합, CPU 소모가 증가하게 된다. 

만약 해당 쿼리가 매우 빈번하게 호출된다면 오히려 ACS 가 오히려 성능에 불안정하게 작용 할수 있다. 

그런 상황에서는 아래와 같이 

- ACS 기능을 비활성화하거나(_optimizer_adaptive_cursor_sharing=false)
  => acs 는 전역적이기 때문에, 모든 sql 은 다시 확인해야하는 어려움이 있다. 

- SQL 구조 변경(조건 분기 + 힌트 사용 등)
  => 쿼리가 지저분해지고, 오직 성능만을 위해 작성된 쿼리

- 해당 SQL만 바인드 변수 대신 리터럴 사용 고려
  => 입력값에 따라 SQL 이 나눠진다. 

---

5. 정리 및 결론 

 ACS 가 적절한 경우 : skewed 데이터분포, 단일 실행계획이 불가한 경우

ACS 가 부적절한 경우 : 매우 빈번한 OLTP 쿼리