본 문서에서는 상황에 따른 다양한 Tuning 사례를 안내 합니다.

Tuning 사례

3. JOIN 순서/방법

3.1. 힌트 추가

EXAMPLE

SELECT …

FROM COLLECTUNIT_MISSING A, COLLECTUNIT_PREDICTION B

WHERE A.ANALYSISDATE = ? AND B.ANALYSISDATE(+) = ? AND B.COLLECTUNITTYPE(+) = ?

AND A.MISSINGCNT > 5

AND A.BUSROUTEID = B.BUSROUTEID (+)

AND A.COLLECTUNITID = B.COLLECTUNITID(+)

AND A.MISSTYPEBIS = ‘1‘

​

2072  hash join (reverse left outer) (et=66499, cr=0, cu=0, co=42873, cpu=4, ro=23782233)

29119  partition iterator(ALL PART) (PS:1, PE:25) (et=72, cr=0, cu=0, co=4749, cpu=0, ro=16409)

29119  table access (rowid) COLLECTUNIT_PREDICTION(8465) (et=8583873463831278, cr=296, cu=0, co=4749, cpu=0, ro=16409)

29119  filter (et=3586, cr=0, cu=0, co=302, cpu=0, ro=16409)

33697  index (range scan) COLLECTUNIT_PREDICTION_PK(8491) (et=3814, cr=354, cu=0, co=301, cpu=0, ro=32818)

2072  partition iterator(ALL PART) (PS:1, PE:22) (et=132, cr=0, cu=0, co=37181, cpu=2, ro=1366144)

2072  table access (full) COLLECTUNIT_MISSING(3292) (et=8583873470045409, cr=87152, cu=230, co=37181, cpu=2, ro=1366144)

Join에서는 Left row 개수가 적을수록 처리가 용이합니다.
위 예제는 Left가 right 보다 row수가 많이 생성되므로 Leading 힌트를 사용합니다.

SELECT /*+ LEADING(A B) */…

FROM COLLECTUNIT_MISSING A, COLLECTUNIT_PREDICTION B

WHERE A.ANALYSISDATE = ? AND B.ANALYSISDATE(+) = ? AND B.COLLECTUNITTYPE(+) = ?

AND A.MISSINGCNT > 5

AND A.BUSROUTEID = B.BUSROUTEID (+)

AND A.COLLECTUNITID = B.COLLECTUNITID(+)

AND A.MISSTYPEBIS = ‘1‘

3.2. 힌트 반영 안되는 경우

Join별 알고리즘 특성을 파악해 알맞은 join을 사용합니다.

Join 별 알고리즘 특성

INDEX JOIN

• NESTED LOOP의 index 버전입니다.
• JOIN 왼쪽에서 row가 많이 올라올수록 loop 횟수가 증가합니다.
• left row가 적게 올라올때 효과적입니다.

HASH JOIN

• 양쪽 row가 많이 올라올때 INDEX JOIN보다 효과적입니다.

​EXAMPLE

SELECT /*+ USE_HASH(A B) */… FROM – USE_HASH 힌트 반영이 안됩니다.

(SELECT BUSROUTEID, POINT_ID, MIN(ROUTEUNITSEQ) SEQ1, MAX(ROUTEUNITSEQ) SEQ3

FROM (…

UNION ALL

SELECT BUSROUTEID, ROUTEUNITSEQ+1 ROUTEUNITSEQ, ENDPOINTID POINT_ID

FROM BUSROUTE_INFOUNIT A,

BUS_INFOUNIT B

WHERE A.INFOUNITID = B.INFOUNITID)

GROUP BY BUSROUTEID, POINT_ID) A,

(SELECT BUSROUTEID, ROUTEUNITSEQ+1 ROUTEUNITSEQ, POINT_ID

FROM (…

UNION ALL

SELECT BUSROUTEID, ROUTEUNITSEQ, ENDPOINTID POINT_ID

FROM BUSROUTE_INFOUNIT A,

BUS_INFOUNIT B

WHERE A.INFOUNITID = B.INFOUNITID)) B

WHERE A.BUSROUTEID = B.BUSROUTEID(+)

AND A.POINT_ID = B.POINT_ID(+)

AND A.SEQ1 < B.ROUTEUNITSEQ(+)

AND A.SEQ3 > B.ROUTEUNITSEQ(+)

위 예제는 적용한 힌트가 반영되지 않고 Index Join으로 처리되고 있습니다.
이때 ‘Cardinality 힌트’를 사용해 Hash join을 유도합니다.

