앞선 포스팅에서 소프트 파싱과 하드파싱을 정리한 글에서
바인드 피킹 기능 한계 및 보완으로 ACS 가 새로 도입이 되었다고 했는데 더 자세히 알아보자
[oracle] 소프트 파싱 과 하드파싱
1) 오라클에서 SQL 의 처리과정 사용자가 SQL 을 실행하면 아래 1~ 3 까지의 파싱 단계를 거치게 된다. 1. syntax check : 문법적 오류 체크 (ex. select * form tab_a ) 2. semantic check : 존재하지 않는 테이블 조회
riorio.tistory.com
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 가 적절히 개입할 필요가 있다.
'생계 > 튜닝' 카테고리의 다른 글
| [oracle] 소프트 파싱 과 하드파싱 (0) | 2024.12.11 |
|---|---|
| mysql 실행계획 확인하기 (0) | 2024.11.25 |
| rac 환경 통계수집 후 no_invalidate (0) | 2023.06.10 |
| 오라클 AWR 사용하기 (0) | 2023.05.13 |
| [SQL]열거된 OR 제거하기. IN 사용 (0) | 2020.11.13 |
