꿈꾸는 개발자, DBA 커뮤니티 구루비

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

{+}데이터 중복 액세스 제거하기{+}

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

{+}VIEW PREDICATING 성능 문제 제거하기{+}

View Predicating

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

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

{code:title=예제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;
{code}

{code:title=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)

---
{code}

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

{code}
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;
{code}

{code}
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)

---
{code}

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

{code}
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(+);
{code}

{code}
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)

---
{code}

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

{code}
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;
{code}

{code}
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;
{code}

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

대용량_SQL 성능 개선을 위한 WITH절 활용하기 (by iamheeya97) [2013.09.13]

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