[total]_테스트_데이터_생성하기_scripts.txt
서브쿼리에 대한 기본 내용 이해하기
서브쿼리란?
- Where절에 비교조건으로 사용되는 Select 쿼리
서브쿼리 동작방식 이해하기
1. Filter 동작방식
Filter 동작방식이 Main SQL추출 결과와 입력 값의 종류에 따라
어떻게 SQL의 성능이 달라지는지 확인해보자.
먼저 유저를 만들고 시작한다.
유저 만드는 법은 알거라 생각하기에 따로 기재하지 않습니다.
필자는 tuner라는 유저를 생성하고 dbs, sysdba라는 권한을 부여한 후,
default tablespace는 users라는 tablespace로 지정을 했습니다.
위 3개의 스크립트를 차례대로 실행한다.
테이블을 어떤 조건으로 생성하는지는 책을 한번 읽어보자.
[테스트1] - Main SQL의 추출 결과가 많고 입력 값이 Unique한 경우
trace 파일을 생셩했으면 생성된 위치(user_dump_dest)로 가서 보기 쉬운 파일로 변환을 하자
tkprof [파일명].trc trace_result.txt sys=no
생성한 파일을 열어서 확인해보자
아래 내용은 결과중 일부를 발췌(?)했다.
[SQL의 실행 계획 설명] – Filter Operation이 있기 때문에 Filter 동작방식으로 수행
1. Subquert_t2 테이블을 Full Table Scan으로 읽으며 “c1 >= :b1 and c1 <= :b2”조건에
만족하는 데이터를 추출 (총 380,001건)
2. 최종 데이터를 추출하기 위해 서브쿼리의 결과도 만족하는지 확인 해야 한다.
서브쿼리로 c1 값을 넘겨준 후 서브쿼리에 결과 값이 존재하는지 확인
(서브쿼리는 c1값을 subquery_t1_idx_01 인덱스를 사용해 총 380,001번 반복 조회 후 최종적으로 230,001건을 추출)
테스트[1]의 SQL트레이스 결과 : Main SQL의 추출 결과 38만건인데 Filter 동작방식으로 수행되다 보니
서브쿼리가 38만번이나 반복적으로 수행. 결국 SQL 전체 I/O발생량 대부분은 서브쿼리에서 반복적으로
사용하는 subquery_t1_idx_01 인덱스에서 발생.
[테스트2] - Main SQL의 추출 건수가 적고, Input 값이 Unique 한 경우
테스트[2]의 SQL트레이스 결과 : Filter동작방식으로 수행되지만, Main SQL의 조건이 효율적이어서 추출되는
데이터 건수가 총 5건이다. 그래서 서브쿼리도 5번만 수행되기 때문에, SQL의 성능은 테스트[1]과 비교해서
매우 양호하다
[테스트3] – Main SQL의 추출건수는 많지만, Input 값의 종류가 26가지인 경우
alter session set events '10046 trace name context forever, level 12';
sql_test03.sqlalter session set events '10046 trace name context off';
트레이스 결과 : Main SQL의 추출 건수는 380,001건으로 상당히 많지만, 입력 값은 26가지에 불과
테스트[3]의 서브쿼리는 Mail SQL의 추출 결과만큼 매번 수행하지 않는다.
- 서브쿼리의 입력 값을 cache하여, 입력 값이 같을 경우 서브쿼리를 수행하지 않기 때문
서브쿼리의 수행 횟수는 입력 값의 종류만큼인 26번만 수행되어 성능이 양호
3 가지의 테스트를 통해 서브쿼리가 Filter 동작방식으로 수행될 경우 Main SQL추출 결과와 입력 값의
종류에 따라 성능이 어떻게 달라지는지 확인했다.(분명)
[정리]
Filter 동작방식은 Main SQL의 추출결과가 많고 서브쿼리에 제공해 주는 값(입력 값)의 종류가 많으면
성능이 좋지 않다. 하지만 Main SQL의 추출건수가 적거나, Main SQL의 추출결과가 많다고 해도 입력 값의
종류가 적은 경우 Filter 동작방식으로 수행되는 SQL의 성능은 양호하다.
Filter 동작방식의 경우 항상 Main SQL이 먼저 수행되고, 서브쿼리는 Main SQL에서 추출된 데이터의 값을
전달 받아 매번 확인하는 형태로 수행된다. Filter Operation은 한가지 방법만 고수해서 다양한 상황에서
유연하게 대처하기가 어렵다. 그래서 서브쿼리가 Filter 동작방식으로 수행되는 경우 SQL성능이 좋지 않은
경우가 많다.
SQL의 실행계획을 점검하다 서브쿼리가 Filter 동작방식으로 수행되고 있으면, 먼저 서브쿼리의
조인 연결 컬럼에 인덱스가 존재하는지 확인
- 서브쿼리가 Filter 동작방식으로 수행되는데, Full Table Scan으로 처리하고 있다면, 심각한
성능문제가 발생할 수 있기 때문이다.
2. 조인 동작방식
조인 동작방식의 차이점은 가변성이다.
Filter 동작방식은 수행 순서, 수행 방법에 있어서 고정이다.
조인 동작방식은 NL Join, Hash Join, sort merge Join, Semi Join, Anti Join이 있고
이중 유리한 것을 선택할 수 있다. Semi, Anti Join을 제외하고 수행 순서까지 선택할 수
있어 유연하게 처리가 가능하다.
NL Join Semi를 제외한 나머지 조인 방법은 Filter 동작 방식이 가지고 있는 Filter Operation
효과에 의한 이득을 얻지 못한다. 따라서 입력 값의 종류가 적은 경우 Filter 동작방식이 유리할 수도 있다.
SQL Trace의 결과를 보면서 조인 동작방식 원리 및 Filter 동작방식의 성능차이가 어디서 발생하는지
자세히 알아보자. 아래의 SQL을 Filter / Join 동작방식으로 수행하고, 성능차이를 비교함으로써
성능 차이가 발생하는 이유를 알아보자
테스트 SQL은 Filter 동작방식으로 수행되어 문제가 발행하고 있다. Filter 동작방식이 가지고 있는 문제점을
알아보고 조인 동작방식으로 변경했을 때 성능의 변화를 알아보자.
테스트[1] – Filter 방식으로 수행되어 성능 문제가 발생하는 SQL
테스트[1]의 경우 Filter 동작방식으로 수행되었고, table t1의 컬럼 c6에 인덱스가 없어
Main SQL의 추출 건수만큼 subquery_t1테이블을 반복으로 Full Table Scan하고 있다.
subquery_t1의 컬럼 c6에 인덱스를 생성해 주면 성능이 개선될 것이다. 실 운영환경에서는
잘 고려해서 선택하자.
인덱스를 생성하려면 우선 subquery_t1 테이블을 액세스하는 다른 SQL의 실행계획에
미치는 영향과 트랜잭션 프로그램 부하도 고려해야 하고, 인덱스 추가에 따른 용량 증가도 고려해야 한다.
테스트[1]의 성능 개선을 하기 위해 FTS을 줄여야 하므로 서브쿼리를 조인동작 방식으로 변경하고
Hash Join Semi로 변경하자.(unnest hash_sj)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.20 1.18 16 37420 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.20 1.18 16 37420 0 11
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 29
Rows Row Source Operation
------- ---------------------------------------------------
11 FILTER (cr=37420 pr=16 pw=0 time=37914 us)
11 HASH JOIN SEMI (cr=37420 pr=16 pw=0 time=37906 us)
221 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=6 pr=0 pw=0 time=965 us)
221 INDEX RANGE SCAN PK_SUBQUERY_2 (cr=4 pr=0 pw=0 time=279 us)(object id 10614)
6400640 TABLE ACCESS FULL SUBQUERY_T1 (cr=37414 pr=16 pw=0 time=12679 us)
기존의 Filter 동작방식이 아닌 조인 동작방식으로 변경되었고, 조인은 Hash Join Semi로 수행
실행계획의 변경은 SQL이 Filter동작방식으로 수행되어 221번 반복되는 FTS을 제거하고,
한번만 FTS를 하도록 변경된 것이다.
개선 전의 SQL의 경우 인덱스가 없는 것이 비효율적이기도 하지만, 인덱스를 생성하기 힘든 경우,
힌트를 사용해서 동작방식을 변경하는 것 만으로도 개선 효과를 볼 수 있다.
테스트[2] – Main SQL의 추출 건수는 매우 많고 서브쿼리에 있는 상수 조건이 매우 효율적이어서
서브쿼리를 먼저 수행해야 효율적인 처리가 되는 SQL이 있다고 가정한다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23 76.58 74.95 8106 63908411 0 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 76.58 74.95 8106 63908411 0 320
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 29
Rows Row Source Operation
------- ---------------------------------------------------
320 FILTER (cr=63908411 pr=8106 pw=0 time=874 us)
16000000 FILTER (cr=37435 pr=8106 pw=0 time=140 us)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=37435 pr=8106 pw=0 time=14219 us)
5 FILTER (cr=63870976 pr=0 pw=0 time=59553122 us)
5 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=63870976 pr=0 pw=0 time=53849622 us)
15967744 INDEX UNIQUE SCAN PK_SUBQUERY_2 (cr=47903232 pr=0 pw=0 time=32029726 us)(object id 10614)
위 SQL을 Filter 동작 방식이 아닌 조인 동작방식(Hash Join Semi)으로 유도 했을 때 변화를 보자.
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23 2.69 2.64 5088 38751 0 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 2.70 2.65 5088 38751 0 320
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 29
Rows Row Source Operation
------- ---------------------------------------------------
320 FILTER (cr=38751 pr=5088 pw=0 time=33494 us)
320 HASH JOIN RIGHT SEMI (cr=38751 pr=5088 pw=0 time=33485 us)
5 TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=655 pw=0 time=179 us)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=37435 pr=4433 pw=0 time=233 us)
기존 SQL에서 1600만번 반복 수행을 제거해서 성능을 크게 개선했다. 하지만 320건의 결과만 나오고 Subquery_t1테이블에 대해
FTS으로 인해 처리시간이 4초나 소요되었다.
이것을 더 개선 시켜 보자. Subquery_t2의 추출된 5건으로 Subquery_t1과 조인 처리 후 최종 320건으로 추출되는 것에 있다
그래서 Subquery_t2를 먼저 처리하고, Subquery_t1 테이블과 NL Join을 수행하면 성능이 개선될 것이다.
따라서 서브 쿼리를 먼저 수행하고, NL_SJ 힌트를 사용하여 SQL을 수행해보자.
SQL을 개선하는 방법 두 가지
1. SQL 변경(O): 서브 쿼리를 조인으로 변경해서 실행 계획 유도
2. SQL 변경(X): 서브쿼리와 Main SQL에 조인 순서(LEADING), 조인 방법(NL_SJ), Query Block(QB_name)힌트를 추가 해서 실행 계획 유도
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23 0.02 0.02 0 1673 0 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 0.02 0.02 0 1673 0 320
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 29
Rows Row Source Operation
------- ---------------------------------------------------
320 FILTER (cr=1673 pr=0 pw=0 time=26847 us)
320 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=1673 pr=0 pw=0 time=26521 us)
326 NESTED LOOPS (cr=1353 pr=0 pw=0 time=2489836 us)
5 SORT UNIQUE (cr=1316 pr=0 pw=0 time=22979 us)
5 TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=126 us)
320 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=37 pr=0 pw=0 time=66 us)(object id 10610)
더욱 성능이 개선 되었다.
3. 서브쿼리 동작방식을 제어하는 힌트들
|
Hint Name |
설명 |
|
No_unnest |
서브쿼리를 Filter로 처리할때 사용 |
|
unnest |
조인 동작방식으로 처리할때 사용 |
|
NL_SJ |
exist, in 조건을 사용한 경우 서브쿼리에 unnest와 함께 사용하면 NL Join Semi로 처리하도록 제어 |
|
Hash_SJ |
exist, in 조건을 사용한 경우 서브쿼리에 unnest와 함께 사용하면 Hash Join Semi로 처리하도록 제어 |
|
NL_AJ |
Not(exist, in) 조건을 사용한 경우 서브쿼리에 unnest와 함께 사용하면 NL Join Anti로 처리하도록 제어 |
|
Hash_AJ |
Not(exist, in) 조건을 사용한 경우 서브쿼리에 unnest와 함께 사용하면 Hash Join Anti로 처리하도록 제어 |
|
ORDERED |
from절의 컬럼 순서로 수행하도록 조인 순서를 정하는 힌트(서브쿼리를 먼저 수행) |
|
QB_NAME |
Query Block의 이름을 지정 |
|
SWAP_JOIN_INPUT |
Hash Join시 사용하면 조인 순서를 변경할 수 있다. |
|
NO_SWAP_JOIN_INPUT |
조인 순서가 바뀌는 경우 강제로 변경하지 못하게 한다. |
|
PUSH_SUBQ |
서브쿼리가 먼저 수행되도록 제어 하는 힌트 |
위 표로 정리 하니까 이해 하기가 쉬운듯 하다.
연습 문제
예제 SQL
1. 예제 SQL의 서브 쿼리를 Filter 방식으로 수행되도록 제어하세요.
(참고 : no_unnest 힌트를 사용)
explain plan for
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);
select * from table(dbms_xplan.display());
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 96063 (1)| 00:19:13 |
|* 1 | FILTER | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K| 8740 (6)| 00:01:45 |
|* 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 |
-----------------------------------------------------------------------------------------------
2. 예제 SQL의 서브쿼리를 NL Semi Join으로 수행되도록 제어하세요.
(참고 : unnest와 함께 NL_SJ힌트를 사용)
explain plan for
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);
select * from table(dbms_xplan.display());
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 28392 | 88818 (1)| 00:17:46 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS SEMI | | 1352 | 28392 | 88818 (1)| 00:17:46 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K| 8740 (6)| 00:01:45 |
|* 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 |
-----------------------------------------------------------------------------------------------
3. 예제 SQL의 서브쿼리를 Hash Join Semi Join으로 수행하되 서브쿼리를 먼저 수행되도록 제어하세요.
(참고 : unnest와 Hash_SJ, Swap_join_inputs 힌트를 사용)
explain plan for
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);
select * from table(dbms_xplan.display());
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 28392| 9052 (6)| 00:01:49 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1352 | 28392| 9052 (6)| 00:01:49 |
|* 3 | TABLE ACCESS FULL | SUBQUERY_T2 | 1250 | 12500| 311 (6)| 00:00:04 |
|* 4 | TABLE ACCESS FULL | SUBQUERY_T1 | 40000 | 429K| 8740 (6)| 00:01:45 |
-------------------------------------------------------------------------------------
4. 예제 SQL의 서브쿼리를 Hash Semi Join으로 수행하되, Main SQL을 먼저 수행되도록 제어하세요.
(참고 : unnest와 hash_sj 힌트를 사용하면 hash semi join으로 수행되도록 제어할 수 있다. Hash Right Semi Join으로
수행되면 조인 순서가 변경되니 no_swap_join_inputs힌트를 명시적으로 사용해야 함.)
explain plan for
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);
select * from table(dbms_xplan.display());
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1352 | 28392 | 9052 (6)| 00:01:49 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN SEMI | | 1352 | 28392 | 9052 (6)| 00:01:49 |
|* 3 | TABLE ACCESS FULL| SUBQUERY_T1 | 40000 | 429K | 8740 (6)| 00:01:45 |
|* 4 | TABLE ACCESS FULL| SUBQUERY_T2 | 1250 | 12500 | 311 (6)| 00:00:04 |
-----------------------------------------------------------------------------------
5. Nested Loop Join으로 수행하되 서브쿼리부터 수행하도록 제어
(참고:QB_NAME 힌트를 사용하되 Query Block명을 지정한 후 조인 순서와 조인 방법을 제어)
explain plan for
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);
select * from table(dbms_xplan.display());
6. Hash Join으로 처리하되 서브쿼리부터 수행하도록 제어
EXPLAIN PLAN FOR
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_MAIN(SUB) */ 'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1, t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
7. NOT EXISTS로 작성된 SQL을 Filter동작방식이 아닌 Nested Loops Anti Join으로 수행하도록 제어
(참고: NOT EXISTS의 경우 Nested Loops Anti Join으로 수행하도록 제어하려면 UNNEST, NL_AJ힌트를 부여)
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >=:b1
AND c6 <=:b2
AND NOT EXISTS(SELECT /*+ UNNEST NL_AJ */ 'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1=t1.c4
AND t2.c3 >=:b3
AND t2.c3 <=:b4);
SELECT * FROM TABLE(DBMS XPLAN.DISPLAY());
8. NOT EXISTS로 작성된 SQL을 Hash Join Anti조인으로 수행하도록 제어
(참고:NOT EXISTS의 경우 UNNEST, HASH...!힌트를 부여하면 실행계획을 제어할 수 있다.
EXPLAIN PLAN FOR
SELECT c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >=:b1
AND c6 <=:b2
AND NOT EXISTS(SELECT /*+ UNNEST NL_AJ */ 'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1=t1.c4
AND t2.c3 >= :b3
AND t2.c3 <= :b4);
9. 다음 SQL에서 서브쿼리를 먼저 읽은 후，Nested Loops Join으로 수행하도록 하자.
select *
from emp a
where empno IN(SELECT max(empno)
FROM emp x
GROUP BY deptno);
조인 순서(LEADING), 조인 방법(USE_NL), QUERY BLOCK명 지정(QB_NAME) 힌트를 사용하여 SQL 실행계획을 제어
EXPLAIN PLAN FOR
SELECT /*+ LEADING(X@SUB) QB_NAME(MAIN) USE_NL(A@MAIN) */ *
from emp a
where empno in(SELECT /*+ UNNEST QB_NAME(SUB) */
max(empno)
FROM emp x
GROUP BY deptno );
SELECT * FROM TABLE(DBMS XPLAN . DISPLAY());
Optimizer가 서브쿼리를 인라인 뷰로 변경하는 SQL 최적화 작업을 수행(뷰 이름:VM_NSO_l에서 유추)
즉，SQL이 변경되고 이로 인해 QUERY BLOCK명도 변경되어서 SQL에 사용한 QB_NAME 힌트와 다른 힌트들이 무시
그래서 LEADING 힌트로 유도했던 조인 순서로 수행되지 않았다. 이런 경우 From절에 나열된 순서대로 조인 순서를
결정하는 ORDERED 힌트를 사용하면 원하는 대로 유도가 가능. 이는 Logical Optimizer가 서브쿼리를 인라인 뷰로
변경할 때 From절의 가장 앞에 위치 시키기 때문에 가능한 일이다.
그럼 ORDERED 힌트를 이용해 실행계획을 원하는 대로 제어를 해보자.
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_NL(A) */ *
FROM emp a
WHERE empno IN(SELECT /*+ UNNEST */
max(empno)
FROM emp x
GROUP BY deptno);
SELECT * FROM TABLE(DBMS XPLAN.DISPLAY());
위의 SQL은 서브쿼리가 하나，Main SQL의 From절에 테이블이 하나 밖에 없는 매우 단순한 SQL이기 때문에 가능.
서브쿼리가 여러 개 있고，Main SQL의 From절에도 여러 개의 테이블이 조인된 상태라면
앞에서 ORDERED 힌트로 제어하여 개선한 방법의 사용은 사실상 불가능하다. 왜냐하면
힌트를 사용하여 여러 개의 서브쿼리 중 특정 서브쿼리를 먼저 읽고 이후 나머지 서브쿼리들과 From절의 테이블의
조인 순서 등을 제어하는 것은 사실상 불가능 하기 때문.
만약，복잡한 SQL를 제어하는 방법은 SQL을 재작성하는 것이다.SQL의 서브쿼리를 인라인 뷰로 변경한 후 힌트로
실행계획을 제어해야 한다.
서브쿼리는 DB서버의 성능에 미치는 영향력이 매우 크기 때문에 각별하게 신경 써야 할 대상이다.
SQL의 업무적 성격(주출 건수 조회 빈도 등)을 고려하는 것은 물론이고，그에 가장 적합한 서브쿼리 동작방식까지
고려한 SQL 을 작성해야 할 것이다.
서브쿼리를 활용한 SQL 성능개선
비효율적인 MINUS 대신 NOT EXISTS를 사용
어느 한 집합에 있는 데이터 중 다른 집합에 존재하지 않는 데이터를(차집합) 추줄하기 위한 방법으로 MINUS를 사용.
MINUS는 두 집합 간의 차집합을 추출할 때，SQL 작성이 쉽고 가독성도 뛰어나 자주 사용
하지만 MINUS를 사용한 SQL이 성능 문제가 발생할 경우，SQL을 변경하지 않고 성능을 개선하려면
MINUS 대신 NOT EXISTS로 대체한 SQL로 재작성하여 성능을 개선하는 것이 가능할 수 있다.
추출 대상이 되는 데이터 집합의 Where절에 효율적인 상수조건이 있고 비교 대상인 다른
집합에는 Where절이 없는 경우 SQL을 각각 MINUS와 NOT EXISTS를 사용하여 작성한 후 두 SQL간 성능 차이를 확인.
alter session set trace
테스트[1]. MINUS
var b1 varchar2(10)
var b2 nurnber
var b3 nurnber
exec :b1 := ’A’
exec :b2 := 200000
exec :b3 := 300000
SELECT c1, c2, c3
FROM SUBQUERY_T2
WHERE c2 :b1
AND c1 >= :b2
AND c1 <= :b3
MINUS
SELECT c4, c5, c6
FROM SUBQUERY_T1
alter session set trace
트레이스 결과를 보면， 비교 대상인 SUBQUERY31 에는 조회 조건이 없어 F내I Table Scan
으로 수행하고I 모든 데이터 (1,600 만건)에 대한 정렬 작업까지 수행하고 있다.
테스트[2]. NOT EXISTS
SELECT cl, c2, c3
FROM SUBQUERY_T2 T2
WHERE c2 :bl
AND cl >= :b2
AND cl <= :b3
AND NOT EXISTS ( SELECT /*+ UNNEST NL_AJ */ 'x'
FROM SUBQUERY_T1 T1
WHERE t1.c4 = t2.c1
AND t1.c5 = t2.c2
AND t1.c6 = t2.c3);
NOT EXISTS를 사용한 SQL의 트레이스 결과 Full Table Scan은 인덱스 스캔으로 수행 방식이 변경,
정렬 작업이 제거되어，MINUS를 사용했을 때 보다 성능이 크게 개선.
MINUS를사용한 SQL 성능문제
비교 대상 테이블에서 데이터 추줄하는 방식이 비효율(FTS)인데도 불구하고 조회 조건이 없어 개선을 하기 힘들다.
비교 대상 테이블에서 많은 데이터가 추출되면 정렬 작업이 과다하게 발생한다는 것.
테스트[2]의 예제와 같이 비교대상 테이블에 대한 Where절에 조건이 없어 Full Table Scan을
수행해 성능 문제가 발생하거나，비교 대상 테이블에서 추출되는 데이터가 많아 정렬 작업에
대한 부하가 과도한 경우:MINUS를 NOT EXISTS로 변경하여 개선.
MINUS를 NOT EXISTS로 변경한 SQL은 추출된 데이터 총 건수가 서로 다른 결과를
추출할 수 있다. MINUS를 사용하는 SQL의 실행계획 중 SORT UNIQUE 오퍼레이선은 MINUS 연산으로
Unique 값을 추줄한다는 것을 의미.
NOT EXISTS를 사용한 SQL의 실행계획에는 SORT UNIQUE 오퍼레이션이 존재하지 않는다. 따라서
Select절에 나열된 컬럼의 조합이 Unique 하지 않다면 NOT EXISTS를 이용한 SQL은 중복된 값이
추출될 가능성이 있다. 이런 이유로 MINUS를 무조건 NOT EXISTS로 변경한 경우,
성능은 개선되었지만 데이터 정합성이 훼손되는 현상이 발생할 수 있는 위험이 존재한다.
일반적으로 MINUS 사용시 대부분 Select 절에 Primary Key컬럼(Unique컬럼)이 포함되어 있어
NOT EXISTS로 바꿀경우 데이터가 잘못 추출되는 문제가 발생하지 않는다. 테스트 SQL도
Primary Key 컬럼이 Select절에 포함.(DISTINCT 처리를 하지 않고 단순히 NOT EXISTS로
변경하여도 정합성이 훼손되지 않은 것이다. 하지만 이는 일반적인 경우일 뿐 Select절에 나열된
컬럼 조합이 Unique 하지 않은 상황이 얼마든지 존재할 수 있다. 따라서 데이터 정합성을 위해서
Select 절에 나열된 컬럼의 조합이 Unique 한지 반드시 체크해야 한다.
MINUS와 NOT EXISTS를 사용한 경우에 총 추출 건수가 달라지는 상황 테스트.
SELECT *
FRom (SELECT 1 no
from dual
union all
select 2
from dual
union all
select 2
from dual)
MINUS
SELECT *
from (select 1 no from dual);
MINUS 연산은 중복 값이 제거되므로 2가 2건 추출되지 않고 1건만 추출.
MINUS를 NOT EXISTS로 변경하여 처리.
SELECT x.*
FROM (SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL) x
WHERE NOT EXISTS ( SELECT 'x'
from (select 1 no from dual) y
where x.no=y.no);
동일한 데이터가 2건이 추출되어 결과가 달라지게 된다. 따라서 결과 값을 동일하게 하려면
DISTINCT를 부여해야 한다.
SELECT distinct x.*
FROM (SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL) x
WHERE NOT EXISTS ( SELECT 'x'
from (select 1 no from dual) y
where x.no=y.no);
MINUS로 작성된 SQL을 NOT EXISTS로 작성할 경우，항상 DIST1NCT를 사용하는 것은
성능상 유리하지 않은 이유를 알아 보자.
SQL에 DISTINCT를 사용하면，oracle은 중복된 값을 제거하기 위해 내부적으로 정렬 작업을
수행하게 된다. 그런데 SQL의 추출 건수가 적은 경우에는 정렬 작업에 대한 부담이
적겠지만, 많은 경우라면 정렬 작업에 대한 부하도 커진다. 그러므로 항상 DISTINCT를 사용하는 것은
성능상 유리하지 않다.
그러므로 MINUS 대신 NOT EXISTS를 사용하여 SQL을 작성할 경우 Select절에 나열된 컬럼 조합이
Unique한지 먼저 판단해야 한다. 그리고 반드시 Unique 하지 않은 경우에만 DISTINCT를 사용.
-------------------------------------------------------------------------------------
조인 대신 서브쿼리를활용하자
확인자 역할을 수행하는 테이블이란 From절에 나열된 테이블 중 Select 절에 추출하는 컬럼은 없고,
단순히 Where 절에 조인 조건이나 Filter 조건으로만 사용되는 테이블을 말한다.
단순히 확인자로서의 역할만 수행하는 테이블에 대해서 조인으로 처리할 때，종종 예상치 않은
비효율이 발생할 수 있다. 확인자 역할의 테이블을 조인으로 수행하도록 처리할 때 발생하는 성능 문제와
그 해결방법에 대해 알아 보자.
var b1 varchar2(10)
var b2 number
var b3 number
exec :b1 := 'A'
exec :b2 := 200000
exec :b3 := 300000
SELECT /*+ LEADING(T2 T1 T3) USE_NL(T2 T1 T3) */
DISTINCT t2.c1, t2.c2, t3.c3
FROM SUBQUERY_T2 T2,
SUBQUERY_T1 T1,
SUBQUERY_T1 T3
WHERE t2.c2 :b1
AND t2.c1 >= :b2
AND t2.c1 <= :b3
AND t2.c1 = t1.c4
AND t2.c2 = t1.c5
AND t1.c4 = t3.c1
AND t1.c5 = t3.c2
SQL 및수행결과정리
• 조인순서 : T2 → T1 →T3(조인은 고정，T2와 T3의 조인 연결 조건없음)
• 데이터 추출: t2.c1, t2.c2, t3.c3
• 조건 절: WHERE t2.c2 = :b1 AND t2.c1)=:b2 AND t2.c1(=:b3 AND t2.c1=t1.c4 AND
t2.c2=t1.c5 AND t1.c4 = t3.c1 AND t1.c5 = t3.c2)
KeyPoint
T2.C1컬럼은 Unique 하지만,T1.C4컬럼은 Unique 하지 않다. 그리고 테이블 T3의 c1컬럼과
C3 컬럼 값이 각각 Unique 하다.
SQL 트레이스 결과
T2 테이블을 먼저 읽어 3,846건 추출
그리고 T1테이블과 1:M 조인을 수행하여 데이터가 123,072 건으로 크게 증가
추출된 데이터 123,072 건은 다시 T3 테이블과 1:1 조인을 수행하여 총 123,072 건 추출
최종 추출된 123,072 건을 DISTINCT 처리하여 중복 값을 제거한 후 1,923 건으로 크게 감소
즉，T1 테이블과 조인 처리 시 불필요한 데이터가 증가했다고 판단
따라서 T2 테이블과 T3 테이블을 먼저 조인 한 후에 마지막으로 T1 테이블과 조인하도록
조인 순서를 바꾸어 준다면 성능이 개선 될 것으로 예상된다. 하지만 T2 와T3 테이블 간의
직접적인 조인 조건이 없어 조인 순서를 변경할 수 없기 때문에 이 방법은 사용할 수 없다.
조인 순서를 변경할 수 없으므로，더 이상 성능 개선방법이 없어 보인다.
하지만 개선방법은 있다. 만약 처음부터 중복 값을 주출하지 않는다면，조인 연결 시도횟수가
줄어 들어 비효율이 개선될 수 있다.
우선 T2와 T1 테이블을 조인할 때 데이터가 급격히 증가한 것으로 보아 T1과의 조인 시에
중복 데이터가 많이 발생했을 것으로 예상. 그리고 실제로도 데이터를 분석해 보면，T1과의
조인 시에 중복된 데이터가 많이 발생하는 것을 확인할 수 있다. 다행히 T1 테이블의
경우 데이터는 추출하지 않고 조인 연결만 관여하는 확인자 역할을 하는 테이블이다.
값을 추줄하지 않고 단순히 데이터 존재 유무를 확인하는 목적으로 사용된 테이블이므로
EXISTS를 사용하는 서브쿼리로 대체해도 데이터 정합성을 훼손하지 않았다. 그래서 아래와
같이 EXISTS를 사용한 서브쿼리로 SQL을 변경하여 수행하면 된다.
Note. 단，SUM이나 COUNT 등의 그룹 함수를 수행하는 경우는 데이터가 훼손될 수 있으므로
데이터 정합성 체크는 필히 수행
SELECT /*+ LEADING(T2 Tl@SUB T3) USE_NL(T2 Tl@SUB T3) */
t2.c1, t2.c2, t3.c3
FROM SUBQUERY_T2 T2,
SUBQUERY_T3 T3
WHERE t2.c2=:b1
AND t2.c1 >= :b2
AND t2.c1 <= :b3
AND EXISTS(SELECT /*+ QB_NAME(SUB) */ 'x'
FROM SUBQUERY_T1 T1
WHERE t1.c4=t2.c1
AND t1.c5=t2.c2
AND t1.c4=t3.c1
AND t1.c5=t3.c2);
트레이스 결과
T2 테이블에서 추출된 3,846 건에 대해 T1과의 조인 부분을 EXISTS로 변경한 결과
추줄된 데이터가 1,923 건으로 크게 줄었다. 따라서 T3 테이블의 조인 연결도
123,072 번에서 1,923 번으로 대폭 줄어들어 성능이 많이 개선.
지금까지 확인자 역할을 수행하는 테이블이 조인으로 처리되어 발생하는 성능 문제의 원인과
개선방법에 대해서 알아 보았다. 이와 유사한 형태의 SQL을 작성할 때 SQL의 성격을
면밀히 파악하는 것은 물론이고 서브쿼리의 특성을 잘 활용하여 가장 효율적인 방법으로
SQL을 작성해야 할 것이다.
WHERE절의 서브쿼리를조인으로 변경하자
서브쿼리를 조인으로 변경하여 성능 문제를 개선하는 방법
서브쿼리가 한 개만 포함된 SQL 이라면，Optimizer는 하나의 서브쿼리에 대한 Cost를
계산하면 되므로，통계정보만 정확하다면 비교적 효율적인 실행계획을 수립할 수 있게 된다.
하지만 Where 절에 서브쿼리가 많다면，Optimizer가 SQL에 대한 최적의 실행계획을
수립하는 것은 힘들다. 왜냐하면，서브쿼리들이 가질 수 있는 모든 조합에 대한 Cost를
계산해야 하므로 Optimizer가 실행계획을 최적화하는 과정이 더 부하가 될 수 있어 과감하게
정확도를 포기하고 모든 서브쿼리를 Unnest 수행한 것과 모두 수행하지 않은 것 두 가지의
Cost만 계산하기 때문이다.
따라서 SQL에 서브쿼리가 많다면，효율적인 실행계획을 수립할 확률이 급격히 떨어질 수
밖에 없다. 이것이 앞에서 언급했던 서브쿼리를 남용하지 말아야 하는 이유이다.
서브쿼리가 여러 개 존재하는 SQL의 성능 문제를 개선하는 방법
운영 환경에서 Bind Variable peeking 기능은 대부분 False로 운영하므로 해당 파라미터를
변경 후 테스트를 수행하였음을 미리 밝힌다.
ALTER SYSTEM SET "_optim_peek_user_binds"=FALSE
SELECT t1.*
FROM SUBQUERY_T1 t1
WHERE EXISTS(SELECT 'x'
FROM SUBQUERY_T2 t2
WHERE t2.c2 like :b1
AND t2.c3 >= :b2
AND t2.c3 <= :b3
AND t2.c1=t1.c4
AND t2.c2=t1.c5
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T3 t3
WHERE t3.c2 LlKE :b4
AND t3.c3 >= :b5
AND t3.c3 <= :b6
AND t3.c1=t1.c4
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T1 t1
WHERE t1.c5 LlKE :b7
AND t1.c4 >= :b8
AND t1.c4 <= :b9
AND t1.c4=t1.c4);
*Bind Variable Peaking란(이하 BVP) Bind 변수를 가진 SQL에 대해서 최초 파싱할 때，실제 Bind 값을 먼저 확인하고,
히스토그램을 참조하여 최적화하는 것을 말한다. 최초 하드파싱하는 SQL에 대해서는 가장 최적의 실행계획을 수립할 수
있다는 장점이 있다. 하지만 SQL이 최초 파싱 될 시점 배치프로그램이 수행되었고, Optimizer가 히스토그램을 참조해
Full Table Scan을 하는 실행계획을 수립했다면，이후 온라인에서 수행되는 SQL이 기간에 대한 Bind값의 기간이 짧아
Index Scan이 성능상 유리함에도 불구하고，기존에 수립한 실행계획에 대해 소프트 파싱으로 Full Table Scan을
하는 실행계획을 그대로 사용하게 되어 성능문제가 발생할수 있는 것이다. 이와 같은상황은 확률상 낮은 편이지만，
발생할경우 성능에 미치는 영향이 매우 심각하기 때문에 보통 BVP 기능은 False로 운영하는게 일반적이다.
BVP는 히든 파라미터인 _OPTIM_PEEK_USER_BINDS로 변경 가능하다.
앞의 SQL을 보면 서브쿼리가 총 3개 존재하고 각 서브쿼리 내의 Where 절 조건은 Bind 변수로 처리되어 있다.
위 SQL은 서브쿼리 외에는 별도의 상수 조건이 존재하지 않기 때문에 서브쿼리를 먼저 수행한 후 Main SQL이
비교 할 값을 전달받아 수행해야 SQL 성능이 양호 할 것이다. 우선 첫 번째 서브쿼리가 (SUBQUERY_T2) 먼저
수행할 때 성능이 유리하도록 Bind 변수 값을 설정한 후 트레이스 결과를 확인하자.
var b1 varchar2(10)
var b2 number
var b3 number
var b4 varchar2(10)
var b5 number
var b6 number
var b7 varchar2(10)
var b8 number
var b9 number
exec :b1 : = 'A'
exec :b2 : = 200000
exec :b3 : = 200100
exec :b4 : = '%'
exec :b5 : = 100000
exec :b6 : = 300000
exec :b7 : = '%'
exec :b8 : = 100000
exec :b9 : = 300000
SQL 작성자는 Bind 변수에 어떤 값을 사용할지 미리 알고 있기 때문에，SUBQUERY_T2를
먼저 수행해야 가장 효율적인 처리가 된다는 것을 알고 있다. 그러나 Optimizer는 Bind 변수에
어떤 값이 입력되는지 모른 체 실행계획을 수립해야 한다. 그래서 Optimizer는 앞의
트레이스 결과와 같이 SUBQUERY_T2가 아닌 SUBQUERY_T3 테이블을 먼저 읽고 수행하는 것이
효율적이라고 잘못 판단하여 비효율이 발생하게 되었다. 따라서 Optimizer가 잘못 수립한
실행계획을 SQL 작성자가 의도했던 대로，SUBQUERY_T2를 먼저 수행하도록 유도해야 한다.
이런 경우 성능 개선방법으로 서브쿼리를 조인으로 변경하는 것이다. 우선 서브쿼리로 작성된
부분을 인라인 뷰로 바꾸어 SQL을 작성하고 추출 데이터가 중복 값을 가진 경우에는 DISTINCT를
부여해 중복을 제거해 준다. 마지막으로 인라인 뷰로 바뀐 서브쿼리를 조인 순서를 조절하는
LEADING 힌트를 사용하여 실행계획을 제어하면 의도한 대로 SQL을 수행할수 있다.
아래는 SUBQUERY_T2를 먼저 수행하도록 SQL을 재작성 한 것이다.
SELECT /*+ LEADING(T2 T1) USE_NL(T2 Tl) * / t1.*
FROM SUBQUERY_T1 t1,
(SELECT DISTINCT t2.c1, t2.c2
FROM SUBQUERY_T2 t2
WHERE t2.c2 like :b1
AND t2.c3 >= :b2 AND t2.c3 <= :b3) t2
WHERE t2.c1=t1.c4
AND t2.c2=t1.c5
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T3 t3
WHERE t3.c2 like :b4
AND t3.c3 >= :b5 AND t3.c3 <= :b6 AND t3.c1=t1.c4)
AND EXiSTS(SELECT 'x'
FROM SUBQUERY_T1 t1
WHERE t1.c5 like :b7
AND t1.c4 >=:b8 AND t1.c4 <=:b9
AND t1.c4=t1.c4)
SQL을 재 작성하고 힌트를 부여하여，의도했던 대로 SQL이 수행되었다. 기존에 90.32초
소요되던 SQL이 0.08초 만에 수행되는 만족스러운 결과를 얻을 수 있었다.
SQL 작성 시 Where절에 많은 서브쿼리가 포함될 경우，성능이 좋지 않은 실행계획을 수립 할
확률이 매우 높다. 이런 경우 힌트를 사용하여 실행계획을 제어하기 어렵기 때문에,
서브쿼리를 조인으로 변경하는 것을 검토해야 한다.
'SQL 튜닝의 시작' 카테고리의 다른 글
| Chapter06.Function 수행과 SQL 성능 문제 이해하기 (0) | 2013.11.25 |
|---|---|
| Chapter05.Merge구문 이해와 효율적인 SQL 작성하기 (0) | 2013.11.25 |
| Chapter04.With절 이해와 효율적인 SQL 작성하기 (0) | 2013.11.25 |
| Chapter03.스칼라 서브쿼리의 이해와 효율적인 SQL 작성하기 (0) | 2013.11.25 |
| Chapter01. SQL 튜닝의 시작은? (0) | 2013.11.21 |
