[Database] SQL 튜닝 비법 Oracle 기반 (2) [실행계획, FULL TABLE SCAN, ROWID SCAN, INDEX SCAN, INDEX SCAN 종류, PLAN_TABLE, SQL TRACE]

📝실행계획

옵티마이저가 SQL문을 가장 효율적으로 처리하기 위해 실행하는 계획을 선택하는 것을 의미한다.

 

📝FULL TABLE SCAN

테이블 전체 데이터를 읽어 조건에 맞는 데이터를 추출하는 방식

 

📝ROWID SCAN

ROWID기준으로 데이터를 추출하는 방식으로 단일행 접근시 가장 빠르다

 

📝INDEX SCAN

원하는 데이터 추출을 위해 인덱스를 사용하여 검색하는 방식

 

 

📝INDEX SCAN이 무조건 좋은가?

FULL TABLE SCAN이 무조건 성능에 좋지 않고 INDEX SCAN이 성능에 좋다는 것이라는 생각은 잘못 된 것이다.

데이터가 많은 테이블로부터 일부의 데이터를 추출해야하는 INDEX SCAN이 유용한 건 사실이지만 반대로 테이블에 있는 대부분의 데이터를 추출해야하면 FULL TABLE SCAN이 더 효과적일 수 있다.

 

📝INDEX SCAN 종류

INDEX UNIQUE SCAN

1. UNIQUE INDEX를 이용하여 필요한 데이터 블록을 접근하는 방식
2. 한건 이하의 ROWID를 반환하는 INDEX SCAN 방식으로 모든 컬럼의 조건절에 '='로 명시된 경우를 의미한다.

 

INDEX RANGE SCAN

1. 인덱스를 이용하여 필요한 데이터 블록을 접근하는 방식 중 가장 일반적인 형태
2. 한건 이상의 필요한 데이터가 포함된 일정 범위의 인덱스 블록을 오름차순으로 접근하는 방식
3. 동일 INDEX KEY 값에 대해서는 ROWID 기준으로 오름차순 정렬
4. INDEX UNIQUE SCAN을 제외한 모든 INDEX SCAN에 사용, 대소비교(<.>)가 하나라도 들어간 경우 예) menu_price > 2000
5. 와일드 카드 문자(%)가 조건 값 뒤에 존재하는 경우 예) like 'abc%'

INDEX RANGE SCAN DESCENDING

1. INDEX RANGE SCAN과 기본적인 접근 방식은 동일
2. 오름차순이 아닌 내림차순으로 인덱스 블록을 접근하는 방식
3. INDEX RANGE SCAN을 수행함과 동시에 ORDER BY DESC절을 만족하는 경우

INDEX SKIP SCAN

1. 결합 인덱스의 선행 컬럼을 건너뛰는 형태로 인덱스 블록에 순차적으로 접근
2. 선두 칼럼의 고유 값의 개수가 적고 후행 칼럼의 고유 값이 많을 때 효과적입니다 예) 부서A, B, C가 있고 급여가 500만원 ~ 700만원인 사람을 추려내야할 때 인덱스를 (부서, 급여)로 만들어서 A부서에서 500만원 ~ 700만원까지 [인덱스라서 정렬된 상태] 찾은 후 그 앞이나 뒤는 안 찾고 B부서로 넘어가는 방식

 

INDEX FULL SCAN

1. 인덱스 리프 블록 전체를 SCAN하는 방식
2. 단일 블록 순차 접근으로 병렬 처리는 불가능하나 인덱스 키순 정렬이 보장된다.
3. 모든 데이터를 가져오는데 조건절은 없이 정렬이 있는 경우에 해당한다 예) SELECT * FROM MEMBER ORDER BY MEMBER_ID

 

INDEX FAST FULL SCAN

1. 인덱스 리프 블록 전체를 SCAN하는 방식
2. 병렬 처리가 가능해 빠르나 인덱스 키 순으로 정렬은 보장 불가능
3. INDEX FAST FULL SCAN은 TABLE FULL SCAN의 INDEX VERSION이라고 생각하면 좋다.
4. HINT를 이용해 유도해야하는 걸로 알고 있음

 

 

📝옵티마이저가 FULL TABLE SCAN을 선택하게되는 이유

1. 조건절에서 비교한 컬럼에 인덱스가 없는 경우
2. 인덱스는 있으나 데이터가 테이블의 많은 양을 차지해 FULL TABLE SCAN의 비용이 INDEX SCAN보다 적다고 판단하는 경우
3. 최적한 인덱스는 있으나 테이블 데이터 자체가 적어 FULL TABLE SCAN 비용이 INDEX SCAN보다 적다고 판단하는 경우

 

📝실행 계획 테이블 (PLAN_TABLE)

ORACLE에서 실행 계획을 테이블에 저장하게 되는데 해당 테이블 이름은 PLAN_TABLE이다.

 

EXPLAIN PLAN -- EXPLAIN PLAN 선언부
SET STATMENT_ID = 'EP_TEST' -- SQL에 임의 ID 부여
FOR
SELECT E.empno, E.ename, D.deptname
FROM emp E, dept D
WHERE E.deptno=D.deptno;

해당 SQL은 SQL의 실행 계획을 저장하는 것일 뿐 실제로 SQL이 실행되는 것이 아니다.

이거는 옵션에 따라 직접 실행시키면서 SQL 실행 계획을 저장할 수도 있다.

 

PLAN_TABLE에서 DBMS_XPLAN 패키지를 이용해 조회가 가능하다

 

실행 계획 순서는 위에서 아래로 진행되며 가장 들여쓰기가 많이 된 곳에서 부터 시작되며 실행 순서는 위에서 아래로 진행된다. 같은 수준에서 위에서 아래로 실행된 게 그 이후에 없으면 상위로 올라가며 반복된다.

 

 

해당 실행 계획의 순서는 5 → 3 → 4 → 2 → 1 → 0 이다

 

📝실행계획 요소 (PLAN_TABLE)

STARTS	실행 계획 정보중 기본적인 최소한의 정보를 출력
A-ROWS	실제 SQL이 실행된 후 수집되는 행 수
A-TIME	실제 SQL 수행에 소요된 시간
E-ROWS	SQL에 대한 예측되는 행의 수
E-BYTES	SQL 실행 시 접근하는 데이터 바이트 수의 예측값
USED_MEM	최근 실행 시 실제 메모리 사용한 양

 

그외 더 다양한 정보가 있습니다.

 

📝SQL TRACE

실행 계획은 물론 여러 세션에서 수행한 SQL 통계 정보 수행 시간, 결과 등을 TRACE로 기록하여 파일 형태로 저장하는 방법이다. 이걸 이용해 필요한 부분의 SQL문의 실행 정보를 수집할 수도 있다.

 

 

🔗 참고 및 출처

https://harris91.vercel.app/query-plan
https://myjamong.tistory.com/237

 

 

발췌 : 실전 사례로 살펴보는 SQL 튜닝 비법