[SQL 튜닝/옵티마이저] 🎈오라클 힌트란? 간단한 사용 방법

SQL 옵티마이저란?

사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를선택해주는 DBMS 핵심엔진

1.  사용자로부터 전달받은 쿼리를 수행하는 데 후보군이될 만한 실행계획을 찾아낸다.
2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3. 최저 비용을 나타내는 실행계획을 선택한다.

SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로표현한 것이 실행 계획(Execution Plan)이다.

이 미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

 

옵티마이저가 인덱스를 선택하는 근거는 비용이다.

비용(Cost)은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.

 

 

옵티마이저 힌트

통계정보에 담을 수 없는 데이터 또는 업무 특성을 활용해 개발자가 직접 더 효율적인 액세스 경로를 찾아낼 수도 있다.

이럴때 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다.

주석 기호에 '+'를 붙이면 된다.

SELECT /*+ INDEX(A 고객_PK) */
		고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID = '000008'

 

 

주의사항

 

1.힌트 안에 인자를 나열할 땐 ','(콤마)를사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안 된다.

/*+ INDEX(A A_X01) INDEX(B, B_X03) */ -> 모두 유효
/*+ INDEX(C), FULL(D) */ -> 첫 번째 힌트만 유효

 

2.테이블을지정할 때 스키마명까지 명시하면 안 된다.

SELECT /*+ FULL(SCOTT.EMP) */ -> 무효
FROM EMP

 

3.FROM절 테이블 명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다.

SELECT /*+ FULL(EMP) */ -> 무효
FROM EMP E

 

 

 

자주 사용하는 힌트 목록

분류	힌트	설명
최적화 목표	ALL_ROWS	전체 처리속도 최적화
	FIRST_ROWS(N)	최초 N건 응답속도 최적화
액세스 방식	FULL	Table Full Scan으로 유도
	INDEX	Index Scan으로 유도
	INDEX_DESC	Index를 역순으로 스캔하도록 유도
	INDEX_FFS	Index를 Fast Full Scan으로 유도
	INDEX_SS	Index Skip Scan으로 유도
조인순서	ORDERED	NL 조인으로 유도
	USE_MERGE	소트 머지 조인으로 유도
	USE_HASH	해시 조인으로 유도
	NL_SJ	NL 세미조인으로 유도
	MERGE_SJ	소트 머지 세미조인으로 유도
	HASH_SJ	해시 세미조인으로 유도
서브쿼리 팩토링	MATERIALIZE	WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도 ex) WITH /*+ MATERIALIZE */ T AS ( SELECT ... )
	INLINE	WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 ex) WITH /*+ INLINE */ T AS ( SELECT ...)
쿼리 변환	MERGE	뷰 머징 유도
	NO_MERGE	뷰 머징 방지
	UNNEST	서브쿼리 Unnesting 유도
	NO_UNNEST	서브쿼리 Unnesting 방지
	PUSH_PRED	조인조건 Pushdown 유도
	NO_PUSH_PRED	조인조건 Pushdown 방지
	USE_CONCAT	OR 또는 IN-List 조건을 OR-Expansion으로 유도
	NO_EXPAND	OR 또는 IN-List 조건을 OR-Expansion 방지
병렬 처리	PARALLEL	테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 ex) PARALLEL(T1 2) PARALLEL(T2 2)
	PARALLEL_INDEX	인덱스 스캔을 병렬방식으로 처리하도록 유도
	PQ_DISTRIBUTE	병렬 수행 시 데이터 분배 방식 결정 ex) PQ_DISTRIBUTE(T1 HASH HASH)
기타	APPEND	Direct-Path Insert로 유도
	DRIVING_SITE	DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정(Local 또는 Remote)
	PUSH_SUBQ	서브쿼리를 가급적 빨리 필터링하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도

 

 

 

출처 - 친절한 SQL 튜닝 BY 조시형