SQL 튜닝의 시작 (2013년)
SQL 성능 개선을 위한 WITH절 활용하기 0 0 65,487

by 구루비스터디 WITH 계층쿼리 [2018.07.14]


SQL 성능개선을 위한 WITH절 활용하기


데이터 중복 액세스 제거하기
  • SQL에서 동일한 데이터를 반복처리시, 성능개선방법으로 WITH절이 많이 활용된다.
  • 데이터 건수는 적지만 데이터 추출시 I/O처리량이 많은 경우 효과적이다.
  • 여러 부분에 사용될 데이터를 1회 추출하여, Global Temporary Table에 저장하고, 요청시 저장된 데이터만 읽어 처리하면 되므로 SQL성능개선 및 I/O처리량되 대폭 줄어들게 된다.


VIEW PREDICATING 성능 문제 제거하기


View Predicating

  • 옵티마이저는 SQL의 성능개선을 위해, 뷰 외부조건을 뷰 내부로 침투 시키도록 시도하는데, 성공했을 경우 View Predicating이 발생되었다고 한다.


  • SQL성능문제 중 뷰 외부조건이 뷰 내부로 침투되지 못하고, 뷰의 데이터를 모두 처리한 후 조인연결조건을 Filter조건으로 사용하는 경우 심각한 비효율이 발생하는 경우가 있다.

예제2


select   /*+ ordered use_hash(wt3)*/
         wt1.c1, wt1.c2, wt2.c1, wt2.c2
from     t1 wt1
       , t2 wt2
       ,(
         select /*+ no_merge(wt3) */
                c1, c2, sum(c3) as c3
         from   t3
         group by c1, c2
       ) wt3
 where wt1.c1 = wt2.c1(+)
 and   wt1.c1 = wt3.c1(+)
 and   wt1.c2 = 'A'
 and   wt1.c3 <= 11000;


-- View Predicating안된 실행계획}
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=456 Card=38 Bytes=3K)
   1    0   HASH (GROUP BY) (Cost=456 Card=38 Bytes=3K)
   2    1     HASH JOIN (OUTER) (Cost=455 Card=38 Bytes=3K)
   3    2       NESTED LOOPS (OUTER) (Cost=120 Card=38 Bytes=3K)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=6 Card=38 Bytes=2K)
   5    4           INDEX (RANGE SCAN) OF 'T1_IDX_02' (INDEX) (Cost=3 Card=38)
   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3 Card=1 Bytes=28)
   7    6           INDEX (RANGE SCAN) OF 'T2_IDX_01' (INDEX) (Cost=2 Card=1)
   8    2       TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=332 Card=430K Bytes=7M)
-----------------------------------------------------------


  • 개선안 1 : t1에서 추출한 값을 인라인 뷰로 만든 후, wt3인라인 뷰에 강제로 추가

select   wt1.c1, wt1.c2, wt2.c1, wt2.c2
from     t1 wt1
       , t2 wt2
       ,(
         select /*+ leading(wt6) use_nl(wt6 wt5) */
                wt5.c1, wt5.c2, sum(wt5.c3) as c3
         from   t3 wt5
                , (
                   select c1, c2
                   from   t1
                   where  c2 = 'A' and c3<=11000
                  ) wt6
         where wt6.c1 = wt5.c1
         group by wt5.c1, wt5.c2
       ) wt3
 where wt1.c1 = wt2.c1(+)
 and   wt1.c1 = wt3.c1(+)
 and   wt1.c2 = 'A'
 and   wt1.c3 <= 11000;


Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=232 Card=38 Bytes=2K)
   1    0   NESTED LOOPS (OUTER) (Cost=232 Card=38 Bytes=2K)
   2    1     HASH JOIN (OUTER) (Cost=118 Card=38 Bytes=2K)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=6 Card=38 Bytes=1K)
   4    3         INDEX (RANGE SCAN) OF 'T1_IDX_02' (INDEX) (Cost=3 Card=38)
   5    2       VIEW (Cost=111 Card=38 Bytes=494)
   6    5         HASH (GROUP BY) (Cost=111 Card=38 Bytes=2K)
   7    6           NESTED LOOPS
   8    7             NESTED LOOPS (Cost=110 Card=38 Bytes=2K)
   9    8               TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=6 Card=38 Bytes=1K)
  10    9                 INDEX (RANGE SCAN) OF 'T1_IDX_02' (INDEX) (Cost=3 Card=38)
  11    8               INDEX (RANGE SCAN) OF 'T3_IDX_01' (INDEX) (Cost=2 Card=1)
  12    7             TABLE ACCESS (BY INDEX ROWID) OF 'T3' (TABLE) (Cost=3 Card=1 Bytes=29)
  13    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3 Card=1 Bytes=16)
  14   13       INDEX (RANGE SCAN) OF 'T2_IDX_01' (INDEX) (Cost=2 Card=1)
