Mooyoung2020

Mooyoung2020

SQL Tunner

Oracle OUTER JOIN 정리 3

Updated:



예제 테이블 생성

  • T1 테이블 : 50만건의 레코드가 있으며 C1 이 PK입니다.
C1 C2 C3
1 B 100000
2 C 100001
3 D 100002
4 E 100003
5 F 100004
6 G 100005
7 H 100006
8 I 100007
9 J 100008
10 K 100009
  • TT2 테이블 : 99건의 레코드가 있으며 C1 이 PK입니다
C1 C2 C3
1 B 100000
2 C 100001
3 D 100002
4 E 100003
5 F 100004
6 G 100005
7 H 100006
8 I 100007
9 J 100008
10 K 100009

1. 오라클 LEFT OUTER JOIN 을 스칼라쿼리로 변경

Left Outer Join문장을 각각 스칼라서브쿼리, 펑션연결 sql로 변환하면서 성능비교를 해봅니다.우리가 흔히 사용하는 SQL형태인데 어느것이 가장 좋은 SQL형태인지 알고 사용하면 시스템 전체에 걸리는 SQL부하를 획기적으로 줄일수 있습니다. 여기에서 대상 시스템은 사용자가 많고 데이타를 빈번하게 처리하는 OLTP(On Line Transactio Processing) 시스템을 대상으로 가정합니다.

DW성 SQL들은 사용자가 많이 없고 다량의 데이타를 처리하는 개념이므로 OLTP와는 반대개념으로 생각하면 됩니다. 즉 조인형태로 SQL을 작성하여 해쉬조인으로 유도하는것이 가장 좋습니다. DW에서는 스칼라서브쿼리나 펑션, 조인이지만 내부적으로 NL조인으로 처리되는 방식은 속도가 좋지않게 나옵니다.

가. NL(Nested Loop)조인을 사용한 Left Outer 조인

OLTP시스템에서 많이 사용하는 화면의 SQL이라 가정하고 SQL형태를 만들어봅니다. 먼저 Left Outer Join 형태인데 내부적으로 NL조인으로 유도하여 처리한 결과 45블럭을 스캔하여 99건을 가지고 왔습니다.

select /*+LEADING(A) USE_NL(B) */ a.c1, b.c1, a.c2
from tt2 a, t1 b
where a.c1=b.c1(+);

실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     99   NESTED LOOPS OUTER (cr=45 pr=1 pw=0 time=0 us cost=201 size=693 card=99)
     99    TABLE ACCESS FULL TT2 (cr=13 pr=1 pw=0 time=1568 us cost=3 size=297 card=99)
     99    INDEX RANGE SCAN T1_IX1 (cr=32 pr=0 pw=0 time=0 us cost=2 size=4 card=1)(Object ID 78970)
  • 10046 trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.000 0.020 1 45 0 99
Total 13 0.000 0.022 1 45 0 99

나. HASH 조인을 사용한 Left Outer 조인

OLTP성 시스템인데 내부적으로 HASH 조인을 사용하여 처리한 형태입니다. HASH조인은 대부분 풀테이블스캔을 기반으로 오라클 메모리를 이용하여 처리하다보니 서버에 부하가 좀 걸리는 단점이 있습니다. DW성 배치처리용 SQL이라면 당연히 해쉬조인이 NL조인보다 유리하다. 각 SQL마다 성격이 다르므로 대략 이정도로 이해하면 될것 같습니다. 상세한 사항은 튜닝 분야에서 다루기로 하겠다. 99건의 데이타를 추출하는 SQL인데 무리하게 해쉬조인으로 사용하다보니 테이블을 풀스캔하여 오히려 NL조인보다 블럭IO가 1138개로 많이 나왔습니다.

select /*+LEADING(A) USE_HASH(B) */ a.c1, b.c1, a.c2
from tt2 a, t1 b
where a.c1=b.c1(+);

