Oracle Query Optimizer Parameter
1. optimizer_max_permutations
optimizer_max_permutations integer 2000
: 옵티마이저가 실행계획을 수립할 때 플랜의 경우의 수 내에서 실행계획을 수립하게 된다.
이러 한 경우 플랜이 훨씬 좋은 경우가 있더라도, 그 플랜은 적용이 안될 수 있다.
8i까지는 Default가 80000 , 9i부터 Default가 2000 .
alter session set optimizer_max_permutations = 80000 ;
2. optimizer_index_cost_adj
default : 100
이 파라메터는 1~10000사이의 값을 설정할수 있으며 Index access 와 Full Table Scan 에서 사용되 는 Physical I/O 의 Cost의 상대적인 비율을 설정하는 파라메터이다.
100인경우에는 두개의 access 모두 동일한 비율로 Cost를 계산하게되며 50으로 설정하여 Index access를 하는 경우 기존의 cost의 1/2로 cost를 계산하게 되어, Index access 방식으로 execution plan이 수립되어질 확률이 높아지게 된다.
Optimizer_mode를 first_rows로 설정하는 경우에는 내 부적으로 optimizer_index_cost_adj 는 10으로 계산되어진다.
따라서 Index access를 이용하여 execution plan을 수립할 확률이 높아지게 되면 Join인 경우에는 Nested Loops Join으로 execution plan이 수립될 가능성이 높아지게 된다.
3. optimizer_index_caching
default : 0
이 파라메터는 0 ~ 99 사이의 값을 설정 할 수 있으며, 0일 경우에는 Index를 이용해서 Access되는 block들이 SGA의 Buffer Cache 영역에서 찾을 수 있는 비율이 0% 란 의미이다.
즉 모든 index access 는 DISK I/O 를 발생하여 Physical Reads 를 수행 한 후에 Buffer cache로부터 Logical reads를 수행한다는 의미가 된다.
이 파라메터는 CBO 에서 Index block 을 access 할 때 소 요되는 Cost를 계산하기 위한 비율로서 사용 되어진다.
4. optimizer_dynamic_sampling
< 10g에서의 RBO Optimizer Mode를 사용할 수 있는가? >
— Database Level에서 RBO 지원하지 않으며, Session Level에서 /*+ Rule */ 힌트에 의해서 일부의 RBO Path가 지원이 되고 있음.
이후 버젂에서는 RBO를 지원하지 않을 것임.
RBO는 하위 버젂(v7.3이젂)과의 호홖성을 위하여 지원하는 것이며, 10g의 init Parameter에 의해서 기본적으로 통계정보가 없는 테이블에 대한 Plan 해석도 RBO로 되지 않으며, CBO 로 해석이 된다.
< Init Parameter >
– optimizer_dynamic_sampling = 2 (default)
이 파라미터가의 값이 2로 설정되어 있을 경우 Query parsing time에 64 blocks가 Query Optimization을 위해서 Sampling 되어 진다.
기본적으로 활성화 되어 있으며, 아래와 같이 설정할 경우에는 비활성화 시킬 수 있다.
< 변경방법 >
– optimizer_dynamic_sampling = 0
– optimizer_features_enable = (9.0.1 or 이하 버전)
5. _optim_peek_user_binds=FALSE
: _optim_peek_user_binds=TRUE일 경우 Bind Value가 있는 SQL의 해석과 FALSE일 경우에 해석되 는 것에 많은 차이가 있다.
TRUE일 경우 Bind Value에 인덱스가 있고, 해당 SQL이 Bind Value를 이용해서 해석이 되어야 하는 경우 Query Optimizer가 Bind Value의 Column Histogram을 참조하 여 SQL문을 해석하고 실행하게 됨.
이때, 평상시 조회가 되지 않는 조건으로 Binding 이 될 경우 평상시의 PLAN 과 상이하게 해석될 수 있으며, 해당 SQL의 Cursor가 Aging Out 되지 않는 동안 에는 PLAN이 유지되게 된다.
Aging Out 된 이후에는 또 다시 Column Histogram을 참조하여 PLAN을 세우게 되어 이전과는 상이한 실행계획이 수립되어 SQL이 실행될 수 있다. False일 경우 에는 일반적인 Query Optimizing을 하게 된다.
(필수적용사항)
alter session set “_optim_peek_user_binds”=false ;
6. _b_tree_bitmap_plans = FALSE
: Query Optimizer가 SQL 해석할 때 Where젃에 여러 조건이 있고, 해당 조건 컬럼들에 Index 가 각각 생성되어 있을 경우 B*tree Index 를 Bitmap으로 conversion 하여 PLAN을 수립하여 실행함.
이럴 경우 TYPE 이나 Code성 컬럼의 경우 B*tree Index range scan으로 해석되는 경우보다 성능이 나을 수 있으나 일반적으로 성능이 저하되는 경우가 더 많음.(필수적용사항)
7. optimizer_mode = ‘FIRST_ROWS_100’
: 10g R1에서는 optimizer_mode를 first_rows로 설정과 관렦된 Bug이 있었음.
그리고, 온라인 (OLTP) 환경에서는 First_rows 설정이 아닌 First_rows_100정도 세팅하는 것이 유리함.
8. _optimizer_sortmerge_join_enabled = FALSE
: Merge Join Cartesian(Merge Join)을 없애기 위한 파라미터 세팅으로, Cartesian Product는 Join Ker가 없이 Join이 발생할 경우 발생되는 것이 정상이나 비정상적으로 Merge Join Cartesian이 발생되어 SQL들의 실행계획이 비정상적으로 수립 및 실행되어 성능저하가 많이 발생하여 적용을 함.
주의할 점은 cartesian Product가 발생할 경우 Nested Loop로 PLAN에 해석되므로, PLAN 해석 할 때에 유의하여야 함. (필수적용사항)
9. _optimizer_skip_scan_enabled = FALSE
: Index Skip Scan이 되지 않게 하기위한 파라미터임.
-> Index Skip Scan이 필요한 경우에는 10g 부터 지원되는 opt_param() 힌트를 사용하여 SQL Level에서 Index Skip Scan이 되게끔 바꿔주면 됨.
( /*+ opt_param(‘_OPTIMIZER_SKIP_SCAN_ENABLED’,’FALSE’) */ )
-> Index Skip Scan이 False일 경우 Skip Scan이 되지 않지만, Index Column인 경우에 Index Filter 가 되기 때문에 성능상 많은 영향은 없음.