[Tibero] SQL Tuning (13)

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 횟수가 증가합니다.
• 왼쪽 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) 에서 계속됩니다.​

​