SQL 튜닝의 시작 (2013년)
- FILTER 동작방식
- 조인 동작 방식
- 테스트1.Filter 방식으로 수행되어 성능 문제가 발생하는 SQL
- Filter 방식 Test
- Join 방식 Test
- 성능 개선 수행
- 서브쿼리 동작 방식을 제어하는 힌트들
- 서브쿼리를 FILTER 동작 방식으로 수행하도록 제어
- NL SEMI JOIN으로 수행되도록 제어
- HASH JOIN SEMI JOIN으로 수행되며, 서브쿼리를 Main SQL 테이블 보다 먼저 수행하도록 제어
- SQL 서브쿼리를 HASH SEMI JOIN으로 수행하되, Main SQL 테이블을 먼저 수행하도록 제어
- SQL을 NL JOIN으로 수행하되, 서브쿼리를 수행하도록 제어
- HASH JOIN으로 처리하되, 허브쿼리로부터 수행하도록 제어
- NOT EXISTS로 작성된 SQL을 NL JOIN ANTI로 수행하도록 제어
- NOT EXISTS로 작성된 SQL을 HASH JOIN ANTI 조인으로 수행하도록 제어
- 서브쿼리를 먼저 읽은 후, NL 으로 수행
FILTER 동작방식
- Main SQL에서 추출된 데이터 건수만큼 서브쿼리가 반복적으로수행되면 처리되는 방식
- Main SQL의 추출 결과에 대해서, 매 로우마다 서브쿼리에 조인 연결 값을 제공한 후 수행한 후, TRUE일 경우 데이터를 추출
- Main SQL의 결과가 100만건이면 최대 100만번 수행
- 이런 이유로 적절한 인덱스가 없을 경우 Full Table Scan을 100만번 수행되어 SQL 성능 저하 발생
- Main SQL의 추출 결과가 많더라도 서브쿼리의 Input 값이 동일하면 Filter가 1번만 수행되는 Filter Optimization이라 불리는 최적화 작업을 수행하므로 성능 문제가 발생하지 않는다.
Filter Optimization
서브쿼리를 수행한 Input 값을 Cache하며, 동일한 Input값이 사용되는 경우 추가적입 읽기 작업 없이 Cache된 값을 사용
- 서브 쿼리의 Input 값이 동일한 확률이 매우 희박하므로 추출 건수가 많은 경우 서브커리를 Filter 동작 방식으로 처리할 경우 성능상 비횰율적인 경우가 더 많음
- Filter 동작 방식으로 수행될 경우 Input 값의 종류가 적어 성능에 유리한지를 반드시 확인해야 함.
Script. 서브쿼리 테스트용
DROP TABLE SUBQUERY_T1 PURGE;
DROP TABLE SUBQUERY_T2 PURGE;
DROP TABLE SUBQUERY_T3 PURGE;
<SUBQUERY_T1>
* 생성요건
- 테이블건수 1,500,000
- 컬럼 C4의 값의 종류: 250,000
- 컬럼 C5의 값의 종류 : 26
- 컬럼 C6SMS 100,000 부터 시작하여, 순차적으로 증가하며 값의 종류 : 250,000
* 테이블 생성
CREATE TABLE SUBQUERY_T1
AS
SELECT LEVEL AS C4, CHR(65+MOD(LEVEL, 26)) AS C5, LEVEL+99999 AS C6
FROM DUAL
CONNECT BY LEVEL <=250000;
- 250,000개의 데이터를 생성 한 후 동일한 테이블 값을 6번 반복하여 복사
FOR I IN 1..6 LOOP
INSERT INTO SUBQUERY_T1 SELECT * FROM SUBQUERY_T1;
COMMIT;
END LOOP;
END;
/
- 각 컬럼에 인덱스 생성 및 통계정보를 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'SUBQUERY_T1', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);
CREATE INDEX SUBQUERY_T1_IDX1_01 ON SUBQUERY_T1(C4, C5);
CREATE INDEX SUBQUERY_T1_IDX1_02 ON SUBQUERY_T1(C5);
<SUBQUERY_T2>
* 생성요건
- 테이블건수 500,000
- 컬럼 C1 값 종류 : 500,000
- 컬럼 C2 값 종류 : 26
- 컬럼 C3 값 종류 : 500,000(100,000부터 증가)
- 컬럼 C4 값 종류 : 26
* 테이블 생성
CREATE TABLE SUBQUERY_T2
AS
SELECT LEVEL AS C1,
CHR(65+MOD(LEVEL, 26)) AS C2,
LEVEL+99999 AS C3,
CHR(65+MOD(LEVEL,26)) AS C4
FROM DUAL
CONNECT BY LEVEL <= 500000;
* 각 컬럼 인덱스 생성 및 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT', TABNAME=>'SUBQUERY_T2', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);
CREATE INDEX SUBQUERY_T2_IDX_01 ON SUBQUERY_T2(C2, C1);
ALTER TABLE SUBQUERY_T2 ADD CONSTRAINT PK_SUBQUERY_2 PRIMARY KEY(C1);
<SUBQUERY_T3>
*생성 요건
- 테이블 데이터 건수 : 500,000
- 컬럼 C1 값 : 500,000
- 컬럼 C2 값 : 26
- 컬럼 C3 값 : 500,000 (100,000부터 순차적으로 증가)
*테이블 생성
CREATE TABLE SUBQUERY_T3
AS
SELECT LEVEL AS C1,
CHR(65+MOD(LEVEL, 26)) AS C2,
LEVEL+99999 AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000;
* 각 컬럼 인덱스 생성 및 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT', TABNAME=>'SUBQUERY_T3', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);
CREATE INDEX SUBQUERY_T3_IDX_01 ON SUBQUERY_T3(C1, C2);
ALTER TABLE SUBQUERY_T3 ADD CONSTRAINT PK_SUBQUERY_3 PRIMARY KEY(C1);
테스트1.Main SQL의 추출 건수가 많고, INPUT값이 UNIQUE한 경우
var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1
AND c1 <= :b2
AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
FROM SUBQUERY_T1 t1
WHERE t1.c4 = t2.c1);
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2302 1.326 1.378 0 789240 0 230001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2304 1.326 1.378 0 789240 0 230001
Rows Row Source Operation
------- -----------------------------------------------------------------------
230001 FILTER (cr=789240 pr=0 pw=0 time=1019504 us)
380001 FILTER (cr=3611 pr=0 pw=0 time=305638 us)
380001 TABLE ACCESS FULL SUBQUERY_T2 (cr=3611 pr=0 pw=0 time=205235 us cost=373 size=4560036 card=380003)
230001 INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=785629 pr=0 pw=0 time=0 us cost=3 size=10 card=2)
- FILTER 방식으로 동작
- SUBQYER_T2를 Full Table Scan으로 읽으며 "C1 >= :B1 AND C1 <= :B2" 조건에 만족하는 데이터 추출 ==> 380,001
- C1의 값을 SUBQUERY_T1_IDX_01 인덱스를 사용해 총 380,001번 반복조회하여 최종 건수 230,001건을 추출
- Main Query의 추출건수(38만건)만큼 서브 쿼리가 38만번 반복적으로 수행
- SQL전체 I/O 발생량의 대부분은 서브쿼리에서 반복적으로 사용하는 SUBQUERY_T1_IDX_01인덱스에서 발생
테스트2. Main SQL의 추출 건수가 적고, INPUT값이 UNIQUE한 경우
var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 20004
SELECT C1,
C2,
C3
FROM SUBQUERY_T2 T2
WHERE C1 >= :B1
AND C1<=:B2
AND EXISTS(SELECT /*+ NO_UNNEST */'X'
FROM SUBQUERY_T1 T1
WHERE T1.C4 = T2.C1)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 21 0 5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 21 0 5
Rows Row Source Operation
------- -----------------------------------------------------------------------
5 FILTER (cr=21 pr=0 pw=0 time=0 us)
5 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=21 pr=0 pw=0 time=0 us cost=13 size=756 card=63)
5 INDEX RANGE SCAN PK_SUBQUERY_2 (cr=19 pr=0 pw=0 time=224 us cost=7 size=0 card=2250)
5 INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=15 pr=0 pw=0 time=0 us cost=3 size=10 card=2)
- FILTER 방식으로 수행되지만, Main SQL의 조건이 효율적이어서 추출되는 데이터 건수가 총 5건 밖에 되지 않음
- 서브쿼리도 5번만 수행되어 비교적 양호
테스트3. Main SQL의 추출 건수는 많지만, INPUT값의 종류가 26개인 경우
var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000
SELECT C1,
C2,
C3
FROM SUBQUERY_T2 T2
WHERE C1 >= :B1
AND C1<=:B2
AND EXISTS(SELECT /*+ NO_UNNEST */'X'
FROM SUBQUERY_T1 T1
WHERE T1.C5 = T2.C2)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.005 0 0 0 0
Fetch 3802 0.484 0.974 60 19809 0 380001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3804 0.484 0.979 60 19809 0 380001
Misses in library cashe during parse : 1
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)
Rows Row Source Operation
------- -----------------------------------------------------------------------
380001 FILTER (cr=19809 pr=60 pw=0 time=457459 us)
380001 FILTER (cr=5107 pr=0 pw=0 time=292967 us)
380001 TABLE ACCESS FULL SUBQUERY_T2 (cr=5107 pr=0 pw=0 time=185774 us cost=377 size=4560036 card=380003)
14640 INDEX RANGE SCAN SUBQUERY_T1_IDX1_02 (cr=14702 pr=60 pw=0 time=0 us cost=3 size=4 card=2)
- Main Query의 추출건수 : 380,001건/ INPUT 종류 : 26
- 위의 서브쿼리는 Main SQL의 추출 결과만큼 배번 수행하지 않았음.
- 서브쿼리의 INPUT값을 CACHE하여 INPUT값이 같을 경우 서브쿼리를 수행하지 않았음.
Filter 방식 수행
- FILTER 방식은 Main SQL의 추출 결과가 많고 서브쿼리에 제공해 주는 값(INPUT)의 종류가 많다면 성능이 좋지 않음
- Main SQL의 추출 거누가 적거나, Main SQL의 추출결과가 많을 경우 INPUT값의 종류가 적으면 성능 양호
- FILTER 방식으로 수행되면 먼저 서브쿼리의 조인 연결 컬럼에 인덱스가 존재하는지 확인해야 함.
FULL TABLE SCAN으로처리된다면 심각한 성능 문제가 발생할 수 있음
조인 동작 방식
- 조안방식과 FILTER 동작방식과 비교시 큰 차이점은 가변성이다.
- FILTER는 수행순서나 수행방법이 고정 (Main SQL -> SubQuery) 다양한 상황에 유연한 대처가 어려움
- 조인 동작 방식은 Nest Loops Join, Hash Join, Sort Merge Join, Semi Join, Anti Join등의 다양한 조인 방법 중 유리한 것을 선택 가능
- SEMI/ANTI JOIN을 제외하고 수행 순서까지 선택 가능
기본적으로 SEMI/ANTI JOIN은 수행순서가 변경되지 않아 Main Query Block의 테이블이 먼저 수행.
Oracle 10부터 Hash Join의 경우에 한해 Driving Table의 순서 변경이 가능
- Nested Loops Join Semi를 제외한 나머지 조인 방법은 Filter 동작 방식이 가지고 있는 FILTER 오퍼레이션 의 효과는 없음.
- INPUT값의 종류가 적은 경우라면, FILTER 방식이 성능상 유리 할 수 있음.
테스트1.Filter 방식으로 수행되어 성능 문제가 발생하는 SQL
var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1
AND c1 <= :b2
AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
FROM SUBQUERY_T1 t1
WHERE T1.C6 = T2.C3
AND T1.C6 >= :b1)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 311.628 932.413 7885118 9223016 0 11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 311.628 932.413 7885118 9223016 0 11
Rows Row Source Operation
------- -----------------------------------------------------------------------
11 FILTER (cr=9223016 pr=7885118 pw=0 time=0 us)
221 FILTER (cr=1317 pr=0 pw=0 time=1980 us)
221 TABLE ACCESS FULL SUBQUERY_T2 (cr=1317 pr=0 pw=0 time=1100 us cost=372 size=2702664 card=225222)
11 FILTER (cr=9221699 pr=7885118 pw=0 time=0 us)
11 TABLE ACCESS FULL SUBQUERY_T1 (cr=9221699 pr=7885118 pw=0 time=0 us cost=164 size=10 card=2)
- 테이블 T1컬럼 C6에 인덱스가 없어 Main SQL의 추출 건수만큼 SUBQUERY_T1 테이블을 반복적으로 Full Table Scan 수행
- SUBQUERY_T1의 컬럼 C6에 인덱스를 생성하면 성능이 개선.
- 인덱스 생성시 SUBQUERY_T1 테이블을 액세스하는 다른 SQL의 실행계획에 영향을 미치는지 확인해야 함
- 트랜잭션 프로그램의 부하도 고려해야 함
- 인덱스 추가에 따른 디스크의 여유 및 앞으로의 증가량 체크
- DB 서버의 성능 측면에서 인덱스를 생성하는 것이 이득이 크지 않다면 다른 개선 방법을 찾아봐야 함.
-- c6에 인덱스를 생성 한 후 결과
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1
AND c1 <= :b2
AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
FROM SUBQUERY_T1 t1
WHERE T1.C6 = T2.C3
AND T1.C6 >= :b1)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.003 0 0 0 0
Fetch 2 0.000 0.065 8 249 0 11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.068 8 249 0 11
Misses in library cashe during parse : 0
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)
Rows Row Source Operation
------- -----------------------------------------------------------------------
11 FILTER (cr=249 pr=8 pw=0 time=0 us)
221 FILTER (cr=7 pr=0 pw=0 time=4400 us)
221 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=7 pr=0 pw=0 time=220 us cost=4 size=2664 card=222)
221 INDEX RANGE SCAN PK_SUBQUERY_2 (cr=4 pr=0 pw=0 time=2970 us cost=3 size=0 card=222)
11 FILTER (cr=242 pr=8 pw=0 time=0 us)
11 INDEX RANGE SCAN SUBQUERY_T1_IDX_03 (cr=242 pr=8 pw=0 time=0 us cost=3 size=5 card=1)
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1
AND c1 <= :b2
AND EXISTS (SELECT /*+ UNNEST HASH_SJ */ 'X'
FROM SUBQUERY_T1 t1
WHERE T1.C6 = T2.C3
AND T1.C6 >= :b1)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 2.761 9.234 37509 43888 0 11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 2.761 9.235 37509 43888 0 11
Rows Row Source Operation
------- -----------------------------------------------------------------------
11 FILTER (cr=43888 pr=37509 pw=0 time=0 us)
11 HASH JOIN SEMI (cr=43888 pr=37509 pw=0 time=0 us cost=10404 size=748 card=44)
221 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=5 pr=0 pw=0 time=880 us cost=4 size=1860 card=155)
221 INDEX RANGE SCAN PK_SUBQUERY_2 (cr=3 pr=0 pw=0 time=440 us cost=3 size=0 card=222)
6400640 TABLE ACCESS FULL SUBQUERY_T1 (cr=43883 pr=37509 pw=0 time=5948287 us cost=10368 size=32003330 card=6400666)
- 반복적인 Full Table Scan을 줄이는게 성능 개선 포인트
- /*+ UNNEST HASH_SJ*/ 힌트를 부여
- HASH JOIN SEMI로 수행. 단 한번 Full Table Scan만으로 수행되도록 변경
- 인덱스가 존재하지 않는 것이 비효율의 근본적인 원인이지만, 인덱스를 생성하기 힘든 경우에 위와 같은 튜닝으로 개선 효과 발생
Filter 방식 Test
var b1 number
var b2 number
var b3 number
var b4 number
exec :b1 := 1
exec :b2 := 450210
exec :b3 := 100000
exec :b4 := 100004
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS (SELECT /*+ NO_UNNEST */'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.009 0 31 0 0
Fetch 5 63.929 63.982 37509 33288865 0 320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 7 63.929 63.991 37509 33288896 0 320
Misses in library cashe during parse : 1
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)
Rows Row Source Operation
------- -----------------------------------------------------------------------
320 FILTER (cr=33288865 pr=37509 pw=0 time=159 us)
16000000 FILTER (cr=43886 pr=37509 pw=0 time=16265983 us)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=43886 pr=37509 pw=0 time=12611300 us cost=10424 size=192000000 card=16000000)
5 FILTER (cr=33244979 pr=0 pw=0 time=0 us)
5 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=33244979 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
15935488 INDEX UNIQUE SCAN PK_SUBQUERY_2 (cr=17309491 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
- Main SQL의 추출 데이터 : 1,600만건.
- SubQuery는 PK 인덱스를 UNIQUE 스캔 수행하여 I/O가 33만 블록
- 최종 데이터는 320건
- 320건을 추출하기 위해 처리한 I/O블록수와 수행 시간이 많이 소요 되었음.
Join 방식 Test
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS (SELECT /*+ UNNEST HASH_SJ */'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 5 4.103 5.269 37509 45202 0 320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 7 4.103 5.269 37509 45202 0 320
Misses in library cashe during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)
Rows Row Source Operation
------- -----------------------------------------------------------------------
320 FILTER (cr=45202 pr=37509 pw=0 time=319 us)
320 HASH JOIN RIGHT SEMI (cr=45202 pr=37509 pw=0 time=319 us cost=10861 size=8448 card=384)
5 TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=8 us cost=374 size=50 card=5)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=43886 pr=37509 pw=0 time=8017471 us cost=10408 size=192000000 card=16000000)
- 기존 SQL에서 서브쿼리가 1,600만번 반복 수행되던 부분을 제거하여 SQL 성능 개선
- 최종 결과가 320건추출 ==> 5초 수행
성능 개선 수행
- 성능 개선 여지는 SUBQUERY_T2의 추출 건수 =>5건으로 SUBQUERY_T1과 조인 후 최종 결과 320건 추출
- SUBQUERY_T2를 먼저 처리하고, Main SQL의 SUBQUERY_T1 테이블과 Nested Loops Join(조인 연결 컬럼 인덱스 존재) 수행
개선 방법
- SQL변경(O) : 서브쿼리를 조인으로 변경하여 효율적인 실행계획 유도
- SQL변경(X) : 서브쿼리와 Main SQL에 조인 순서(LEADING), 조인 방법(NL_SJ),QUERY BLOCK(QB_NAME) 힌트 추가하여 효율적인 실행 계획 유도
SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NL(T1@MAIN) */
C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS (SELECT /*+ UNNEST QB_NAME(SUB) */'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 5 0.062 0.052 0 1654 0 320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 7 0.062 0.053 0 1654 0 320
Misses in library cashe during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)
Rows Row Source Operation
------- -----------------------------------------------------------------------
320 FILTER (cr=1654 pr=0 pw=0 time=3509 us)
320 NESTED LOOPS (cr=1654 pr=0 pw=0 time=3349 us)
320 NESTED LOOPS (cr=1334 pr=0 pw=0 time=2871 us cost=477 size=7040 card=320)
5 SORT UNIQUE (cr=1316 pr=0 pw=0 time=8 us cost=374 size=50 card=5)
5 TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=4 us cost=374 size=50 card=5)
320 INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=18 pr=0 pw=0 time=567 us cost=2 size=0 card=64)
320 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=320 pr=0 pw=0 time=0 us cost=66 size=768 card=64)
- 서브쿼리를 조인으로 변경하기 위해서 추출 결과의 건수가 틀려지는 것을 방지하기 위해서 반드시 중복값을 제거하기 위해 SORT UNIQUE 오퍼레이션이 발생
- 서브쿼리의 추출 데이터가 5건에 불과해 추가적인 정렬 작업의 부하가 거의 없음.
서브쿼리 동작 방식을 제어하는 힌트들
| HINT명 | 설명 |
|---|---|
| NO_UNNEST | 서브쿼리를 FILTER동작방식으로 처리하고 싶을 경우, 서브쿼리에 NO_UNNEST 힌트를 사용 |
| UNNEST | FILTER 동작방식을 선택하지 않고 조인 동작방식으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용 |
| NL_SJ | EXISTS나 IN조건 사용시 서브쿼리에 UNNEST와 함께 NL_SJ힌트를 사용하면, NESTED LOOPS JOIN SEMI로 처리되도록 유도 |
| HASH_SJ | EXISTS나 IN조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_SJ 힌트를 부여하면 HASH JOIN SEMI로 처리하도록 제어 |
| NL_AJ | NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 NL_AJ 힌트를 사용하면, NESTED LOOPS JOIN ANTI로 처리하도록 제어 |
| HASH_AJ | NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_AJ 힌트를 사용하면 HASH JOIN ANTI로 처리하도록 제어 |
| ORDERED | FROM절에 나열된 순서대로 수행하도록 조인 순서를 정하는 힌트. 서브쿼리가 존재한다면 서브쿼리가 가장 먼저 수행 |
| QB_NAME | QUERY BLOCK의 이름을 지정 |
| SWAP_JOIN_INPUTS | HASH JOIN시 조인 순서를 변경 가능. 명시된 테이블이 BUILD 테이블이 됨 |
| NO_SWAP_JOIN_INPUTS | HASH JOIN시 조인 순서가 바뀌는 경우, 이를 강제적으로 변경되지 못하도록 제어 |
| PUSH_SUBQ | 서브쿼리가 먼저 수행하도록 제어. FILTER로 수행됨 |
서브쿼리를 FILTER 동작 방식으로 수행하도록 제어
NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ NO_UNNEST*/ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 97718 (1)| 00:19:33 |
|* 1 | FILTER | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2 | 1 | 10 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_SUBQUERY_2 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
NL SEMI JOIN으로 수행되도록 제어
NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST NL_SJ*/ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 29744 | 90486 (1)| 00:18:06 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS SEMI | | 1352 | 29744 | 90486 (1)| 00:18:06 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
|* 4 | TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2 | 42 | 420 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_SUBQUERY_2 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
HASH JOIN SEMI JOIN으로 수행되며, 서브쿼리를 Main SQL 테이블 보다 먼저 수행하도록 제어
UNNEST와 HASH_SJ, SWAP_JOIN_INPUTS힌트를 사용하면 서브쿼리부터 수행하도록 실행계획 제어 가능
SELECT
C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST HASH_SJ SWAP_JOIN_INPUTS(T2)*/ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
----------------------------------------------------------------------------------
d | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1352 | 29744 | 10828 (3)| 00:02:10 |
1 | FILTER | | | | | |
2 | HASH JOIN RIGHT SEMI| | 1352 | 29744 | 10828 (3)| 00:02:10 |
3 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500 | 374 (3)| 00:00:05 |
4 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
----------------------------------------------------------------------------------
SQL 서브쿼리를 HASH SEMI JOIN으로 수행하되, Main SQL 테이블을 먼저 수행하도록 제어
UNNEST와 HASH_SJ 힌트를 사용하면, HASH SEMI JOIN으로 수행하도록 제어
SEMI JOIN은 MAIN SQL쪽 테이블을 먼저 수행하는 것이 기본이나 HASH RIGHT SEMI JOIN으로 수행되면 조인 순서가 변경되므로
NO_SWAP_JOIN_INPUTS 힌트를 명시적으로 사용
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST HASH_SJ NO_SWAP_JOIN_INPUTS(T2) */ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 29744 | 10828 (3)| 00:02:10 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN SEMI | | 1352 | 29744 | 10828 (3)| 00:02:10 |
|* 3 | TABLE ACCESS FULL| SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
|* 4 | TABLE ACCESS FULL| SUBQUERY_T2 | 1250 | 12500 | 374 (3)| 00:00:05 |
-----------------------------------------------------------------------------------
SQL을 NL JOIN으로 수행하되, 서브쿼리를 수행하도록 제어
QB_NAME 힌트를 사용해 QUERY BLOCK명을 지정한 후, QUERY BLOCK 명을 지정한 후, 조인 순서와 조인 방법을 제어
SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NAME(T1@MAIN) */
C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1351 | 29722 | 10829 (3)| 00:02:10 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1351 | 29722 | 10829 (3)| 00:02:10 |
| 3 | SORT UNIQUE | | 1250 | 12500 | 374 (3)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| SUBQUERY_T2 | 1250 | 12500 | 374 (3)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
------------------------------------------------------------------------------------
HASH JOIN으로 처리하되, 허브쿼리로부터 수행하도록 제어
SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_HASH(T1@MAIN) */
C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1351 | 29722 | 10829 (3)| 00:02:10 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1351 | 29722 | 10829 (3)| 00:02:10 |
| 3 | SORT UNIQUE | | 1250 | 12500 | 374 (3)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| SUBQUERY_T2 | 1250 | 12500 | 374 (3)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
------------------------------------------------------------------------------------
NOT EXISTS로 작성된 SQL을 NL JOIN ANTI로 수행하도록 제어
NOT EXISTS의 경우 NL JOIN ANTI로 수행 제어 하기 위해서 UNNEST, NL_AJ힌트를 부여
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST NL_AJ */ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 29744 | 10828 (3)| 00:02:10 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1352 | 29744 | 10828 (3)| 00:02:10 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500 | 374 (3)| 00:00:05 |
|* 4 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
-------------------------------------------------------------------------------------
NOT EXISTS로 작성된 SQL을 HASH JOIN ANTI 조인으로 수행하도록 제어
SELECT C4,
C5,
C6
FROM SUBQUERY_T1 T1
WHERE C6 >= :B1
AND C6 <= :B2
AND EXISTS(SELECT /*+ UNNEST HASH_AJ */ 'X'
FROM SUBQUERY_T2 T2
WHERE T2.C1 = T1.C4
AND T2.C3 >= :B3
AND T2.C3 <= :B4);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 29744 | 10828 (3)| 00:02:10 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1352 | 29744 | 10828 (3)| 00:02:10 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500 | 374 (3)| 00:00:05 |
|* 4 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 468K| 10453 (3)| 00:02:06 |
-------------------------------------------------------------------------------------
서브쿼리를 먼저 읽은 후, NL 으로 수행
SELECT
*
FROM EMP E
WHERE EMPNO IN (SELECT
MAX(EMPNO)
FROM EMP X
GROUP BY DEPTNO);
-- 서브쿼리를 먼저 읽은 후, NL로 수행
SELECT /*+ LEADING(X@SUB) QB_NAME(MAIN) USE_NL(E@MAIN) */
*
FROM EMP E
WHERE EMPNO IN (SELECT /*+ UNNEST QB_NAME(SUB) */
MAX(EMPNO)
FROM EMP X
GROUP BY DEPTNO);
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 1400 | 8 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 14 | 182 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 14 | 364 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
- 의도한대로 제어되지 않는 이유는 OPTIMIZER가 서브쿼리를 인라인 뷰로 변경하는 SQL 최적화 작업을 수행했기 때문이다.
- VW_NSO_1이란 점에서 추축 가능
- SQL이 변경되고, 이론 인해 QUERY BLOCK명도 변경되어 QB_NAME 힌트는 물론, 다른 힌트들도 무시
- 이런 경우는 FROM절에 나열된 순서대로 조인 순서를 결정하는 ORDERED 힌트를 사용하면 유도 할 수 있음
- LOGICAL OPTIMIZER가 서브쿼리를 인라인 뷰로 변경할 때 FROM절의 맨 앞에 위치 시키기 대문에 ORDERED로 유도 가능
SELECT /*+ ORDERED USE_NL(E) */
*
FROM EMP E
WHERE EMPNO IN (SELECT /*+ UNNEST */
MAX(EMPNO)
FROM EMP X
GROUP BY DEPTNO);
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 18 (6)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 14 | 1400 | 18 (6)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 14 | 182 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 14 | 364 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
- SUBQUERY가 여러개 일 경우 ORDERED 힌트로 제어 불가.
- 여러개의 SUBQUERY가 존재할 경우는 SQL을 재작성하여 SUBQUERY를 인라인 뷰로 면경 후, 힌트로 실행계획을 제어 해야 함
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3781
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.