-----------------------------------------------------------


  • 개선안 2 : t1테이블 추출데이터를 With절로 선언한 후, wt3인라인뷰에 추가

with wt1 as (
              select /*+ materialize */
                     c1, c2
              from   t1
              where  c2 = 'A' and c3<=11000
            )
select   wt1.c1, wt1.c2, wt2.c1, wt2.c2
from     wt1
       , t2 wt2
       ,(
         select /*+ leading(wt1) use_nl(wt3 wt4) */
                wt3.c1, wt3.c2, sum(wt3.c3) as c3
         from   t3 wt3
                , wt1
         where wt1.c1 = wt3.c1
         group by wt3.c1, wt3.c2
       ) wt4
 where wt1.c1 = wt2.c1(+)
 and   wt1.c2 = wt2.c2(+)
 and   wt1.c1 = wt4.c1(+)
 and   wt1.c2 = wt4.c2(+);


Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=240 Card=38 Bytes=2K)
   1    0   TEMP TABLE TRANSFORMATION
   2    1     LOAD AS SELECT OF 'SYS_TEMP_0FD9D6608_621035'
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=6 Card=38 Bytes=1K)
   4    3         INDEX (RANGE SCAN) OF 'T1_IDX_02' (INDEX) (Cost=3 Card=38)
   5    1     NESTED LOOPS (OUTER) (Cost=234 Card=38 Bytes=2K)
   6    5       HASH JOIN (OUTER) (Cost=120 Card=38 Bytes=1K)
   7    6         VIEW (Cost=2 Card=38 Bytes=608)
   8    7           TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6608_621035' (TABLE (TEMP)) (Cost=2 Card=38 Bytes=608)
   9    6         VIEW (Cost=117 Card=38 Bytes=608)
  10    9           HASH (GROUP BY) (Cost=117 Card=38 Bytes=2K)
  11   10             NESTED LOOPS
  12   11               NESTED LOOPS (Cost=116 Card=38 Bytes=2K)
  13   12                 VIEW (Cost=2 Card=38 Bytes=494)
  14   13                   TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6608_621035' (TABLE (TEMP)) (Cost=2 Card=38 Bytes=608)
  15   12                 INDEX (RANGE SCAN) OF 'T3_IDX_01' (INDEX) (Cost=2 Card=1)
  16   11               TABLE ACCESS (BY INDEX ROWID) OF 'T3' (TABLE) (Cost=3 Card=1 Bytes=29)
  17    5       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3 Card=1 Bytes=16)
  18   17         INDEX (RANGE SCAN) OF 'T2_IDX_01' (INDEX) (Cost=2 Card=1)
-----------------------------------------------------------


계층 쿼리의 데이터 처리 최소화하기

select apt_id
       , count(subsno) apt_cnt)subsno_1
       , substr(max(sys_connect_by_path(subsno,',')), 2) subsno_lst
from   (
        select a.*
               row_number() over (partition by tmp_key order by subsno) rnum
        from   (
                select ....
                from   tb_logdata a
                where  .....
                group by a.apt_id, a.subsno
                having count(a.subsno) >=2) a) b
start with rnum = 1
connect by prior rnum = rnum -1
and prior temp_key = temp_key
group by apt_id;


-- with 절
witth temp_t1 as (
                  select /*+ materialize */
                         a.*
                  row_number() over (partition by tmp_key order by subsno) rnum
                  from   (
                          select ....
                          from   tb_logdata a
                          where  .....
                          group by a.apt_id, a.subsno
                          having count(a.subsno) >=2
                          ) a
                  )
select apt_id
       , count(subsno) apt_cnt)subsno_1
       , substr(max(sys_connect_by_path(subsno,',')), 2) subsno_lst
from   tem_t1
start with rnum = 1
connect by prior rnum = rnum -1
and prior temp_key = temp_key
group by apt_id;


  • 계층구조를 처리하는 SQL은 보통 START WITH \-> CONNECT BY \-> WHERE 순서로 수행되나, 간혹 옵티마이저가 잘못된 해석을 하는 경우가 있다.
  • 위 SQL은 계층구소분석대상이 되는 데이터에 대해 먼저 그룹핑을 했으나, 실행계획상으로는 그룹핑이 수행되지 않은 채 수행되어, 성능문제가 발생했다.
  • 먼저 데이터를 그룹핑 하여 WITH절로 선언한 후 계층구조를 처리하도록 한다. 이때 반드시 Materialize방식으로 수행되어야 WITH절에서 미리 추출된 데이터를 사용해 계층구조를 분석할 수 있도록 유도할 수 있다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3788

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입