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