DYNAMIC SAMPLING(동적 샘플링)은 통계정보가 존재하지 않은 Table에 대한 정확한 selectivity와 cardinality의 추정치를 결정하여 성능을 향상시키는 것이 목적
Dynamic Sampling 단점
Oracle은 compile time에 어떤 query를 동적 샘플링을 하는 것이 좋을지 나쁠지를 결정한다.
만약 동적 샘플링을 하는 것이 좋다고 판단되면 recursive sql이 해당 query table block의 small random sample을 scan해서 주요한 single table에 추정 선택도(selectivites)를 결정한다.
그만큼의 부하가 발생되고 또한 통계정보가 있는 것보다는 부정확하므로 CBO를 사용한다면 되도록이면 꼭 통계정보를 생성하도록 하는 것이 좋다.
통계 정보가 잘 수집되었다 하더라도 Dynamic Sampling은 여전히 필요( 엑셈 조동욱 차장님 포스팅)
DYNAMIC_SAMPLING 힌트의 정체
Dynamic Sampling은 통계 정보에 대한 해석의 오류에서 오는 실행 계획 이상 현상을 방지할 수 있는 훌륭한 도구가 될 수 있다. 통계 정보가 있지만, 이미 수집된 통계 정보로는 정확한 정보를 추출할 수 없다면 Query가 Parse되는 시점에 Dynamic Sampling을 수행해서(Dynamic Sampling에 의한 오버헤드를 감수) 비교적 정확한 정보를 얻을 수 있다.
Dynamic Sampling은 바인드 변수와는 잘 어울리지 않는다. Dynamic Sampling Hint를 주더라도 SQL 텍스트가 완벽하게 동일하다면 추가적인 하드 파싱(Optimization)이 발생하지 않으므로 바인드 변수를 사용하는 경우에는 Dynamic Sampling은 유용하지 않다.
이런 이유 때문에 Dynamic Sampling Hint는 Parse시간이 Execute시간에 비해 상대적으로 매우 저렴한 DW 환경에서만 의미가 있고, 대부분 Literal 값을 사용하는 것이 권장된다. 거꾸로 바인드 변수 사용이 절대적인 OLTP 환경에서는 Parse시간이 중요하기 때문에 Dynamic Sampling을 사용을 피한다.
이미 통계정보가 수집된 테이블들에 대한 쿼리에서 dynamic_sampling 힌트가 필요한 경우는 다음과 같이 요약할 수 있다. "통계 정보의 한계로 인해 CBO가 제대로 된 예측을 할 수 없을 때"
통계 정보를 매우 정확하게 수집했음에도 불구하고 CBO가 비효율적인 실행 계획을 생성한다면, 통계 정보가 잘못된 것이 아니라 통계 정보 자체의 한계이다.
예를 들어, 통계 정보가 있다고 하더라도 Like '%XXX%' 류의 계산은 항상 오류의 위험이 있다. Oracle이 수집하는 컬러 통계 정보로는 Like '%XXX%'의 Selectivity를 예측하지 못하기 때문이다. (그래서 항상 5%를 곱해버림)
이런 경우에 Dynamic Sampling을 사용하면 Sampling 과정에서 실제 Predicate 조건에 들어간 값을 이용해 예측하는 효과가 발생한다. 이로 인해 비교적 정확한 예측을 할 수 있게 되며, Dynamic Sampling으로 인해 Like '%XXX%' 조건에 대한 예측 로우수가 상당히 정확해질 수 있다.
즉, 통계 정보를 주기적으로 착실히 수집하고 있는 곳에서도 Dynamic Sampling이 반드시 필요한 때가 있다. 물론 힌트로도 같은 효과를 볼 수 있지만, 힌트는 실행 계획을 고정시켜버리는 반면에 Dynamic Sampling은 상황에 따라 적절한 실행 계획을 생성하는 장점이 있다.
항상 주의할 것은 Dynamic Sampling 과정 자체가 보통의 쿼리를 수행하는 정도의 부하를 가지고 있기 때문에 DW성의 대용량 Query에서만 사용되어야 한다.
(참고로) SQL Server 2005는 Like '%XXX%' 류의 조건에 대해서도 통계 정보를 수집한다고 한다.
Optimizer Mode & Dynamic Sampling
Oracle 9i에서는 Optimizer Mode가 choose이므로 통계 정보가 없으면 Rule Based로 동작한다.
반면, Oracle 10g에서는 Optimizer Mode가 all_rows이므로 통계 정보가 없으면 Dynamic Sampling을 수행하게 된다.
10g에서도 Optimizer Mode를 choose나 rule로 변경하면 자동적인 Dynamic Sampling은 발생하지 않는다.
Dynamic Sampling이 발생하더라도 Hard Parse가 이루어지는 시점에만 발생하므로 Overhead가 크게 문제가 되지 않을 수도 있다. 단, /*+ dynamic_sampling(...) */ Hint를 사용하는 경우에는 파라미터 값에 무관하게 Sampling이 이루어진다
Dynamic Sampling 적용 시점 : 정상 Query에는 동적 샘플링은 적용되지 않음
동적 샘플링을 하는 것이 좀 더 좋은 실행계획을 만들 수 있는 경우
sampling time이 query의 전체 실행시간의 일부분에 지나지 않는 경우
query 실행 시간이 오래 소용 되는 경우
Dynamic으로 변경 : optimizer_dynamic_sampling = value
Sampling Level (0 ~10)
0 :dynamic sampling 비활성화
1 :9i Default value
수행 Query에 최소 하나 이상의 통계정보가 없는 table에 존재
통계정보가 존재하지 않은 Table이 Join을 수행하거나 Sub Query로 수행
인덱스가 존재하지 않음
통계정보가 존재하지 않는 Table의 block개수가 dynamic sampling을 수행하는 블록의 개수(32) 보다 많을 때
2 : 10g Default value. 통계정보가 없는 모든 Table을 sampling 함. Sampling block의 개수는 1과 동일
4 : where 절에서 두 개 이상의 컬럼을 참조하는 모든 table을 sampling함