오라클 adaptive cursor sharing (ACS) 정리

앞선 포스팅에서 소프트 파싱과 하드파싱을 정리한 글에서 

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

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

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

 

1. ACS 등장 배경 

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

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

 4) adaptive cursor share
 - 데이터 분포에 따라 다른 실행계획 생성
 - 오버헤드는 발생 

 

 

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('SYSTEM','TB_DPS_TRSC_BASE', ESTIMATE_PERCENT=>100, METHOD_OPT=>'FOR COLUMNS ACCT_NO SIZE 2 ');

* SIZE 뒤의 숫자만큼 버킷을 지정되어 수집되고 1부터 2048 까지 지정 할수 있다.

0 은 지정할수 없고, 1 은 히스토그램을 수집하지 않겠다는 것을 의미.

NDV 를 고려해서 버킷사이즈를 적절하게 선택 

 

- 관련 파라미터 확인 

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

 

 - V$SQL 의 관련 컬럼

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

IS_BIND_AWARE
 => Y : (SENSITIVE already marked 상태) extended cursor sharing 사용 
 => N : 1) SENSITIVE 가 N 이거나
            2) SENSITIVE 가 Y 이지만 아직 extended cursor sharing 사용되지 않은 상태 
   

- 실행계획 분화 프로세스

  1. 통계, 히스토그램 수집

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

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

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

 

 

3. ACS 한계

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

 아래와 같은 추가적인 비용이 발생하게 된다.  

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

 - 메모리 할당(library cache) 및 통계 정보 수집(v$sql_cs_histogram) 발생
최대 20개까지의 Child Cursor 유지로 인한 메모리 증가

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

 

즉, IS_BIND_AWARE = Y 이면 이후 해당 SQL 은

매번 바인드 값을 확인하고, 
Child Cursor 선택/생성 로직을 거치며,
통계 정보와 히스토그램을 참조하는 추가적인 내부 연산이 발생한다. 

Child Cursor가 많아질수록 커서 관리 비용, 라이브러리 캐시 경합, CPU 소모가 증가하게 된다. 

 

그러므로, 과도한 child cursor 로 인한 부작용 발생시, 아래와 같이 적절히 대응해야 할수도 있다. 

- ACS 기능을 비활성화하거나(_optimizer_adaptive_cursor_sharing=false)
- 바인드 변수 대신 리터럴 사용
- 히스토그램 수집 범위 조정 (skew 없으면 히스토그램을 수집X)
- SQL 구조 변경(조건 분기, 힌트 사용 등)

즉, 남용하지 말고 DBA 가 적절히 개입할 필요가 있다.