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

대용량_SQL 성능 개선을 위한 WITH절 활용하기




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;
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절에서 미리 추출된 데이터를 사용해 계층구조를 분석할 수 있도록 유도할 수 있다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.