by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

서브 쿼리 동작방식 이해하기




서브쿼리 Unnesting

  1. Filter 동작방식
  2. 조인 동작방식
  3. 서브쿼리 동작방식을 제어하는 힌트들
  4. 예제 SQL

Filter 동작방식

create_subquery_t1.sql
create_subquery_t2.sql
create_subquery_t3.sql

테스트 1

Main SQL의 추출 결과가 많고 입력 값이 Unique한 경우

sql_test01.sql
alter session set events '10046 trace name context forever, level 12';

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);

alter session set events '10046 trace name context off';
trace_result.txt

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    6.739       13.057      20367     789240          0     230001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2304    6.739       13.057      20367     789240          0     230001

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
 230001  FILTER  (cr=789240 pr=20367 pw=0 time=9233297 us)
 380001   FILTER  (cr=3611 pr=928 pw=0 time=2266433 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=3611 pr=928 pw=0 time=1055114 us cost=368 size=4560036 card=380003)
 230001   INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=785629 pr=19439 pw=0 time=0 us cost=3 size=10 card=2)

********************************************************************************

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 한 경우

sql_test02.sql
alter session set events '10046 trace name context forever, level 12';

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);

alter session set events '10046 trace name context off';
trace_result02.txt
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.041          0          0          0          0
Fetch        2    0.031        0.034          0       1332          0          5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.031        0.075          0       1332          0          5

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
      5  FILTER  (cr=1332 pr=0 pw=0 time=0 us)
      5   FILTER  (cr=1317 pr=0 pw=0 time=104 us)
      5    TABLE ACCESS FULL SUBQUERY_T2 (cr=1317 pr=0 pw=0 time=0 us cost=368 size=4560036 card=380003)
      5   INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=15 pr=0 pw=0 time=0 us cost=3 size=10 card=2)


테스트2의 SQL트레이스 결과 : Filter동작방식으로 수행되지만, Main SQL의 조건이 효율적이어서 추출되는 데이터 건수가 총 5건이다.
그래서 서브쿼리도 5번만 수행되기 때문에, SQL의 성능은 테스트1과 비교해서 매우 양호

테스트3

Main SQL의 추출건수는 많지만, Input 값의 종류가 26가지인 경우

sql_test03.sql
alter session set events '10046 trace name context forever, level 12';

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);

alter session set events '10046 trace name context off';
trace_result03_2.txt
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.109        0.267         24        588          0          0
Fetch     3802    0.905        1.569       1365       5185          0     380001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     3804    1.014        1.837       1389       5773          0     380001

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
 380001  FILTER  (cr=5185 pr=1365 pw=0 time=3281341 us)
 380001   FILTER  (cr=5107 pr=1312 pw=0 time=1973209 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=5107 pr=1312 pw=0 time=778436 us cost=368 size=4560036 card=380003)
     26   INDEX RANGE SCAN SUBQUERY_T1_IDX_02 (cr=78 pr=53 pw=0 time=0 us cost=3 size=4 card=2)


트레이스 결과 : 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으로 처리하고 있다면, 심각한 성능문제가 발생할 수 있기 때문이다.

조인 동작방식

조인 동작방식의 차이점은 가변성이다.

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

sql_test04.sql
alter session set events '10046 trace name context forever, level 12';

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);

alter session set events '10046 trace name context off';
trace_result04.txt
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.016        0.005          0          3          0          0
Fetch        2  330.987     1207.685    7842071    7841166          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4  331.003     1207.690    7842071    7841169          0         11

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=7841166 pr=7842071 pw=0 time=0 us)
    221   FILTER  (cr=7 pr=3 pw=0 time=5610 us)
    221    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=7 pr=3 pw=0 time=3960 us cost=4 size=2664 card=222)
    221     INDEX RANGE SCAN PK_SUQUERY_2 (cr=4 pr=3 pw=0 time=2420 us cost=3 size=0 card=222)
     11   FILTER  (cr=7841159 pr=7842068 pw=0 time=0 us)
     11    TABLE ACCESS FULL SUBQUERY_T1 (cr=7841159 pr=7842068 pw=0 time=0 us cost=407 size=5 card=1)


테스트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)

sql_test04_1.sql
alter session set events '10046 trace name context forever, level 12';

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 /*+ unnest hash_sj */ 'x' --no_unnest를 unnest hash_sj로 변경
		from subquery_t1 t1
		where t1.c6=t2.c3 and t1.c6 >= :b1);

alter session set events '10046 trace name context off';
trace_result04_1.txt
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.003          0          0          0          0
Fetch        2    2.917        4.371      37303      37314          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    2.917        4.374      37303      37314          0         11

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=37314 pr=37303 pw=0 time=0 us)
     11   HASH JOIN SEMI (cr=37314 pr=37303 pw=0 time=0 us cost=10444 size=748 card=44)
    221    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=5 pr=0 pw=0 time=1430 us cost=4 size=1860 card=155)
    221     INDEX RANGE SCAN PK_SUQUERY_2 (cr=3 pr=0 pw=0 time=550 us cost=3 size=0 card=222)
6400640    TABLE ACCESS FULL SUBQUERY_T1 (cr=37309 pr=37303 pw=0 time=11181310 us cost=10394 size=32003330 card=6400666)

기존의 Filter 동작방식이 아닌 조인 동작방식으로 변경되었고, 조인은 Hash Join Semi로 수행

