DATOR


옵티마이져의 행동에 영향을 미칠 수 있는 파라미터 RDBMS 개론


오라클 비용기반 옵티마이져(CBO)의 행동에 영향을 미칠 수 있는 초기화 파라미터들은 아래와 같습니다.

 

ALWAYS_ANTI_JOIN

B_TREE_BITMAP_PLANS

COMPLEX_VIEW_MERGING

DB_FILE_MULTIBLOCK_READ_COUNT

FAST_FULL_SCAN_ENABLED

HASH_AREA_SIZE

HASH_JOIN_ENABLED

HASH_MULTIBLOCK_IO_COUNT

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_MODE

OPTIMIZER_GOAL

OPTIMIZER_PERCENT_PARALLEL

OPTIMIZER_MAX_PERMUTATIONS

OPTIMIZER_SEARCH_LIMIT

PARTITION_VIEW_ENABLED

PUSH_JOIN_PREDICATE

SORT_AREA_SIZE

SORT_DIRECT_WRITES

SORT_WRITE_BUFFER_SIZE

STAR_TRANSFORMATION_ENABLED

V733_PLANS_ENABLED

CURSOR_SHARING

 

많은 조인을 수반하는 SQL 하나를 파싱하고 최적화하기 위해서는 CBO는 무수히 많은 실행계획들을 생성해서 평가해 보아야만 하는데, 이러한 CBO의 일량을 조절하기 위해 사용할 수 있는 파라미터가 OPTIMIZER_SEARCH_LIMIT과 OPTIMIZER_MAX_PERMUTATIONS입니다.

그리고 SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT 등은 옵티마이져가 해시조인과 머지 조인 그리고 테이블 스캔의 비용을 평가하는데 큰 영향을 미칩니다.

 

모든 파라미터를 다 설명할 수는 없으므로, 이번 칼럼에서는  OPTIMIZER_INDEX_CACHING과 OPTIMIZER_INDEX_COST_ADJ에 대해서만 자세히 살펴보겠습니다.

 

옵티마이져는 Nested Loop 조인시 Inner(=Drived) 테이블의 데이터를 매번 디스크에서 읽어온다는 가정을 하고 비용을 계산합니다. 하지만 실제로는 DB Buffer Cache에 존재하는 블록을 읽을 확률이 더 높기 때문에 예상보다 훨씬 적은 수의 디스크 I/O가 발생하게 되고, 특히 Inner 테이블을 액세스하기 위해 경유하는 인덱스의 Clustering Factor가 매우 좋다면 인덱스 뿐만 아니라 테이블 데이터 블록 I/O도 훨씬 줄게 됩니다. 따라서 Nested Loop 조인에 대한 옵티마이져의 비용산정은 가장 최악의 상황을 가정한 것이 되고, 따라서 Sort Merge Join이나 Hash Join이 선택될 가능성이 높아지게 됩니다. 이러한 예측치와 실측치 간의 차이를 보정하기 위해 OPTIMIZER_INDEX_CACHING와 OPTIMIZER_INDEX_COST_ADJ의 값을 조정해 주면 효과적인데, 좀더 자세히 설명드리겠습니다.

 

OPTIMIZER_INDEX_CACHING 파라미터는 디폴트 0으로 설정되어 있으며 가능한 값의 범위는 0~100입니다. 이 값은 인덱스 블록이 DB Buffer Cache에서 찾아질 가능성을 의미하는데, 예를 들어 100으로 설정할 경우 SQL 조건에 따라 읽어들여야 하는 인덱스 블록이 100% DB Buffer Cache에서 찾아질 것을 가정하는 것이고 0으로 설정할 경우 Buffer Cache에서 찾아질 가능성이 0이라고 가정하는 것입니다. 따라서 이 파라미터를 각자의 시스템 실정에 맞게 조정한다면 옵티마이져가 이전보다 더 좋은 실행계획을 수립할 가능성이 매우 높아집니다.

 

OPTIMIZER_INDEX_COST_ADJ 파라미터는 디폴트 100으로 설정되어 있으며 가능한 값의 범위는 1부터 10000 까지입니다. 이 파라미터는 인덱스 탐색비용에 대한 평가를 조정하기 위해 사용하는 것으로서, 디폴트 설정값 100은 인덱스에 대한 액세스 비용이 보통의 비용모델에 근거해서 평가될 것이고, 10으로 설정한다면 보통의 인덱스 액세스 비용의 1/10로 평가될 것임을 의미합니다. 따라서 이 값이 작을수록 인덱스를 경유한 테이블 액세스(à Single-Block I/O) 비용이 더 적게 평가될 것이고, 클수록 인덱스를 경유하지 않는 Full Tabe Scan(à Multiblock I/O)의 액세스 비용이 더 적게 평가될 것입니다. 바꾸어 말하면, 이 파라미터는 Single-Block I/O와 Multiblock I/O에 대한 상대적인 평가로 해석할 수 있고, 따라서 테이블 데이터 블록이 평균적으로 얼마만큼 캐싱되어 있는지에 대한 의미로 해석할 수도 있습니다. 앞에서 설명한 OPTIMIZER_INDEX_CACHING이 인덱스가 캐싱되어 있을 확률을 가리키는 것처럼 말입니다.

사실 이 두 파라미터의 기본설정은 DW 환경에나 적합한 것입니다. 따라서 보다 일반적이라고 할 수 있는 OLTP 성향의 시스템에서는 OPTIMIZER_INDEX_CACHING을 훨씬 높게 설정하고 OPTIMIZER_INDEX_COST_ADJ를 줄여 줄 필요가 있습니다.

오해하지 말아야 할 것은 이들 파라미터를 조정하는 것이 SQL을 더 빠르게 수행되도록 한다거나 인덱스가 얼마만큼 캐싱될지를 조절하는 것이 아니라는 사실입니다. 다만 선정된 실행계획들을 평가하고 선택하는 과정에 영향을 주기 위해 사용하는 것으로서, 옵티마이져가 아무리 똑똑하더라도 사람만이 판단할 수 있는 그런 정보들을 옵티마이져에게 전달해 줌으로써 옵티마이져가 스스로 최선의 선택을 해 나갈 수 있도록 도와주기 위한 기능입니다.     

Leave Comments