SELECT … FROM

(SELECT BUSROUTEID, POINT_ID, MIN(ROUTEUNITSEQ) SEQ1, MAX(ROUTEUNITSEQ) SEQ3

FROM (…

UNION ALL

SELECT /*+ CARD(A B 10000000) */ ​BUSROUTEID, ROUTEUNITSEQ+1 ROUTEUNITSEQ, ENDPOINTID POINT_ID

FROM BUSROUTE_INFOUNIT A,

BUS_INFOUNIT B

WHERE A.INFOUNITID = B.INFOUNITID)

GROUP BY BUSROUTEID, POINT_ID) A,

(SELECT BUSROUTEID, ROUTEUNITSEQ+1 ROUTEUNITSEQ, POINT_ID

FROM (…

UNION ALL

SELECT /*+ CARD(A B 10000000) */ ​BUSROUTEID, ROUTEUNITSEQ, ENDPOINTID POINT_ID

FROM BUSROUTE_INFOUNIT A,

BUS_INFOUNIT B

WHERE A.INFOUNITID = B.INFOUNITID)) B

WHERE A.BUSROUTEID = B.BUSROUTEID(+)

AND A.POINT_ID = B.POINT_ID(+)

AND A.SEQ1 < B.ROUTEUNITSEQ(+)

AND A.SEQ3 > B.ROUTEUNITSEQ(+)

SELECT …

FROM COLLECTUNIT_MISSING A, COLLECTUNIT_PREDICTION B

WHERE A.ANALYSISDATE = ? AND B.ANALYSISDATE(+) = ? AND B.COLLECTUNITTYPE(+) = ?

AND A.MISSINGCNT > 5

AND A.BUSROUTEID = B.BUSROUTEID

AND A.COLLECTUNITID = B.COLLECTUNITID

AND A.MISSTYPEBIS = ‘1‘

​

2072  hash join (reverse left outer) (et=66499, cr=0, cu=0, co=42873, cpu=4, ro=23782233)

29119  partition iterator(ALL PART) (PS:1, PE:25) (et=72, cr=0, cu=0, co=4749, cpu=0, ro=16409)

29119  table access (rowid) COLLECTUNIT_PREDICTION(8465) (et=8583873463831278, cr=296, cu=0, co=4749, cpu=0, ro=16409)

29119  filter (et=3586, cr=0, cu=0, co=302, cpu=0, ro=16409)

33697  index (range scan) COLLECTUNIT_PREDICTION_PK​(8491) (et=3814, cr=354, cu=0, co=301, cpu=0, ro=32818)

2072​  partition iterator(ALL PART) (PS:1, PE:22) (et=132, cr=0, cu=0, co=37181, cpu=2, ro=1366144)

2072  table access (full) COLLECTUNIT_MISSING​(3292) (et=8583873470045409, cr=87152, cu=230, co=37181, cpu=2, ro=1366144)

4. 통계 분포도

히스토그램 최대, 최소값 사이가 아닌 값이 조건문인 경우 입니다.

EXAMPLE

SELECT DISTINCT BUSROUTEID RID, COLLECTUNITID CID, COLLECTUNITLENGTH CLEN, COLLECTUNITTYPE CTYPE

FROM COLLECTUNIT_PREDICTION a

WHERE ANALYSISDATE = TO_DATE(‘2013/01/25 05:00:00′,’YYYY/MM/DD HH24:MI:SS’)

AND COLLECTUNITTYPE = ‘U‘

​

 646  partition iterator(ALL PART) (PS:1, PE:56) (et=68, cr=0, cu=0, co=4, cpu=0, ro=1)

 646  filter (et=2236, cr=0, cu=0, co=4, cpu=0, ro=1)

29765  index (range scan) COLLECTUNIT_PREDICTION_PK(8491) (et=6985, cr=278, cu=0, co=4, cpu=0, ro=1)

위 예제는 최대값보다 큰 영역이기에 1개를 예측했으나 실제는 많은 수가 존재합니다.
아래 방법을 참고해 해결합니다.

Optimizer의 영역 외 값 계산 기능
히스토그램에는 (1~100) 사이 값만 있어도 101에도 존재할 수 있음을 가정합니다.

_OPT_BOUND_SELEC_ADJUST_DEGREE
영역 바깥의 값에 대한 고려를 위한 기울기값으로 기본값은 0 입니다.
문제 발생시 100으로 설정을 변경합니다.

☞ “[Tibero] SQL Tuning (14)” 에서 계속됩니다.​