|
ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]
1. SQL 쿼리 속도 저하 요인
옵티마이져는 2개의 인덱스(IP, IDATE) 중 '='가 'LIKE' 보다 우선순위가 높아 61만건 Access
GROUP BY에 L2의 불필요 SORT
2. SQL 쿼리 속도 개선 사항
IDATE 'LIKE' 인덱스를 사용하도록 하여 40만건 Access
GROUP BY에 L2 삭제
3. SQL 쿼리
3.1 튜닝전 일부 쿼리
SELECT
'S_'||A.IP IP,
B.HOST,
FROM
(
SELECT
IP,
FROM
RPT_DAILY
WHERE
L1 = 'FW' AND
L2 = 'H_SUM' AND
IP = '10.100.3.254' AND
SUBSTR(IDATE, 1, 6) = SUBSTR(200801, 1, 6)
) A,
SERVER_LIST B
WHERE
A.IP = B.IP(+)
GROUP BY
A.IP, B.HOST, A.IDATE
3.2 튜닝후 일부 쿼리
SELECT
'S_'||A.IP IP,
B.HOST,
FROM
(
SELECT
IP,
FROM
RPT_DAILY
WHERE
L1 = 'FW' AND
L2 = 'H_SUM' AND
IP||’ ’ = '10.100.3.254‘||’ ’ AND
IDATA LIKE ‘200801’||’%’
) A,
SERVER_LIST B
WHERE
A.IP = B.IP(+)
GROUP BY
A.IP, B.HOST, A.IDATE
4. 실행 PLAN
4.1 튜닝전
Rows Execution Plan
------- ------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE
31 SORT (GROUP BY)
119 MERGE JOIN (OUTER)
119 SORT (JOIN)
119 VIEW
119 SORT (GROUP BY)
2284 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_DAILY'
612252 INDEX (RANGE SCAN) OF 'IDX_RPT_DAILY_IP' (NON- UNIQUE)
119 SORT (JOIN)
56 TABLE ACCESS (FULL) OF 'SERVER_LIST'
4.2 튜닝 후
Rows Execution Plan
------- ------------------------------------------ 0 SELECT STATEMENT GOAL: CHOOSE
31 SORT (GROUP BY)
119 MERGE JOIN (OUTER)
119 SORT (JOIN)
119 VIEW
119 SORT (GROUP BY)
2284 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_DAILY'
400607 INDEX (RANGE SCAN) OF 'IDX_RPT_DAILY_IDATE'
(NON-UNIQUE)
119 SORT (JOIN)
56 TABLE ACCESS (FULL) OF 'SERVER_LIST' |
