Oracle Tuning 강좌
튜닝 전
아래 SQL문장은 OLTP환경에서 부분범위를 처리하기 위한 예제이다.
Trace를 확인한 결과 901건이 추출되었으며. 이 중 화면상에 설계된 15건만 부분범위 처리하여 보여주고자 한다.
SELECT D.DNAME,
EMP_V.DEPTNO,
EMP_V.HIREDATE,
EMP_V.MIN_JOB
FROM (SELECT E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
MIN(E.JOB) MIN_JOB
FROM EMP E
GROUP BY E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V,
DEPT D
WHERE EMP_V.DEPTNO = D.DEPTNO
AND D.DNAME = :B1;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.004 0 0 0 0
Fetch 92 43.910 45.473 6989 44244 0 901
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 94 43.910 45.477 6989 44244 0 901
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
901 HASH JOIN (cr=44244 pr=6989 pw=0 time=44110198 us)
1 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=126 us)
1000007 VIEW (cr=44241 pr=6989 pw=0 time=46107766 us)
1000007 HASH GROUP BY (cr=44241 pr=6989 pw=0 time=45107756 us)
10000000 TABLE ACCESS FULL EMP (cr=44241 pr=6989 pw=0 time=10000130 us)
1차 개선 - ROWNUM 사용
1차로 위 SQL문에서 최종 SQL에 ROWNUM을 넣은 뒤 인라인 뷰로 만든 다음 밖에서 화면 설계된 로우수 만큼 추출하는 조건을 주었다. 하지만 이 조건만으로는 부분범위 처리가 되지 않는다.
부분범위 처리 조건은 실행계획에서 모든 조인은 NL 조인이 되어야 하며, 만약 HASH JOIN이 하나라도 있으면 부분범위처리가 안 된다.
SELECT *
FROM (SELECT D.DNAME,
EMP_V.DEPTNO,
EMP_V.HIREDATE,
EMP_V.MIN_JOB,
ROWNUM CNT
FROM (SELECT E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
MIN(E.JOB) MIN_JOB
FROM EMP E
GROUP BY E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V,
DEPT D
WHERE EMP_V.DEPTNO = D.DEPTNO
AND D.DNAME = :B1)
WHERE CNT <= 15;
-- Trace 내용
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.004 0 0 0 0
Fetch 3 43.170 42.211 0 44244 0 15
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 43.170 42.218 0 44244 0 15
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
15 VIEW (cr=44244 pr=0 pw=0 time=40881287 us) -- ROWNUM 적용
901 COUNT (cr=44244 pr=0 pw=0 time=40883966 us)
-- HASH JOIN이 적용되어 부분범위 처리를 하지 못하고 EMP 테이블을 모두 액세스함
901 HASH JOIN (cr=44244 pr=0 pw=0 time=40883056 us)
1 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=131 us)
1000007 VIEW (cr=44241 pr=0 pw=0 time=41880601 us)
1000007 HASH GROUP BY (cr=44241 pr=0 pw=0 time=41880596 us)
10000000 TABLE ACCESS FULL EMP (cr=44241 pr=0 pw=0 time=114 us)
-- XPLAN 내용
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 706K| 40M| | 40769 (4)| 00:08:10 |
|* 1 | VIEW | | 706K| 40M| | 40769 (4)| 00:08:10 |
| 2 | COUNT | | | | | | |
|* 3 | HASH JOIN | | 706K| 15M| | 40769 (4)| 00:08:10 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 | 11 | | 3 (0)| 00:00:01 |
| 5 | VIEW | | 3534K| 40M| | 40731 (4)| 00:08:09 |
| 6 | HASH GROUP BY | | 3534K| 43M| 311M| 40731 (4)| 00:08:09 |
| 7 | TABLE ACCESS FULL| EMP | 10M| 124M| | 12240 (3)| 00:02:27 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CNT"<=15)
3 - access("EMP_V"."DEPTNO"="D"."DEPTNO")
4 - filter("D"."DNAME"=:B1)
-- 위의 내용을 보면 HASH JOIN 부분인 Id 3번에 조인키인 3 - access("EMP_V"."DEPTNO"="D"."DEPTNO")
-- 내용이 있는것을 확인할 수 있으며, 이를 통해 조인키가 EMP_V 인라인 뷰 안으로 Merge가
-- 되지 못하였다는 것을 알 수 있으므로 가급적 NL 조인이 되도록 만들어야 한다.
2차 개선 - NL 조인 유도하기 위해 힌트 사용
1차 개선에서 HASH JOIN 때문에 조건이 EMP_V 인라인 뷰 안으로 Merge가 되지 않았으므로 USE_NL 힌트를 사용해 NL 조인이 되도록 유도한다.
SELECT * FROM (SELECT /*+ LEADING(D) USE_NL(D EMP_V) PUSH_PRED(EMP_V) INDEX(EMP_V.E EMP_N1) */ D.DNAME, EMP_V.DEPTNO, EMP_V.HIREDATE, EMP_V.MIN_JOB, ROWNUM CNT FROM (SELECT E.DEPTNO, TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE, MIN(E.JOB) MIN_JOB FROM EMP E GROUP BY E.DEPTNO, TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V, DEPT D WHERE EMP_V.DEPTNO = D.DEPTNO AND D.DNAME = :B1 ) WHERE CNT <= 15; -- Trace 내용 Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 1 0.000 0.002 0 0 0 0 Execute 1 0.000 0.004 0 0 0 0 Fetch 3 32.600 31.880 0 44245 0 15 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 5 32.600 31.886 0 44245 0 15 Misses in library cache during parse: 1 Optimizer goal: ALL_ROWS Parsing user: APPS (ID=44) Rows Row Source Operation ------- --------------------------------------------------- 0 STATEMENT 15 VIEW (cr=44245 pr=0 pw=0 time=30636573 us) 901 COUNT (cr=44245 pr=0 pw=0 time=30639256 us) 901 NESTED LOOPS (cr=44245 pr=0 pw=0 time=30638349 us) 1 TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=204 us) 901 VIEW (cr=44241 pr=0 pw=0 time=30637328 us) 1000007 SORT GROUP BY (cr=44241 pr=0 pw=0 time=31636297 us) 10000000 TABLE ACCESS FULL EMP (cr=44241 pr=0 pw=0 time=113 us) -- 위에서 NESTED LOOPS이 되었음에도 불구하고 EMP_V 인라인 뷰 안의 EMP 테이블을 -- 여전히 TABLE ACCESS FULL을 하고 있으며, 부분범위처리 또한 되지 않았다. -- 그 이유를 확인하기 위해 아래에서 XPLAN의 Predicate Information 내용을 살펴보자. -- XPLAN 내용 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 706K| 40M| | 40734 (4)| 00:08:09 | |* 1 | VIEW | | 706K| 40M| | 40734 (4)| 00:08:09 | | 2 | COUNT | | | | | | | | 3 | NESTED LOOPS | | 706K| 15M| | 40734 (4)| 00:08:09 | |* 4 | TABLE ACCESS FULL | DEPT | 1 | 11 | | 3 (0)| 00:00:01 | |* 5 | VIEW | | 706K| 8284K| | 40731 (4)| 00:08:09 | | 6 | SORT GROUP BY | | 3534K| 43M| 311M| 40731 (4)| 00:08:09 | | 7 | TABLE ACCESS FULL| EMP | 10M| 124M| | 12240 (3)| 00:02:27 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CNT"<=15) 4 - filter("D"."DNAME"=:B1) 5 - filter("EMP_V"."DEPTNO"="D"."DEPTNO") -- Id 5번을 보면 HASH에서 NL로 변경되었음에도 불구하고 5 - filter("EMP_V"."DEPTNO"="D"."DEPTNO") -- 내용처럼 조인키가 Merge가 되지 않았다. 왜 NL 조인으로 변경하였음에도 불구하고 조인키가 -- Merge가 되지 않았을까? 그 이유는 바로 EMP_V 인라인뷰에 그룹함수가 있기 때문이다. -- 일반적으로 View Merging이 되기 위해서는 아래의 제약조건이 없어야 하는데 이를 일부 살펴보면 -- 다음과 같다. ① 집합 연산자를 사용할 경우(union, intersect, minus) ② connect by 절을 사용할 경우 ③ rownum을 사용할 경우 ④ 그룹함수를 사용할 경우(avg, count, max, min, sum) ⑤ 분석함수를 사용할 경우(row_number, rank, ...) -- 이 중 여기서의 문제는 4번째 Case인 그룹함수를 EMP_V에서 사용했기 때문에 Merge가 일어나지 못하고 -- 있는데, 일반적으로 위의 구문을 사용할경우 옵티마이저는 가급적 해당 집합을 먼저 구성한 다음 -- 조인을 해야 데이터 정합성을 해치지 않기 때문에 View Merging을 가급적 시도하지 않는다. -- 하지만 이런 그룹함수들도 분석함수를 사용할 경우 Complex View Merging을 통해 옵티마이저가 -- Merging을 시키도록 할 수 있다.
3차 개선 - 그룹함수를 분석함수를 사용하여 변경
여기서는 그룹함수의 MIN을 분석함수의 ROW_NUMBER를 통해 변경하는 방법을 살펴보려고 한다. 변경내용에만 집중하기 위해 EMP_V 인라인 뷰 내용만 가지고 설명을 하고자 한다.
-- 1. EMP_V 인라인 뷰 그룹함수 버전
SELECT E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
MIN(E.JOB) MIN_JOB
FROM EMP E
GROUP BY E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD');
-- 위의 내용을 보면 DEPTNO, HIREDATE 2개의 컬럼을 Grouping 지은 후,
-- 그 중 JOB 컬럼 값 중 최소값(MIN)을 가져오는 내용이다.
-- 이를 분석함수로 대체할 경우 어떻게 하면 될까? 바로 ROW_NUMBER() 함수를
-- 사용하여 JOB 컬럼값을 Asc로 ORDER BY 시킨 후 첫 번째 값만 가져오도록 변경해주면 된다.
-- 2. EMP_V 인라인 뷰 분석함수 버전
SELECT INLINE_EMP.DEPTNO,
INLINE_EMP.HIREDATE,
INLINE_EMP.MIN_JOB
FROM (SELECT E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
E.JOB MIN_JOB,
ROW_NUMBER() OVER(PARTITION BY E.DEPTNO, TO_CHAR(E.HIREDATE, 'YYYYMMDD') ORDER BY E.JOB) GUBN
FROM EMP E) INLINE_EMP
WHERE INLINE_EMP.GUBN = 1;
-- 즉, 위의 내용처럼 PARTITION BY를 통해 2개의 컬럼을 Grouping 시킨 후 ORDER BY 한 결과를
-- 밖에서 1 값만 취득하면 그룹함수의 MIN()과 동일한 효과를 나타낼 수 있으며,
-- 이를 통해 EMP_V 인라인 뷰 안으로 조인키인 DEPTNO 값이 Merge 되도록 할 수 있다.
4차 개선 - EMP_V 인라인 뷰의 그룹함수를 분석함수로 대체
이제 위에서 EMP_V 인라인 뷰의 그룹함수를 분석함수로 대체하여 다시 테스트를 해보자.
SELECT *
FROM (SELECT /*+ LEADING(D)
USE_NL(D EMP_V)
PUSH_PRED(EMP_V)
INDEX(EMP_V.INLINE_EMP.E EMP_N1) */
D.DNAME,
EMP_V.DEPTNO,
EMP_V.HIREDATE,
EMP_V.MIN_JOB
FROM (SELECT INLINE_EMP.DEPTNO,
INLINE_EMP.HIREDATE,
INLINE_EMP.MIN_JOB
FROM (SELECT E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
E.JOB MIN_JOB,
ROW_NUMBER() OVER(PARTITION BY E.DEPTNO,
TO_CHAR(E.HIREDATE, 'YYYYMMDD')
ORDER BY E.JOB) GUBN
FROM EMP E) INLINE_EMP
WHERE INLINE_EMP.GUBN = 1) EMP_V,
DEPT D
WHERE EMP_V.DEPTNO = D.DEPTNO
AND D.DNAME = :B1)
WHERE ROWNUM <= 15;
-- Trace 내용
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.010 0 0 0 0
Fetch 3 0.060 0.055 18 59 0 15
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.060 0.068 18 59 0 15
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
15 COUNT STOPKEY (cr=59 pr=18 pw=0 time=53933 us)
15 NESTED LOOPS (cr=59 pr=18 pw=0 time=53924 us)
1 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=85 us)
-- View Merging이 되어 PUSHED PREDICATE 실행계획이 나타남
15 VIEW PUSHED PREDICATE (cr=56 pr=18 pw=0 time=53834 us)
132 WINDOW SORT PUSHED RANK (cr=56 pr=18 pw=0 time=54082 us)
9000 TABLE ACCESS BY INDEX ROWID EMP (cr=56 pr=18 pw=0 time=18068 us)
9000 INDEX RANGE SCAN EMP_N1 (cr=21 pr=18 pw=0 time=9053 us)(Object ID 6720703)
-- 이제 다시 Trace 내용을 살펴보면 좀 전까지 View로 막혔던 내용이 'VIEW PUSHED PREDICATE'라는
-- 내용으로 변경되었으며, EMP_V 인라인 뷰 안의 EMP 테이블이 FULL SCAN이 아닌
-- INDEX RANGE SCAN으로 변경되었음을 알 수 있다.
-- 또한, 우리가 원하는 부분범위처리가 되어 EMP 테이블을 천만건 액세스하는 것이 아닌 9,000건만
-- 액세스하여 수행속도를 기존 45초에서 0.068초로 감소된것을 확인할 수 있다.
-- 우리가 원하는 실행계획을 통해 부분범위처리가 되었긴 하지만,
-- 실제 조건이 잘 들어갔는지 확인하기 위해 XPLAN을 다시 살펴보자.
-- XPLAN 내용
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 765 | | 4 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | NESTED LOOPS | | 1111K| 54M| | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPT | 1 | 14 | | 3 (0)| 00:00:01 |
|* 4 | VIEW PUSHED PREDICATE | | 3 | 111 | | 1 (0)| 00:00:01 |
|* 5 | WINDOW SORT PUSHED RANK | | 2001K| 24M| 91M| 26239 (2)| 00:05:15 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 2001K| 24M| | 16592 (1)| 00:03:20 |
|* 7 | INDEX RANGE SCAN | EMP_N1 | 2018K| | | 3986 (2)| 00:00:48 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=15)
3 - filter("D"."DNAME"=:B1)
4 - filter("INLINE_EMP"."GUBN"=1)
5 - filter(ROW_NUMBER() OVER ( PARTITION BY
"E"."DEPTNO",TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'YYYYMMDD')
ORDER BY "E"."JOB")<=1)
7 - access("E"."DEPTNO"="D"."DEPTNO") -- 조인 조건이 뷰 안으로 침투가 된 것이 확인됨
-- 위의 내용에서 보듯이 Id 4번에 조인키가 위치해 있지 않고 Id 7번에 조인키가 위치해
-- 있는것으로 보아 Merge가 잘 되었음을 확인할 수 있다.
-- 이처럼, 튜닝을 진행할 때는 우리가 원하는 방향을 설정한 후 분할&정복 방식으로 진행하는
-- 것이 좋으며, 실행계획 유도가 안 될 경우 그 이유가 무엇인지 XPLAN 정보를 통해 보정할
-- 경우 원하는 성능향상을 얻을 수 있을것이다.
- 강좌 URL : http://www.gurubee.net/lecture/2135
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
by Ejql
[2010.07.16 09:39:59]
이런 실전적인 부분이 조금 필요했습니다. 감사합니다.
by 강정식
[2010.07.19 15:54:33]
도움이 되셨다니 다행입니다.
감사합니다.
감사합니다.
by 진진
[2010.07.28 18:11:26]
늘상 닥치는 문제인데...이렇게 푸는거였군요
감사합니다
감사합니다
by 손님
[2012.10.28 16:59:50]
안녕하세요..
실행계획을 위의 << -- XPLAN 내용 >> 포맷으로 보려면 어떻게 해야하나요?
저는 위처럼 실행시간이 안나옵니다. ㅠ
감사합니다.
by 참된신자
[2014.08.13 15:51:41]
감사합니다 :)
) 버튼을 클릭하여 작성 하시면 됩니다.