SQL실행계획
태그 :
- 개념
- - 옵티마이저에 의해 생성된 SQL문에 사용되는 Table 및 Index의 접근경로. - 동일한 SQL문은 많은 수의 실행계획을 가질 수 있으며, 결과는 동일하지만 소요되는 시간과 자원의 양은 서로 다름. - 성능에 중요한 영향을 미치는 요소(튜닝)
1. SQL의 성능 진단을 위한 도구 실행계획의 개요
가. 실행계획(Execution Plan)의 정의
옵티마이저에 의해 생성된 SQL문에 사용되는 Table 및 Index의 접근경로.
동일한 SQL문은 많은 수의 실행계획을 가질 수 있으며, 결과는 동일하지만 소요되는 시간과 자원의 양은 서로 다름.
성능에 중요한 영향을 미치는 요소(튜닝)
나. 실행계획에 영향을 미치는 요소
2. 실행계획(Execution Plan)의 유형 및 상세
가. 실행계획의 유형
구분 |
유형 |
OPTION |
설명 |
데이터접근(Access Method) |
테이블 TABLE ACCESS |
BY INDEX ROWID |
Record가 Index를 거쳐 접근되는 경우 |
FULL |
Table을 직접 접근해 모든 자료를 가져오는 경우 |
||
BY USER ROWID |
사용자가 직접 Record의 Rowid를 지정해 접근하는 경우 |
||
인덱스 INDEX
|
RANGE SCAN |
순차적으로 Index가 Scan되는 경우 |
|
UNIQUE SCAN |
Index로부터 단 1개의 Rowid만을 가져오는 경우(Where절에서 'EQUAL(=) 상수'를 받을 때) |
||
비트맵인덱스BITMAP INDEX |
SINGLE VALUE |
Bitmap Index 컬럼에 ‘=‘로 접근하는 경우 |
|
데이터연결(Join Operation) |
내포조인 NESTED LOOPS |
|
한쪽의 Row set로 부터 다른 쪽의 Row set를 순차적,반복적으로 Access하는 경우 |
병합조인 MERGE JOIN |
OUTER |
Outer Join을 Merge Join 방식으로 수행하는 경우 |
|
해쉬조인 HASH JOIN |
SEMI |
Semi Join을 Hash Join 방식으로 수행하는 경우 |
|
Set Operation |
UNION |
|
2개 이상의 Row set을 서로 결합한 후 중복 값은 삭제하는 경우 |
MINUS |
|
앞의 Row set중 나중의 Row set의 값들은 삭제하는 경우 |
나. NESTED LOOP JOIN :
1) 두 개의 Row Set 중 첫 번째 Row Set의 내용을 1개 읽은 후 그것을 두 번째 Row Set에서 검색
2) 주어진 조건을 만족하거나 첫 번째 Row Set의 내용이 소진될 때까지 반복 수행
3) NESTED LOOP JOIN의 특징
구분 |
설명 |
순차적 |
Driving Table의 처리범위에 있는 각각의 ROW들이 순차적으로 수행될 뿐 아니라 테이블간의 연결도 순차적 |
선행적 |
먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정 |
종속적 |
나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스.즉 값을 받아서 처리범위가 결정 |
Random Access |
Driving Table의 인덱스 액세스는 첫 번째 ROW만 Random Access이고,나머지는 Scan, 연결작업은 Random Access. |
선택적 |
연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음 |
연결고리중요, 방향성 |
연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음 |
부분범위처리 가능 |
연결작업 수행 후 Check 되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐 |
4) NESTED LOOP JOIN의 사용
- 부분범위처리를 하는 경우
ㆍ Nested Loop Join은 주로 전체가 아닌 부분범위를 처리하는 경우에 유리.
- Join 되는 테이블이 상호의존적인 경우
ㆍ Join 되는 어느 한 쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리. 하지만, Driving Table의 처리가 많거나 연결 테이블의 Random Access 량이 많을 경우에는 Sort/Merge Join 보다 불리
- 처리량이 적은 경우
ㆍRandom Access를 많이 하므로 On-Line 애플리케이션처럼 처리량이 적은 경우에 유리
- Driving Table 의 선택이 관건
ㆍ어느 테이블이 먼저 ACCESS 되는가가 수행속도에 큰 영향.
다. SORT MERGE JOIN
1) 첫 번째 Table을 읽고 Join Key를 중심으로 Sort, 두 번째Table을 읽고 Join Key를 중심으로 Sort 후 각각의 값을 비교해 일치하는 자료를 Return함
2) 처리 건수와 상관없이 Join의 대상이 되는 Table(또는 Index) 모두를 읽어 Sort 해야 함.
- SORT MERGE JOIN의 특징
동시적 : 각각의 테이블이 자신의 처리범위를 액세스하여 정렬.
독립적 : 각 테이블은 다른 테이블에서 어떠한 상수값도 제공 받지않고 주어진 상수값에 의해서만 범위를 줄임
전체범위처리 : 부분범위처리를 할 수 없음.
Scan방식 : 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 Random Access이고,Merge작업은 Scan방식
선택적 : 연결고리가 되는 칼럼은 인덱스를 사용하지 않음
무방향성 : Join의 방향과는 무관
3) SORT MERGE JOIN의 사용
- 전체범위처리를 하는 경우
ㆍSort/Merge Join은 주로 부분이 아닌 전체범위를 처리하는 경우에 유리
- EQUI-JOIN에서만 가능
- Join되는 테이블이 상호독립적인 경우
ㆍ 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태에서 유리. 상수값을 받아 줄여진 범위가 30%이상이면 Sort/Merge Join이 유리
- 처리량이 많은 경우
ㆍRandom Access를 하지 않으므로 전체범위처리에 유리
- 효과적인 인덱스 구성이 관건
ㆍ자신의 처리범위를 어떻게 줄이느냐가 관건이므로 효과적인 인덱스구성 중요
라. HASH JOIN
1) 첫 번째 Table을 읽어 Join Key를 대상으로 Hash Area상에 Hash Table을 구성함
2) 두 번째 Table을 읽으면서Join Key에 대한 Hash Value를 구해 Hash Area상의 Hash Value와 비교
3) 주어진 조건을 만족하거나 두 번째 Table의 내용을 모두 읽을 때까지 수행
- HASH JOIN의 특징
독립적 : 각 테이블은 다른 테이블에서 어떠한 상수값도 제공 받지 않고 주어진 상수값에 의해서만 범위를 줄임
전체범위처리 : 부분범위처리를 할 수 없음
선택적 : 연결고리가 되는 칼럼은 인덱스를 사용하지 않음
SORT 안함 : SORT를 하지 않으므로 SORT MERGE JOIN 보다 좋은 성능을 내며,작은 table
과 큰 table 의 join시에 유리
- HASH JOIN의 사용
전체범위처리를 하는 경우 : HASH Join은 주로 부분이 아닌 전체범위를 처리하는 경우에 유리
EQUI-JOIN에서만 가능
처리량이 많은 경우 : Random Access를 하지 않으므로 전체범위처리에 유리.
효과적인 인덱스 구성이 관건 : 자신의 처리범위를 어떻게 줄이느냐가 관건이므로 효과적인 인덱스 구성 중요. 작은 table 과 큰 table의 join시에 유리.
마. 실행계획의 사례
제일 상단의 부분에서 시작해 가장 오른쪽에 있는Join Operation의 바로 아래에 있는 Table(또는 Index) 먼저 수행
2개 이상의 Table일 경우 Join Operation에 따라 바로 아래에 있는 Table(또는 Index)에 Access
Index가 있는 경우 Index를 먼저 Access한 후 Table Access
Select emp.ename ,dept.dname From emp, dept Where emp.deptno = dept.deptno and emp.ename like 'A%' |
SELECT STATEMENT : ALL_ROWS NESTED LOOPS ①TABLE ACCESS (FULL) : STD01.EMP[O] ②TABLE ACCESS (BY INDEX ROWID) : STD01.DEPT[O] ③INDEX (UNIQUE SCAN) : STD01.DEPT_PRIMARY_KEY[O](DEPTNO:1) |
3. 실행계획(Execution plan)의 제어
가. 힌트(hint)의 활용
-힌트 : 옵티마이저가 항상 최적화된 실행계획을 수립하는 것은 아니므로 사용자가 힌트를 사용하여 원하는 실행계획으로 유도
-힌트의 사용 형식 :
나. 힌트(hint)의 종류
1)INDEX Access Operation 관련 HINT
HINT |
내용 |
INDEX |
INDEX를 순차적으로 스캔 |
INDEX_ASC |
INDEX를 내림차순으로 스캔 |
INDEX_DESC |
INDEX를 오름차순으로 스캔. |
2)JOIN Access Operation 관련 HINT
HINT |
내용 |
USE_NL |
옵티마이저가 NESTED LOOP JOIN을 사용하도록 유도 먼저 특정 테이블의 ROW에 액세스하고 그 값에 해당하는 다른 테이블의 ROW를 찾는 작업을 해당범위까지 실행 조인 |
USE_NL_WITH_INDEX |
INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 유도 |
USE_MERGE |
옵티마이저가 SORT MERGE JOIN을 사용하도록 유도. 먼저 각각의 TABLE의 처리범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOING하는 방식 |
USE_HASH |
옵티마이저가 HASH_JOIN을 사용하도록 유도 |
3)JOIN시 DRIVING 순서 결정 HINT
HINT |
내용 |
ORDERED |
FROM절에 명시된 테이블의 순서대로 DRIVING |
LEADING |
파라미터에 명시된 테이블의 순서대로 JOIN |
4)옵티마이저 모드 변경 HINT
HINT |
내용 |
CHOOSE |
Access되는 테이블의 통계치 존재여부에 따라 옵티마이저가 RBO와 CBO중 하나를 선택 가능 |
ALL_ROWS |
전체 RESOURCE 소비를 최소화 시키기 위함 |
FIRST_ROWS |
주로 On-Line환경일 때 빠른 응답시간을 얻기 위함 |
RULE |
RULE Based approach를 하도록 유도 |
4. 실행계획(Execution plan)을 통한 성과 및 제약사항
가. Explain Plan을 통한 성과
의도하지 않은 Full scans 을 피할 수 있다.
100건을 조회하기 위해 백만 건을 스캔 하는 Unselective range scans 을 피할 수 있다.
Late Predicate filters
처리범위를 증가시키는 잘못된 조인순서를 피할 수 있다. Wrong join order
처리범위를 줄이기 위해 조인 전에 데이터를 필터할 수 있다. Late filter operations
나. Explain Plan의 제약사항
바인드 변수에 대해서는 정확한 실제 계획을 보여주지 못한다.
암묵적인 형 변환에 대해서도 정확한 예측을 하지 못한다.