실행계획의 변경은 SQL이 Filter동작방식으로 수행되어 221번 반복되는 FTS을 제거하고,

한번만 FTS를 하도록 변경된 것이다.

개선 전의 SQL의 경우 인덱스가 없는 것이 비효율적이기도 하지만, 인덱스를 생성하기 힘든 경우,

힌트를 사용해서 동작방식을 변경하는 것 만으로도 개선 효과를 볼 수 있다.

테스트2

Main SQL의 추출 건수는 매우 많고 서브쿼리에 있는 상수 조건이 매우 효율적이어서 서브쿼리를 먼저 수행해야 효율적인 처리가 되는 SQL이 있다고 가정

sql_test05.sql
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);
trace_result05
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.002          0          0          0          0
Execute      1    0.016        0.009          0          6          0          0
Fetch        5  460.250      464.591      38477   31887999          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7  460.265      464.601      38477   31888005          0        320

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=31887999 pr=38477 pw=0 time=957 us)
16000000   FILTER  (cr=37312 pr=37303 pw=0 time=99756672 us)
16000000    TABLE ACCESS FULL SUBQUERY_T1 (cr=37312 pr=37303 pw=0 time=47979004 us cost=10473 size=192000000 card=16000000)
      5   FILTER  (cr=31850687 pr=1174 pw=0 time=0 us)
      5    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=31850687 pr=1174 pw=0 time=0 us cost=3 size=10 card=1)
15741952     INDEX UNIQUE SCAN PK_SUQUERY_2 (cr=16108735 pr=520 pw=0 time=0 us cost=2 size=0 card=1)


위 SQL을 Filter 동작 방식이 아닌 조인 동작방식(Hash Join Semi)으로 유도 했을 때 변화를 보자.

sql_test05_1.sql
alter session set events '10046 trace name context forever, level 12';

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 /*+ unnest hash_sj */ 'x'
	    from subquery_t2 t2
	    where t2.c1=t1.c4 and t2.c3 >= :b3
	    and t2.c3 <=:b4);

alter session set events '10046 trace name context off';
trace_result05_1.txt
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.003          0          0          0          0
Fetch        5    6.146        6.684      37959      38628          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7    6.146        6.688      37959      38628          0        320

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=38628 pr=37959 pw=0 time=1754 us)
    320   HASH JOIN RIGHT SEMI (cr=38628 pr=37959 pw=0 time=797 us cost=10933 size=8448 card=384)
      5    TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=656 pw=0 time=8 us cost=370 size=50 card=5)
16000000    TABLE ACCESS FULL SUBQUERY_T1 (cr=37312 pr=37303 pw=0 time=26194512 us cost=10450 size=192000000 card=16000000)


기존 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)힌트를 추가 해서 실행 계획 유도

sql_test05_2.sql
alter session set events '10046 trace name context forever, level 12';

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 /*+ qb_name(main) nl_sj(t1@main) leading (t2@sub) */ 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);

alter session set events '10046 trace name context off';
trace_result05_2.txt

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.004          0          0          0          0
Fetch        5    0.078        0.553         67       1654          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7    0.078        0.558         67       1654          0        320

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=1654 pr=67 pw=0 time=17864 us)
    320   NESTED LOOPS  (cr=1654 pr=67 pw=0 time=17066 us)
    320    NESTED LOOPS  (cr=1334 pr=3 pw=0 time=11803 us cost=473 size=7040 card=320)
      5     SORT UNIQUE (cr=1316 pr=0 pw=0 time=20 us cost=370 size=50 card=5)
      5      TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=8 us cost=370 size=50 card=5)
    320     INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=18 pr=3 pw=0 time=3024 us cost=2 size=0 card=64)
    320    TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=320 pr=64 pw=0 time=0 us cost=66 size=768 card=64)

더욱 성능이 개선 되었다.

서브쿼리 동작방식을 제어하는 힌트들


HINT 명 설명
NO_UNNEST 서브쿼리를 Filter 방식으로 유도하는 HINT
UNNEST 서브쿼리를 조인동작방식으로 유도하는 HINT
NL_SJ 조인동작방식 중 Nested Loops Join Semi로 유도하는 HINT
HASH_SJ 조인동작방식 중 Hash Join Semi로 유도하는 HINT
NL_AJ 조인동작방식 중 Nested Loops Join Anti로 유도하는 HINT
HASH_AJ 조인동작방식 중 Hash Join Anti로 유도하는 HINT
ORDERED FROM절에 나열된 순서대로 조인 순서를 정하는 HINT (SUB QUERY가 존재하면 서브쿼리부터 수행하도록 유도함
QB_NAME QUERY BLOCK의 이름을 지정하는 HINT
SWAP_JOIN_INPUTS HASH OUTER JOIN과 같이 순서가 고정된 상황에서 조인 순서를 바꾸도록 유도하는 HINT
NO_SWAP_JOIN_INPUTS HASH OUTER JOIN과 같이 순서가 고정된 상황에서 조인 순서를 바꾸지 못하도록 유도하는 HINT
PUSH_SUBQ 서브쿼리가 먼저 수행하도록 유도하는 HINT



예제 SQL


쿼리 example.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 을 작성해야 할 것이다.

문서정보

Labels

동작방식 동작방식 Delete
서브쿼리 서브쿼리 Delete
이해하기 이해하기 Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.