DBMS - Optimizer, Hint

Optimizer

Oracle DBMS에서는 옵티마이저(Optimizer)를 제공하고 있다.옵티마이저란 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 DBMS 내부의 핵심 엔진이다. 

 

사용자가 쿼리문으로 결과를 요청하면 이를 생성하는 데 필요한 처리 경로는 DBMS에 내장된 옵티마이저가 자동으로 생성되고 최적의 실행 방법을 생성하여 주는데 이를 실행계획이라고 한다.

 

 

SQL최적화 과정

각 실행 계획에 대한 예상 비용은 다음과 같이 구성되어 있다.

• COST : 예상 수행 시간, 쿼리를 수행하는 데 소요되는 시간 또는 일량
• CARDINALITY : 실행 결과의 건수

 

옵티마이저가 사용자의 SQL 요청을 최적화하는 과정으로는 다음과 같다.

• 사용자가 작성한 쿼리문 수행을 위해, 실행될만한 실행 계획을 찾는다.
• 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행 계획의 예상 비용을 산정한다.
• 각 실행계획을 비교해서 최저 비용을 갖는 하나를 선택하여 실행한다.

 

 

옵티마이저의 종류

• 규칙 기반 옵티마이저(Rule-Based Optimizer) : 미리 정해진 규칙에 따라 실행하는 옵티마이저로 Oracle 10g 버전부터는 사용을 하지 않는다.
• 비용 기반 옵티마이저(Cost_Based Optimizer) : 비용이 가장 낮은 실행계획을 선택

 

비용기반 옵티마이저(CBO)는 다음의 과정을 통해 SQL문을 실행하게 된다.

• PARSER : 문법오류검사, 코드로 변경
• OPTIMIZER : 총 비용 계산, 실행계획을 생성
• ROW_SOURCE GENERATOR : 실행가능 코드로 변경
• SQL_ENGINE : 실행

 

 

Oracle에서는 실행 가능 코드로 변경하기 전에 Optimizer를 통해 실행계획을 먼저 생성한다. 즉, 내가 A라는 실행계획을 가진 쿼리문을 질의하였을 때, 옵티마이저가 같은 결과를 내는 B라는 실행계획이 더 효율적이라고 판단이 되면 B 실행 계획에 대해 실행가능 코드로 변경 후 실행을 시키는 것이다.

 

 

 

Hint

DML문법에 실행 순서는 다음의 순서로 실행이 진행된다.

FROM >> WHERE >> GROUP BY >> HAVING >> SELECT >> ORDER BY

여기서 정렬을 해주는 order by가 가장 늦게 실행되고  cost 또한 상당히 크다. Hint가 필요한 이유 중 하나로, order by의 높은 cost를 들 수가 있다.

 

Order by는 정렬을 할 때 해당 칼럼의 각 값에 대해 index를 부여하고 정렬을 진행하게 되는 데 이 과정이 상당한 cost를 발생시키는 것이다.

 

이러한 부담을 줄이기 위해서 나온 것이 Hint 문법이다. Hint 문법은 SELECT 문에 실행하고 싶은 계획을 전달할 때 사용하는 문법으로 잘못 작성되어도 실행할 때에는 무시되며 별도의 오류가 발생하지 않는 장점이 있다.

 

Hint문을 통해서 미리 index를 부여하게 되며 order by를 진행할 때 훨씬 적은 cost로 실행시킬 수 있는 것이다. (데이터 양이 적을 때에는 Hint가 효율적이지는 않지만 커질수록 효율적)

 

 

Hint 사용 방법

사용 위치로는 SELECT문 첫 부분이고, 사용 방법으로는 다음과 같다.

SELECT /*+ [실행하고 싶은 계획] */ COL1, COL2 FROM ...

/*+로 시작되며 */로 마치는 것이다. 예를 들어, 내림차순 인덱스를 부여싶다면 다음과 같이 사용하면 된다.

SELECT /*+ INDEX_DESC(테이블명 PK명)*/ COL1, COL2, ... FROM 테이블명;

 

 

 

2023.08.17 - [Programming 공부/DBMS] - DBMS - JOIN

DBMS - JOIN