CheerUp_Cheers
옵티마이저 본문
SQL 최적화의 원리
POINT 1. 옵티마이저와 실행계획
[1] 옵티마이저란?
- SQL개발자가 SQL을 작성하여 실행 시, 옵티마이저는 SQL을 어떻게 실행할 것인지 계획.
-> SQL 실행계획을 수립과 실행(동일 결과도 실행계획에 따라서 성능 달라짐)
-> SQL 실행계획과 실행을 하는 소프트웨어
[2] 옵티마이저 특징
- 여러 실행 계획중 최저 비용의 계획을 선택하여 SQL 실행.
- 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계등의 정보를 사용해서 예상비용산정.
[3] 옵티마이저 필요성
- 다음과 같은 경우 비용이 더 커짐
1) EMP 테이블 셀렉
2) AND (찾은 행과 동일한 것을 찾기 위해)
3) DEPT 테이블 셀렉
-> 반대로 했을 경우, 더욱 싼 비용으로 가능
- 비효율적인 실행계획을 수립하면, SQL개발자는 옵티마이저에게 실행계획을 변경하도록 요청가능(HINT)
[4] HINT(힌트)
- 정의
SQL 튜닝의 핵심 부분으로 일종의 지시 구문.
오라클이 항상 최적비용을 산정하기는 어려우니, 요청.
사용자가 SQL문장의 높은 선택도의 인덱스를 알고 있을 경우, 옵티마이저보다 효율적인 계획이 가능하기 때문.
- 사용이유
액세스 경로, 조인 순서, 병렬 및 직렬 처리, Optimizer의 목표(Goal)를 변경 가능하다.
데이터 값을 정렬해야 하는 경우, 힌트의 사용이 필요하다.
또한, 드라이빙 테이블을 원하는 대로 선정하고자 할 때도 사용된다
드라이빙 테이블 : TABLE에 대한 JOIN시 먼저 ACCESS되서 ACCESS PATH를 주도하는
TABLE을 DRIVING TABLE이라 한다.
인덱스 유무, 우선순위, 테이블 지정순위에 영향을 받으며, ACCESS 순서에 따라 속도의 차이가 남.
- 사용법
쿼리 서두에 힌트를 명시하는 것.
주석과 달리 '+'가 붙음.
-- index_asc, index 힌트 : 인덱스 영역에서 순방향으로 스캔 하라는 뜻
SELECT /*+ index_asc(e idx_myemp1_ename) */
EMPNO, ENAME, SAL
FROM MYEMP1 e
WHERE ENAME >= '가'
출처: https://devuna.tistory.com/35 [튜나 개발일기📚][SQL 튜닝] 오라클 힌트(hint)의 개념/ 사용법/자주쓰이는힌트 정리
[SQL 튜닝] 오라클 힌트(hint)의 개념/ 사용법 정리 💡 힌트(Oracle Hint)란 무엇일까? 힌트란 SQL 튜닝의 핵심 부분으로 일종의 지시 구문이다. 즉, 오라클 옵티마이저(Optimizer)에게 SQL문 실행을 위
devuna.tistory.com
POINT 2. 옵티마이저 종류
[1] 옵티마이저의 실행 방법
- SQL 실행, 파싱을 실행하여 구문분석과 SQL문법 검사
- 규칙, 비용 기반으로 실행계획 수립.
- 기본적으로 비용 기반 옵티마이저로 실행 계획 수립,
- 실행 완료가 되면 데이터 인출.
[2] 옵티마이저 엔진
#규칙 기반 옵티마이저
- 정의
규칙 기반 옵티마이저는 실행계획 시, 15개의 우선순위를 기준으로 실행계획 수립.
최신 오라클은 규칙기반보다는 비용기반 옵티마이저
낮은 순위가 높은 우선순위를 나타냄.
- 규칙 1. Single row by rowid
- ROWID를 통해서 테이블에서 하나의 행을 액세스하는 방식이다.
- ROWID는 행이 포함된 데이터 파일, 블록 등의 정보를 가지고 있기 때문에 다른 정보를 참조하지 않고도 바로 원하는 행을 액세스할 수 있다.
- 하나의 행을 액세스하는 가장 빠른 방법이다.
- 규칙 4. Single row by unique or primary key
- 유일 인덱스(Unique Index)를 통해서 하나의 행을 액세스하는 방식이다.
- 이 방식은 인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행을 액세스한다.
- 규칙 8. Composite index : 복합 인덱스에 동등('=' 연산자) 조건으로 검색하는 경우이다.
- 예를 들어, 만약 A+B 칼럼으로 복합 인덱스가 생성되어 있고, 조건절에서 WHERE A=10 AND B=1 형태로 검색하는 방식이다.
- 복합 인덱스 사이의 우선 순위 규칙은 다음과 같다. 인덱스 구성 칼럼의 개수가 더 많고 해당 인덱스의 모든 구성 칼럼에 대해 '='로 값이 주어질 수록 우선순위가 더 높다.
- 예를 들어, A+B로 구성된 인덱스와 A+B+C로 구성된 인덱스가 각각 존재하고 조건절에서 A, B, C 칼럼 모두에 대해 '='로 값이 주어진다면 A+B+C 인덱스가 우선 순위가 높다.
- 만약 조건절에서 A, B 칼럼에만 '='로 값이 주어진다면 A+B는 인덱스의 모든 구성 칼럼에 대해 값이 주어지고 A+B+C 인덱스 입장에서는 인덱스의 일부 칼럼에 대해서만 값이 주어졌기 때문에 A+B 인덱스가 우선 순위가 높게 된다.
- 규칙 9. Single column index
- 단일 칼럼 인덱스에 '=' 조건으로 검색하는 경우이다. 만약 A 칼럼에 단일 칼럼 인덱스가 생성되어 있고, 조건절에서 A=10 형태로 검색하는 방식이다.
- 규칙 10. Bounded range search on indexed columns
- 인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색하는 방식이다.
- 이러한 연산자에는 BETWEEN, LIKE 등이 있다. 만약 A 칼럼에 인덱스가 생성되어 있고, A BETWEEN '10' AND '20' 또는 A LIKE '1%' 형태로 검색하는 방식이다.
- 규칙 11. Unbounded range search on indexed columns
- 인덱스가 생성되어 있는 칼럼에 한쪽 범위만 한정하는 형태로 검색하는 방식이다.
- 이러한 연산자에는 >, >=, <, <= 등이 있다. 만약 A 칼럼에 인덱스가 생성되어 있고, A > '10' 또는 A < '20' 형태로 검색하는 방식이다.
- 규칙 15. Full table scan : 전체 테이블을 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출한다.
- 규칙기반은 인덱스를 이용한 액세스가 규칙 15.의 전체 테이블 액세스 보다 우선순위가 높음!
- SQL문의 이용가능한 인덱스가 존재한다면, 항상 인덱스를 이용한 실행계획을 짜게 됨.
- 사용
SQL문의 힌트를 통해서 사용이 가능함!
SELECT /*+ RULE */ FROM EMP
WHERE ROWID = 'ROWID';
#비용 기반 옵티마이저
- 정의
규칙기반이 어떠한 정해진 규칙에 따라 만들어졌을 경우, BETWEEN의 현실적인 데이터 건수등을 예측 못함
-> 이러한 단점을 극복하기 위해 출현.
비용 기반 옵티마이저는 오브젝트 통계 및 시스템 통계를 사용하여 총 비용 계산.
총비용이란? SQL문을 실행하기 위해서 예상되는 소요시간 혹은 자원의 사용량.
단, 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생 가능하다.
POINT 5. 옵티마이저 조인
[1] Nested Loop 조인
- 하나의 테이블에서 데이터를 먼저찾고 그다음 테이블을 조인 하는 방식
- 먼저 조회되는 테이블은 외부테이블(Outer), 내부테이블(Inner)
- 외부 테이블의 크기가 작은것을 먼저 찾는것이 중요, 데이터 스캔 범위를 줄이기 위함.
- Nested Loop조인은 랜덤 어세스가 많이 발생, 이는 성능 지연이 발생.
/* EMP 테이블을 먼저 FULL SCAN하고, 다음 DEPT 테이블을 FULL SCAN 하여 Nested Loop 조인 */
/* 아래의 ordered 힌트는 FROM 절에 나오는 테이블을 순서대로 조인하라!는 뜻 */
SELECT /*+ ordered use_nl(b) */
FROM EMP a, DEPT b
WHERE a.DEPTNO = b.DEPTNO
AND a.DEPTNO = 10;
[2] Sort Merge 조인
- 두개의 테이블을 SORT_ARREA라는 메모리 공간에 모두 로딩후 SORT 수행.
- SORT가 완료되면 병합
- SORT가 발생하기 때문에 데이터양이 많아지면 성능이 떨어짐
- 정렬 데이터양이 너무 많으면 정렬은 임시 영역(디스크), 성능이 급격히 떨어짐.
/* use_merge 힌트는 ordered 힌트와 같이 사용해야 함 */
SELECT /*+ ordered use merge(b) */
FROM EMP a, DEPT b
WHERE a.DEPTNO = b.DEPTNO
AND a.DEPTNO = 10;
[3] Hash 조인
- 두 개의 테이블 중, 작은 테이블은 HASH 메모리에 로딩하고 두개의 테이블의 조인키를 이용해 해시 테이블 생성.
- 해시 함수를 사용해서 주소를 계산하고, 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU연산이 많음
- Hash 조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다.
SELECT /*+ ordered use_hash(b) */
FROM EMP a, DEPT b
WHERE a.DEPTNO = b.DEPTNO
AND a.DEPTNO = 10;