실행계획
Rows     Row Source Operation
---------------------------------------------------------
      0  STATEMENT
     99   HASH JOIN OUTER (cr=1138 pr=1091 pw=0 time=0 us cost=320 size=693 card=99)
     99    TABLE ACCESS FULL TT2 (cr=3 pr=0 pw=0 time=0 us cost=3 size=297 card=99)
 500000    INDEX FAST FULL SCAN T1_IX1 (cr=1135 pr=1091 pw=0 time=140552 us cost=314 size=2047248
  • 10046 trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.141 0.474 0 1138 0 99
Total 13 0.141 0.498 0 1138 0 99

다. Left Outer 조인을 스칼라서브쿼리로 변경

스칼라서브쿼리로 변경하여 테스트해보니 35블럭으로 Left Out Join 45블럭보다 10블럭 작게 소모되었습니다. 극한의 튜닝개념에서는 이 10블럭 차이는 굉장히 중요한 수치입니다. 예를 들어 초당 SQL이 1만개가 발생하는 시스템에서 이 SQL이 약 30%정도 차지하고 있습니다면 이 10블럭을 개선하게되면 CPU 감소나 DB서버에 발생하는 병목현상이 엄청 개선될것입니다.

select a.c1, (select b.c1 from t1 b  where a.c1=b.c1), a.c2
from tt2 a;

실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     99   INDEX RANGE SCAN T1_IX1 (cr=22 pr=0 pw=0 time=0 us cost=3 size=4 card=1)(Object ID 78970)
     99   TABLE ACCESS FULL TT2 (cr=13 pr=0 pw=0 time=0 us cost=3 size=297 card=99)
  • 10046 trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.000 0.001 0 35 0 99
Total 13 0.000 0.001 0 35 0 99

라. 펑션 연결 SQL

현장지원을 나가다 보면 금융산업에서는 펑션같은 PL/SQL을 OLTP성 화면에 사용하지 않는것을 볼수있습니다. 물론 일부 카드사 같은곳에서는 사용하는것을 봤지만 증권, 은행은 사용자가 많은 화면에서는 펑션처리를 하지않습니다. 제조나 공공, 국방등은 대부분 펑션을 사용하여 SQL을 만든 곳을 많이 볼수있습니다. 펑션을 사용하여 스칼라서브쿼리 대신 사용할수 있으나 사용자가 많은 빅싸이트에 펑션연결 SQL들을 사용하게 되면 성능문제가 발생하는것을 많이 볼수있습니다. 왜일까요?

아래의 SQL을 보면 TT2 테이블에서 99건 추출하여 한건씩 Onerow라는 펑션으로 가져간다. 이 펑션은 tt1에서 한건씩 모두 99번 가져가서 펑션을 처리하여야 최종값을 줄수있습니다. 즉 펑션이 99번 호출되는것입니다. 이 펑션을 사용하는 화면을 사용자 1명만 사용하는게 아니라 다른 사용자들도 그화면을 사용할수 있고, 또한 펑션을 공유하여 코딩하므로 다른 화면에서도 사용할수도 있습니다.

그러니 많이 사용하는 화면에 펑션이 들어가 있으면 스칼라서브쿼리로만 SQL을 만들어 놨을떄보다 당연히 성능이 저하됩니다. 향후 펑션문제에 대해 집중 조명하도록 하겠다.

아래의 SQL에서 펑션을 사용하게 되면 총 310블럭을 조회하게 됩니다. 이중 펑션애 SQL이 사용한 블럭은 297블럭을 사용하였다. 스칼라서브쿼리 35블럭보다 8배이상 많이 소모되었습니다.

select a.c1,  onerow(c1), a.c2
from tt2 a;

create or replace function onerow
(v_c1 in varchar2)
return char is r_c1 number;
Begin
   select c1 into r_c1
   from t1 b  where c1=v_c1;
  return r_c1;
 end onerow;
/

실행계획
Rows     Row Source Operation -- 310블록에는 아래의 297블록이  포함된 값입니다
--------------------------------------------------------------
      0  STATEMENT
     99   TABLE ACCESS FULL TT2 (cr=13 pr=0 pw=0 time=0 us cost=3 size=297 card=99)
  • 10046 trace : 전체 SQL부하
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 11 0.031 0.017 0 310 0 99
Total 13 0.031 0.017 0 310 0 99
  • 10046 trace : 펑션내부 SQL부하
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.001 0 0 0 0
Fetch 11 0.016 0.001 0 297 0 99
Total 13 0.016 0.002 0 297 0 99

2. 칼럼이 2개 이상일 경우 성능비교

이번에는 1번과 같이 Left Outer Join을 스칼라서브쿼리로 바꾸는데 이때 동일테이블에 접근하는 스칼라서브쿼리가 2개 이상일 경우 성능 비교를 해봅니다. 이때는 스칼라서브쿼리 통합기술을 한번 살펴보고 테스트해 봅니다.

위의 SQL과 비교하면 SELECT 절에 B.C2가 추가되었습니다. 칼럼이 추가되면 어떤 상황이 나오는지 면밀히 살펴보면서 비교해봅니다.

가. NL(Nested Loop)조인을 사용한 Left Outer 조인

LEFT Outer Join형태로 b 테이블에서 칼럼을 2개 조회하였다. b.c2가 추가되었습니다. 1번 가항 SQL과 비교시 35블럭에서 56블럭으로 부하가 증가했습니다.

select /*+LEADING(A) USE_NL(B) */ a.c1, b.c1, a.c2, B.C2
from tt2 a, t1 b
where a.c1=b.c1(+);

실행계획

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     99   NESTED LOOPS  (cr=65 pr=0 pw=0 time=0 us)
     99    NESTED LOOPS  (cr=54 pr=0 pw=0 time=1274 us cost=102 size=8 card=1)
     99     TABLE ACCESS BY INDEX ROWID TT2 (cr=22 pr=0 pw=0 time=196 us cost=2 size=297 card=99)
     99      INDEX FULL SCAN IX_005 (cr=11 pr=0 pw=0 time=98 us cost=1 size=0 card=99)(Object ID 79504)
     99     INDEX RANGE SCAN T1_IX1 (cr=32 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(Object ID 78970)
     99    TABLE ACCESS BY INDEX ROWID T1 (cr=11 pr=0 pw=0 time=0 us cost=2 size=5 card=1)
  • 10046 trace
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 11 0.000 0.000 1 56 0 99
Total 13 0.000 0.001 1 56 0 99

나. HASH 조인을 사용한 Left Outer 조인

이번에는 해쉬조인을 사용하여 조회하였다. 1번 나항과 비교시 1138블럭에서 증가한 1189블럭의 IO를 발생시켰다.

select /*+LEADING(A) USE_HASH(B) */ a.c1, b.c1, a.c2, B.C2
from tt2 a, t1 b
where a.c1=b.c1(+);

실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     99   HASH JOIN  (cr=1189 pr=1173 pw=0 time=0 us cost=338 size=8 card=1)
     99    TABLE ACCESS FULL TT2 (cr=3 pr=0 pw=0 time=98 us cost=3 size=297 card=99)
 500000    TABLE ACCESS FULL T1 (cr=1186 pr=1173 pw=0 time=202892 us cost=332 size=2559060  card=511812)
  • 10046 trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.219 0.416 1173 1189 0 99
Total 13 0.219 0.416 1173 1189 0 99

다. Left Outer 조인을 스칼라서브쿼리로 변경

1번 다항에서는 Left Outer Join을 스칼라서브쿼리로 변경했을때 35블럭으로 가장 좋은 성능을 나타냈다. 그러나 칼럼을 하나 더 추가했을때에는 68블럭으로 2번 가항(56블럭)보다 더 성능이 좋지않다. 이는 당연히 칼럼을 하나 더 추가하여 이런 문제가 발생한것입니다. 이를 개선할려면 이미 포스팅한 스칼라서브쿼리의 통합 편에서 언급한 동합기술을 사용하면 됩니다. 

select a.c1, (select b.c1 from t1 b where a.c1=b.c1)  , a.c2, 
             (select b.c2 from t1 b  where a.c1=b.c1) 
from tt2 a;

실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     99   INDEX RANGE SCAN T1_IX1 (cr=22 pr=0 pw=0 time=0 us cost=3 size=4 card=1)(Object ID 78970)
     99   TABLE ACCESS BY INDEX ROWID T1 (cr=33 pr=0 pw=0 time=0 us cost=4 size=5 card=1)
     99    INDEX RANGE SCAN T1_IX1 (cr=22 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(Object ID 78970)
     99   TABLE ACCESS FULL TT2 (cr=13 pr=0 pw=0 time=0 us cost=3 size=297 card=99)
  • 10046 trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.016 0.002 0 68 0 99
Total 13 0.016 0.005 0 68 0 99

라. 스칼라서브쿼리 통합

오라클 11g DB이상에서 사용할수 있는 Regexp_substr 을 사용하여 통합해보았다. 물론 rpad와 substr 기능이 제공되는 DBMS에서도 사용가능하다. 자세한것은 스칼라서브쿼리 통합편을 다시봅니다. 통합후 46블럭으로 최고의 블럭 IO를 보이고 있습니다.

select x.c1, x.c2, 
       regexp_substr(bb, '[^/]+',1,1) a, regexp_substr(bb, '[^/]+',1,2 ) b
from (select  a.c1, a.c2, 
             (select b.c1||'/'||b.c2 
             from t1 b  
             where  a.c1=b.c1) as bb
      from  tt2 a) x ;

실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     99   TABLE ACCESS BY INDEX ROWID T1 (cr=33 pr=0 pw=0 time=0 us cost=4 size=7 card=1)
     99    INDEX RANGE SCAN T1_IDX1 (cr=22 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(Object ID 80674)
     99   TABLE ACCESS FULL TT2 (cr=13 pr=0 pw=0 time=0 us cost=3 size=297 card=99)
  • 10046 trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.016 0.003 0 46 0 99
Total 13 0.016 0.004 0 46 0 99

3. 결언

이번 칼럼은 중요한 SQL형태에 대해 각각 성능비교를 해보았습니다. 일선에서 가장 많이 사용하는 형태들을 가지고 테스트했는데, OLTP시스템에서 빈번하게 사용하고 작은 데이타를 처리하는 화면이라면 조인이나, 스칼라 서브쿼리방식으로 NL조인 위주로 처리하고, DW성 배치처리 SQL이라면 스칼라쿼리나 펑션연결은 지양하고 조인방식의 해쉬조인 위주로 처리하는게 기본적인 개념입니다. 그리고 펑션관련 해서는 다음번에 집중적으로 이야기하도록 하겠습니다.

You May Also Enjoy