[DB] 옵티마이저(optimizer)란 무엇인가?

옵티마이저(optimizer)

개발자를 대신해서 프로그래밍 해주는 DBMS에서의 프로그래머 같은 존재이다

즉 개발자가 SQL을 작성시 옵티마이저는 최적의 실행계획(현재 비용기반 옵티마이저를 대부분 사용하고 있다.)을 세우고 프로시저를 생성해준다

 

사용자 -> SQL 작성 -> 옵티마이저 -> 실행계획 선택 -> 프로시저 생성

 

사용자가 아래의 SQL을 작성 했다 가정해보자

SELECT *
FROM EMP E
WHERE E.EMPNO = 9999

 

옵티마이저

• EMP라는 테이블에서 EMPNO가 9999인 데이터를 찾기 위해서 실행계획을 찾아낸다.
• 데이터 딕셔너리(Data Dictionary)에서 미리 수집해 놓은 정보들을 바탕으로 실행 계획의 예상비용을 산정한다.
• 가장 적은 비용의 실행계획을 선택

 

즉, 옵티마이저는  개발자가 원하는 작업을 판단하고 최적의 경로를 선택하여 프로시저를 생성해주는 DBMS의 엔진이다.

 

 

실행계획(Execution Plan)

실행계획이란, 옵티마이저 찾아낸  처리절차를 사용자가 확인할 수 있도록 트리구조로 표현한 것이다.

아래의 사진이 위의 EMPNO = 9999인 데이터를 찾기위해 옵티마이저가 선택한 실행 계획이다.

실행계획

 

옵티마이저가 실행 계획을 선택하기 위해 비용을 정하는 기준은 무엇일까?

비용(Cost)란, 쿼리를 수행하는데 소요되는 일량 또는 시간이다.

• I/O 요청 횟수만으로 비용 평가(예전의 방법)
• I/O 일량을 시간으로 표현
• CPU 연산 시간을  더하여 상대전 시간 개념으로 환산한 값
• 테이블 통계, 컬럼 통계, 인덱스 통계

여기서 실행계획을 바꾸기 위해서는 어떻게 해야 할까?

바로 옵티마이저 힌트를 통하여 옵티마이저에게 더 좋은 선택을 할 수 있도록해야한다.

 

옵티마이저 힌트

SQL 작성시 함께 작성하여 옵티마이저에게 실행계획을 선택하도록 지시 하는 것이다

(옵티마이저도 결국 프로그램이라 완벽한 실행계획을 선택할 수 없다.)

 

힌트 사용법

잘 작성한 힌트	잘못된 힌트
/*+ HINT */	/*+ INDEX(A A_IDX01), INDEX(B B_IDX01) */ (, 사용 불가)
/*+ INDEX(A A_IDX01)  INDEX(B B_IDX01) */	/*+ FULL(스키마명.테이블명) */ (스키마명 명시시 무효)
--+ HINT (비추합니다....)	/*+ FULL(EMP) */ (해당 경우의 경우 ALIAS 작성 시 무효)

 

옵티마이저가 힌트를 무시하는 케이스는 아래와 같다

• 문법적으로 잘못 적은 힌트
• 잘못된 참조값 사용
• 논리적으로 불가능할 경우
• 의미적으로 맞지 않은 힌트 사용
• 옵티마이저에 의해 내부적으로 쿼리가 변하는 경우