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

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




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

데이터중복액셰스제거하기

SQL 에서 동일한 데이터를 반복 처리하여 성능 문제를 발생시키는 경우가 있다. 이런 경우 개선방법으로 많이 활용되는 구문이 With 절
(With절의 사용으로 반복수행이 되지 않고 데이터 추출이 1회만 수행되기에 I/O 발생도 줄어들게 된다.
 단, 데이터 추출 건수가 매우 많다면 그 데이터들이 Temporary Table에 저장하는 비용적인 문제나 그데이터를 읽어오는데 드는 비용도 있기에
  이런경우에는 꼭 With절을 사용하여야 하는지에 대한 검토가 필요하다. )

VIEW PREDICATING 성능 문제 제거하기

옵티마이저는 SQL의 성능개선을 위해, 뷰 외부조건을 뷰 내부로 침투 시키도록 시도하는데, 성공했을 경우 View Predicating이 발생되었다고 한다
예제1
- 전제조건
1. Outer Join 이므로 WITH T1 테이블을 먼저 수행한다.
2. T1 과 조인 연결 컬럼인 T2 와 T3 테이블의 C1 컬럼 값은 Unique 하다.

- View Predicating 되지 못했을 때

SELECT tl.cl ,
       tl.c2 ,
       t2.cl ,
       t2.c2 ,
       t3.c3
FROM   WITH_Tl Tl,
       WITH_T2 T2,
      (
       SELECT /*+ NO MERGE */
              cl , c2, sum(c3) c3
       FROM WITH_T3
       GROUP BY cl , c2
       )t3
WHERE t1.c1 = t2.c1(+)
AND   t1.c1 = t3.c1(+)
AND   tl.c2 = 'A'
AND   t1.c3 <= 11000;

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

위의 실행계획의 8번 라인을 보게 되면 T3가 조건을 받지 못하고 TABLE ACCESS (FULL)이 수행되고 있음을 볼수있다.
위의 문제에 따른 성능 개선을 위한 방안이 2가지가 나올수 있는데 
첫 번째는 WITH_T1 에서 주출한 값을 인라인 뷰로 만든 후 인라인 뷰 T3(WITH_T3) 안에 강제로 추가하는 방법
두번째는 With절을 선언하여 필요한 데이터를 미리 주줄한 후,필요할 때 마다 재 사용하도록 SQL 을 작성하는 방법

방안1)
select   tl.cl ,
         tl.c2 ,
         t2.cl ,
         t2.c2 ,
         t3.c3
from     WITH_Tl Tl,
         WITH_T2 T2,
        (
       SELECT /*+ NO MERGE */
              cl , c2, sum(c3) c3
       FROM WITH_T3,
            (SELECT c1 , c2
               FROM WITH_T1
              WHERE c2 = 'A ' AND c3 <= 11000
             )t1
       WHERE tl.c1 = t3.c1
       GROUP BY t3.c1 , t3.c2 ) t3
where t1.c1 = wt2.c1(+)
 and  t1.c1 = wt3.c1(+)
 and  t1.c2 = 'A'
 and  t1.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)
-----------------------------------------------------------

이 개선안은 T3를 처리하는데 많은 개선이 될것이다. 하지만 이 방안의 경우 WHIT_T1 의 동일한 데이터 집합을 2번 수행하게 되기에 비효율이 존재하고 있다.

방안2)

with t1 as (
              select /*+ materialize */
                     c1, c2
              from   t1
              where  c2 = 'A' and c3<=11000
            )
select   t1.c1, t1.c2, t2.c1, t2.c2
from     T1
       , WHIT_T2 T2
       ,(
         select /*+ leading(wt1) use_nl(wt3 wt4) */
                wt3.c1, wt3.c2, sum(wt3.c3) as c3
         from   WHIT_T3 T3
               ,t1
         where t1.c1 = t3.c1
         group by t3.c1, t3.c2
       ) t3
 where t1.c1 = t2.c1(+)
 and   t1.c2 = t2.c2(+)
 and   t1.c1 = t3.c1(+)
 and   t1.c2 = t3.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)
-----------------------------------------------------------

문서정보

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