CBO 와 바인드 변수, 바인드 피크

바인드 변수


바인드 변수의 장점 : 구문이 길고 조건만 다른 SQL문들을 한 개로 정리할 수 있다.

  -> 공유 풀 내의 메모리 사용량을 줄인다.

  -> 하드 파스 발생률을 줄인다.


일반적인 OLTP 시스템에서는 바인드 변수를 사용해 SQL을 작성하는 것이 필수. 따라서 옵티마이저가 바인드 변수를 어떻게 취급하는지 이해하는 것은 매우 중요한 부분.


예전버전의 오라클에서는 바인드 변수를 사용한 SQL문에서 선택도를 구하기 위해 내부에 저장된 기본값을 사용했지만, 9i 이후 버전에서는 '바인드 피크 (bind peek)'라고 하는 기능을 사용되고 있기 때문에 바인드 피크의 작동 방식을 반드시 알아두어야 한다.



바인드 피크


9i 이후 버전에서는 바인드 피크 기능이 켜져있다. 이전 버전에서는 조건절에 바인드 변수가 있을시 내부 기본값으로 행의 선택도를 결정하는데, 내부 기본값과 실제 데이터의 분포에 차이가 있으면 적절치 못한 실행 계획을 선택할 가능성이 높다.


바인드 피크란 옵티마이저가 실행 계획을 생성할 때 바인드 변수에 설정된 실제 값을 '엿보는(peek)' 기능이다. 이 기능을 이용해 리터럴 SQL을 사용할 때 처럼 값의 분포를 고려한(히스토그램도 이용) 행의 선택도를 계산할 수 있다. 사용자가 지정한 실제 값과 통계를 이용하기 때문에 좀 더 적절한 실행 계획을 선택할 수 있게 된다.


바인드 피크를 이용하게 되면, 바인드 변수를 이용한 조건절이 해당 테이블 안에 10만건이 존재 했을 경우 (전체의 10%), 10%를 히트 함으로 테이블 풀스캔을 선택한다. 같은 SQL 문에서 1만건 밖에 존재하지 않는 조건(전체의 1%)을 검색 했을 때도 10%조건에 최적화된 공유 커서가 이미 존재 하고 있어, 해당 실행계획을 다시 사용한다. 그렇지만 1%를 위해 풀스캔을 하는 것은 부적절하다. Shared Pool을 Flush 하고 다시 1% 조건을 실행 해보면 인덱스 액세스 패스를 사용한다. 이렇게 조건절의 데이터 차이가 심한 경우에는 바인드 피크를 끄는 경우가 좋을 수도 있다. 


 ● 바인드 변수를 사용 하는 목적은 같은 구문을 가진 SQL의 실행 계획을 한개로 처리하여 자원 사용의 효율을 향상하는 것에 있다.

 ● 모든 바인드값에 최적인 실행계획이 한 개일 수는 없으며, 현실적으로 사용자가 사용하는 '대부분'의 값을 좋게 만드는 성능이 있다면 사용해야 한다.

 ● 한 개의 실행계획이 '대부분'의 요구를 만족하지 못할 때 실행 계획을 여러 개로 만들 필요가 있다. 이 경우에는 리터럴로 만드는 것이 적절.

  - DW 시스템에서는 적합함

  - OLTP 시스템에서는 이런 SQL문을 가능하면 적게 할 필요가 있음.

 ● 바인드 피크를 사용할 때 예외적인 값이 입력되어 엉뚱한 실행 계획이 도출될 가능성이 있으며, '대부분'에 해당하는 값을 가져오기 위한 성능을 악화시킬수 있음. 단, '대부분'의 값에 맞춰 최적화됐을 경우, 바인드 피크가 꺼져 있을 때에는 얻을수 없었던 뛰어난 효율성을 가진 실행 계획이 도출될 가능성이 있음.

  - 특히 범위 검색의 경우

  - 히스토그램이 존재하고 있을때는 '='조건에서도 해당함.

 ● 바인드 피크를 끄면 최적 실행 계획이 도출될 확률은 줄어들지만, 실행 계획이 변경되는 위험도 그만큼 적다.

  - '=' 조건 검색은 1/NDV를 선택도로 사용하므로, 히스토그램이 없는 상태의 리터럴 SQL과 같음.

  - 범위 검색의 기본 선택도는 5%이므로 그나마 인덱스를 자주 사용하게 됨

  - 인덱스나 조인 순서를 선택할 때 최적의 선택을 하지 못할 가능성이 있음. 그 경우엔 힌트를 사용해 강제로 해야 할 필요가 있음.


바인드 피크 기능을 끄려면 _OPTIM_PEEK_USER_BINDS 파라미터를 FALSE로 변경. 



※ 바인드 변수와 EXPLAIN PLAN


EXPLAIN PLAN 이나 AUTOTRACE에서는 바인드 피크가 수행되지 않으므로 실제와 다른 실행계회이 도출되는 경우가 있음. 바인드 변수를 사용한 SQL의 실행 계획을 확인 하기 위해서는 SQL 트레이스나 V$SQL_PLAN을 사용.

'ORACLE > Tunning' 카테고리의 다른 글

CURSOR_SHARING  (0) 2018.10.31
CBO 와 바인드 변수, 바인드 피크  (0) 2018.05.17
Hint 정리  (0) 2017.12.18
CBO와 히스토그램 정리  (0) 2017.12.07
옵티마이저 기본기능  (0) 2017.11.14

티스토리 툴바