[Oracle] Optimizer Mode 란?

오라클 Optimizer 는 (테이블or 컬럼)통계정보를 보고 (operator에서 return 되는 row수를 예측하고 ) 실행계획을 만든다 

 

통계정보를 추출하는 방법은?

=> analyze table , DBMS_STAT

 

그럼 통계정보는 어디서 확인할 수 있을까?

=> 아래의 dictionary View 에서 확인 할 수 있다

USER_TABLE , USER_TAB_COLUMNS ,USER_TAB_STATISTICS,  USER_TAB_COL_STATISTIC , 

 

column 통계정보에는 어떤 데이터가 있는가?

=> column의 Distinct values 의 수 , 최소값, 최대값 등의 정보가 있다  

 

optimizer mode 란?

설정 시점은?

1. 초기 매개변수 파일인 init.ora 에서 변경 가능 

2. alter session, alter system 명령으로도 변경 가능 

3. oracle HIINT 에서 사용 가능 ex) /*+ FIRST_ROWS_10 */ 

 

확인하는 방법은?

show parameter OPTIMIZER_MODE; 로 확인 가능 하다 

 

 

OPTIMIZER_MODE  옵션 값 

SQL의 실행계획을 생성한 방법으로 'Optimizer' 가 어떤 기준으로 실행계획을 세울지 결정하게 한다 

OPTIMIZER_MODE = { FIRST_ROWS_[1 | 10 | 100 | 1000] | FIRST_ROWS | ALL_ROWS }

 

• ALL_ROWS: 19c의 기본값 / 테이블의 통계정보의 유무와 상관없이  CBO(cost based optimizer=비용중심) 으로 동작한다. 즉 SQL문의 처리하는데 걸리는 시간을 최소로 하는게 목표 
• FIRST_ROWS: 테이블의 통계정보의 유무와 상관없이 동작 / CBO 이용하여 처음 N 개의 행을 빠르게 추출하는게 목표 . 한건만 처리할 때 유용하다 

공식 문서에는 없지만 사용할 수 있는 옵션값이 있다 

• RULE: 테이블의 통계정보의 유무와 상관없이 동작/ RBO(rule b무ased optimizer) 로 동작 / ex) index 있는 column을 where절에 두면 무조건 index를 탄다 (CBO와 상관없이 )
• 옵티마이저가 규칙 기반 접근법을 사용합니다. 현대의 Oracle 버전에서는 잘 사용되지 않으며, 코스트 기반 옵티마이저(CBO)가 기본적으로 사용됩니다.
• CHOOSE: 통계정보가 있으면 CBO, 없으면 RBO로 동작 
• 시스템 매개 변수와 통계 정보를 기반으로 옵티마이저 모드를 선택합니다. 통계가 있을 경우 ALL_ROWS 또는 FIRST_ROWS와 유사하게 동작합니다.

 

OPTIMIZER_MODE (oracle.com)

 

Database Reference

 

 

특정 user는 항상 ALL_ROWS로 하고 싶다면? 

트리거를 사용해서 LOG IN 시에 'ALTER SESSION' 명령으로 변경시킨다 

CREATE OR REPLACE TRIGGER Tr_setOptimizerMode
AFTER LOGON
ON DATABASE
BEGIN
 IF USER LIKE 'SCOTT%' THEN
 EXECUTE IMMEDIATE 'alter session set optimizer_mode=all_rows';
 END IF;
END;

/