고민의 흔적

Optimizer_mode

• RULE
  • RBO(Rule-based optimizer) 규칙 기반 옵티마이저
• CHOOSE
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
• FIRST_ROWS
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • 옵티마이저가 인덱스를 가장 많이 사용할 수 있도록 하는 모드 중 하나이다.
• FIRST_ROWS_N
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • N은 1, 10, 100, 1000개 행을 의미하고 최초의 N건의 응답 속도를 최적화할 때 사용하는 모드 중 하나이다. B2C 고객의 수가 엄청 많다면 FIRST_ROWS_1을 추천한다.
  • FIRST_ROWS_1을 사용한다면 optimizer_index_caching과 optimizer_index_cost_adj를 변경해주어야 한다.
  • optimizer_index_caching는 인덱스를 사용해 접근한 블록을 오라클 메모리, 버퍼 캐시 디비 캐시 사이즈 파라미터에서 찾을 수 있는 비율을 말한다. 보통 90 ~ 95
  • optimizer_index_cost_adj는 테이블을 Full Scan을 많이 할지 아니면 인덱스를 더 많이 사용할지를 결정하는 파라미터이다. 보통 30 ~ 35
• ALL_ROWS(DEFUALT)
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • 데이터의 모든 ROW를 빠르게 가져오고 테이블 Full Scan을 선호하는 경향이 있다.
  • 소형 프로그램이거나 DBA가 없을 경우 권장한다.

Adaptive Query Optimization

Oracle 자동 SQL 최적화 관리이고 Oracle 12C부터 적용 되었다. 데이터 베이스가 내부적으로 분석해서 SQL 실행 계획을 최적화하는 방식이다.

• 오라클 옵티마이저 통계 및 통계 어드바이저
• SQL Plan 관리
• 정확도가 비교적 높은 SQL 처리

Adaptive Query Optimization를 false로 처리할려면

• 완벽에 가까운 모델링(테이블 설계)
• 아름다운 인덱스 설계
• 확실한 테이블, 인덱스 통계정보 관리
• 환상적인 SQL 튜닝

Adaptive Query Optimization 기능을 제어하는 대표적인 파라미터

• optimizer_adaptive_plans (Defualt: true)
  • 옵티마이저가 adaptive_plan을 고려 대상으로 판단할지 여부를 결정한다.
• optimizer_adaptive_statistics (Defualt: false)
  • 통계 피드백을 비활성화해서 SQL Plan 변경을 방지한다.
• optimizer_adaptive_reporting_only (Defualt: false)
  • 어뎁티브 기능에 필요한 정보만 수집하고 SQL Plan을 변경할지 여부를 결정한다.
• _optimizer_adaptive_cursor_sharing (Defualt: true)
  • 변숫값에 따라서 SQL Plan을 변경하는 파라미터이다.
• _optimizer_aggr_groupby_elim (Defualt: true)
  • 특정 기준 조건으로 group by를 할 때 안에 group by 있고 밖에 group by가 또 있다고 하면 두 개의 group by가 아닌 하나의 group by만 사용하는 경우가 있다. 이럴 경우 문제가 있을 수도 있어 false로 놓고 많이 사용한다.
• _add_col_optim_enabled (Defualt: true)
  • 오라클 내부에서 테이블 칼럼을 추가하면 빠른 속도로 처리가 된다. 하지만 Not null 조건에 defualt라고 하면 약간 문제가 있을 수 있어서 false로 놓고 많이 사용한다.

참고

https://www.youtube.com/watch?v=Y7scjyhY_1E&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=4