본 문서에서는 상황에 따른 다양한 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으로 설정을 변경합니다.
