[Oracle] Dynamic Statistics

Dynamic sampling(DS) 은 optimizer가 parse time에 추가적으로 통계를 수집하도록하는 기능이다. 

통계가 누락이되었거나, 오래되었거나, 좋은 실행 계획을 생성하기 부족한 경우에 실행이 된다. 이렇게 수집된 통계는 DBMS_STATS 패키지를 사용하여 수집된 통계만큼 품질이 높거나 완전 하진않다. ( 컴파일 시간이 미치틑 영향을 제한하기 위함)

 

SQL 문에 복잡한 조건자 식이 포함되어 있고 확장 통계를 사용할 수 없는 경우 DS를 사용할 수 있다.

Oracle Database 11g Release 1에서는 옵티마이저가 복잡한 술어 표현식에 대해 우수한 품질의 카디널리티 추정을 얻을 수 있도록 돕기 위한 확장 통계가 도입되었다.

 

인스턴스나 세션 수준에서 OPTIMIZER_DYNAMIC_SAMPLING  파라미터에 의해 조정이 가능하고, 다음과 같은 hint를 사용하여 개별 쿼리에 대해 사용할 수 있다. 

alter session set optimizer_dynamic_sampling = 4;

SELECT /*+ dynamic_sampling(e 1) */ count(*)
FROM emp e;

 

파라미터 value 는 0~10을 설정할 수 있으며, sampling의 정도를 나타낸다.

Level	When Dynamic Statistics will be used	Sample size (blocks)
0	Switches off dynamic statistics	N/A
1	At least one non-partitioned table in the statement has no statistics	32
2 (default)	One or more tables in the statement have no statistics This setting is suitable for the majority of systems	64
3	Any statement that meets level 2 criteria and any statement that has one or more expressions used in the where clause predicates e.g. Where substr(CUSTLASTNAME,1,3) or Where a + b =5	64
4	Any statement that meets level 3 criteria and any statement that has complex predicates. An OR or AND operator between multiple predicates on the same table	64
5	Any statement that meets level 4 criteria	128
6	Any statement that meets level 4 criteria	256
7	Any statement that meets level 4 criteria	512
8	Any statement that meets level 4 criteria	1024
9	Any statement that meets level 4 criteria	4086
10	All statements	All Blocks
11	The database determines automatically if dynamic statistics are required	Automatically determined

 

 

보통 Sampling 수준은 Optimizer_dynamic_sampling 매개변수에 의해 제어되지만, 

Oracle Database 12c 릴리스 1에는 SQL plan directives 이 존재하므로 쿼리가 컴파일될 때 동적 통계 수집이 시작될 수도 있다. 이는  adaptive statistics 기능이며 Oracle Database 12c 릴리스 1의 데이터베이스 매개변수 Optimizer_adaptive_features(OAF) 및 Oracle Database 12c 릴리스 2의 Optimizer_adaptive_statistics(OAS)에 의해 제어된다. 즉, Oracle Database 12c 릴리스 1부터 DS는 관련 매개변수를 TRUE로 설정하여 특정 적응 기능이 활성화된 경우에 사용된다.

 

Database Version	Name of Database Parameter Controlling Relevant Adaptive Features	Default Value
Oracle Database 12c Release 1	optimizer_adaptive_features (OAF)	TRUE
Oracle Database 12c Release 2 Onwards	optimizer_adaptive_statistics (OAS)	FALSE

 

 

직렬 쿼리(간단한 OLTP성 쿼리) 은 일반적으로 단기간 실행되며 컴파일 시 DS 오버헤드가 전체 시스템 성능에 큰 영향을 미칠 수 있다.(if 문이 자주 하드 구문 분석되는 경우). 해당 시스템의 경우 

OAF=FALSE로 설정하는 것이 좋고 Oracle Database 12c 릴리스 2 이후부터는 기본 OAS=FALSE를 사용하는 것이 좋다.

 

parallel 쿼리 또한 일반적으로 더 많은 리소스를 사용므로, 더 나은 plan을 찾기위해 컴파일에 있어 더 많은 오버헤드 투자할 가치가 있다고 판단하기 때문에, 같은 쿼리여도 병렬 구문이 추가된다면 dynamic samplig이 사용되는 것을 볼 수 있다. 

 

DS는 일반적으로 복잡한 조건으로 인해 실행계획이 잘 못 되었을때 사용되는것이 권장된다, 하지만 전체 시스템에 있어 이것을 찾고 적용하기는 어렵기 때문에 alter session 을 사용하여 시기 적절히 parameter를 설정하는것이 바람직하다. 

 

수행이 빈번하여 컴파일 수행시간을 최대한 줄여야 하는  OLTP 쿼리같은 경우 DS는 사용되지않는것이 바람직하다.