조인 시 사용된 결합인덱스의 문제
▶ Optimizer_mode = Rule Based Optimizer
▶ 인덱스 정보
▶ Before Tuning (Original Source)
▶ View 를 통한 변형 (VIEW MERGE 현상 발생)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A,
(SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY) B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.13 0.12 0 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.14 0.12 0 39172 0 109
위의 것과 비교해 보았을 때 별다른 차이가 없으며 view에 대한 것에 문제가 있다. 나타나지 않는다.
왜냐하면 view merge 때문에 그런 것이다.
▶ View 를 통한 변형 (VIEW MERGE 현상 발생 해소)
▶ WITH ~ AS 구문을 활용한 변경 (여기서도 VIEW MERGE가 일어남을 확인해야 한다.)
# VIEW MERGE가 되지 않는 경우
WITH B AS (SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY
ORDER BY DIM_TIME_KEY,DIM_COURSE_KEY)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A, B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (ORDER BY)
109 NESTED LOOPS
3232 VIEW
3232 SORT (GROUP BY)
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
109 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
3231 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
▶ WITH ~ AS 구문을 활용한 변경
# VIEW MERGE가 된 경우
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 NESTED LOOPS
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
19454 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
테이블 순서를 바꾸기...
FULL TABLE SCAN의 영향때문에 속도가 늦는지 알아보기 위해 인덱스 설정 후 진행한다.
인덱스 설정
hint 사용 (인덱스 전후 비교)
SELECT /*+ ORDERED USE_NL(A B) */
A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
(1) 인덱스 설정 전
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.25 0.24 6 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.25 6 3400 0 109
Suppressing 사용 (인덱스 사용 전후 비교)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY + 0 = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
view 사용 (인덱스 사용 전후 비교)
(1)인덱스 설정 전
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.26 0.24 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.24 0 3400 0 109
(1)인덱스 설정 전
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 VIEW
51 COUNT
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
full table scan에 따른 성능 저하는 문제가 아니다.....
설계상의 문제가 있는 것이다. 결합인덱스의 문제를 살펴보아야 한다.
생성한 인덱스 삭제하기
SQL> DROP INDEX EC_COURSE_CD_IDX01;
설계상의 이슈를 해결하기 위해 새로운 인덱스를 생성한다.
CREATE INDEX EC_USER_CNT_IDX
ON EC_USER_CNT(DIM_COURSE_KEY, DIM_TIME_KEY)
TABLESPACE TS_EC_IDX
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
UNRECOVERABLE;
▶ 인덱스 정보
제대로된 인덱스 생성 후 sql 성능 재 비교
hint 사용
SELECT /*+ ORDERED USE_NL(A B) */
A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_IDX' (NON-UNIQUE)
Suppressing 사용
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY + 0 = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_IDX' (NON-UNIQUE)
view 사용
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM (SELECT DIM_COURSE_KEY,
DIM_COURSE_NAME,
ROWNUM
FROM EC_COURSE_CD
WHERE DIM_BIG_KEY = '02') A,
EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 VIEW
51 COUNT
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_IDX' (NON-UNIQUE)
▶ Optimizer_mode = Rule Based Optimizer
▶ 인덱스 정보
EC_COURSE_CD : EC_COURSE_CD_PK : DIM_COURSE_KEY
EC_USER_CNT : EC_USER_CNT_PK : DIM_TIME_KEY + DIM_COURSE_KEY + DIM_GEN_KEY
EC_USER_CNT : EC_USER_CNT_PK : DIM_TIME_KEY + DIM_COURSE_KEY + DIM_GEN_KEY
▶ Before Tuning (Original Source)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.14 0.18 147 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.15 0.20 149 39174 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 NESTED LOOPS
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT' (B)
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD' (A)
19455 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
B - Index Range Scan
A - DIM_BIG_KEY가 인덱스가 존재하지 않기 때문에 Full Table Scan을 할 것이다.
---> 그래서 우선 순위 측면에서 B가 먼저 선택이 된 것이다.
---> 조인 순서는 바꿀수 없다면 조인 횟수라도 줄여보자..
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.14 0.18 147 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.15 0.20 149 39174 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 NESTED LOOPS
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT' (B)
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD' (A)
19455 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
B - Index Range Scan
A - DIM_BIG_KEY가 인덱스가 존재하지 않기 때문에 Full Table Scan을 할 것이다.
---> 그래서 우선 순위 측면에서 B가 먼저 선택이 된 것이다.
---> 조인 순서는 바꿀수 없다면 조인 횟수라도 줄여보자..
▶ View 를 통한 변형 (VIEW MERGE 현상 발생)
튜닝시 뷰의 사용 목적
1. 테이블 사이의 조인 회수 개선 (감소)
2. 조인 순서 제어
3. 함수 사용회수(빈도수)를 개선 (감소)
4. update 작업의 최적화
1. 테이블 사이의 조인 회수 개선 (감소)
2. 조인 순서 제어
3. 함수 사용회수(빈도수)를 개선 (감소)
4. update 작업의 최적화
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A,
(SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY) B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.13 0.12 0 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.14 0.12 0 39172 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 NESTED LOOPS
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
19455 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 NESTED LOOPS
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
19455 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
위의 것과 비교해 보았을 때 별다른 차이가 없으며 view에 대한 것에 문제가 있다. 나타나지 않는다.
왜냐하면 view merge 때문에 그런 것이다.
▶ View 를 통한 변형 (VIEW MERGE 현상 발생 해소)
-- 9i 부터 group by 가 변형 예외 케이스에서 사라져서 발생한 것이다.
-- 해결 방법 : group by 뒤 order by 추가 하고 컬럼은 동일한 것 넣어준다.
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A,
(SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY
ORDER BY DIM_TIME_KEY,DIM_COURSE_KEY) B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
-- 해결 방법 : group by 뒤 order by 추가 하고 컬럼은 동일한 것 넣어준다.
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A,
(SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY
ORDER BY DIM_TIME_KEY,DIM_COURSE_KEY) B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.06 0.05 0 6726 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.06 0.05 0 6726 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (ORDER BY)
109 NESTED LOOPS
3232 VIEW <-----------------------
3232 SORT (GROUP BY)
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
109 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
3231 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.06 0.05 0 6726 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.06 0.05 0 6726 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (ORDER BY)
109 NESTED LOOPS
3232 VIEW <-----------------------
3232 SORT (GROUP BY)
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
109 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
3231 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
▶ WITH ~ AS 구문을 활용한 변경 (여기서도 VIEW MERGE가 일어남을 확인해야 한다.)
# VIEW MERGE가 되지 않는 경우
WITH B AS (SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY
ORDER BY DIM_TIME_KEY,DIM_COURSE_KEY)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A, B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.06 0.05 0 6726 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.06 0.05 0 6726 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.06 0.05 0 6726 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.06 0.05 0 6726 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (ORDER BY)
109 NESTED LOOPS
3232 VIEW
3232 SORT (GROUP BY)
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
109 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
3231 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
▶ WITH ~ AS 구문을 활용한 변경
# VIEW MERGE가 된 경우
WITH B AS (SELECT DIM_TIME_KEY,DIM_COURSE_KEY,
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A, B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
SUM(USER_CNT) USER_CNT
FROM EC_USER_CNT
WHERE DIM_TIME_KEY LIKE '2001%'
GROUP BY DIM_TIME_KEY,DIM_COURSE_KEY)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
B.USER_CNT
FROM EC_COURSE_CD A, B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
ORDER BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.13 0.12 0 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.13 0.12 0 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.13 0.12 0 39172 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.13 0.12 0 39172 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 NESTED LOOPS
19455 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
19455 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
19454 INDEX (UNIQUE SCAN) OF 'EC_COURSE_CD_PK' (UNIQUE)
테이블 순서를 바꾸기...
FULL TABLE SCAN의 영향때문에 속도가 늦는지 알아보기 위해 인덱스 설정 후 진행한다.
인덱스 설정
SQL> CREATE INDEX EC_COURSE_CD_IDX01 ON EC_COURSE_CD(DIM_BIG_KEY)
2 TABLESPACE TS_EC_IDX STORAGE (INITIAL 1M NEXT 1M);
2 TABLESPACE TS_EC_IDX STORAGE (INITIAL 1M NEXT 1M);
hint 사용 (인덱스 전후 비교)
SELECT /*+ ORDERED USE_NL(A B) */
A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
(1) 인덱스 설정 전
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.25 0.24 6 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.25 6 3400 0 109
(2) 인덱스 설정 후
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.25 0.24 6 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.25 6 3400 0 109
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.25 0.24 6 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.25 6 3400 0 109
(1)인덱스 설정 전
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
(2) 인덱스 설정 후
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_COURSE_CD_IDX01' (NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_COURSE_CD_IDX01' (NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
Suppressing 사용 (인덱스 사용 전후 비교)
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY + 0 = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
(1)인덱스 설정 전
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.38 0.37 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.38 0.37 0 3400 0 109
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.38 0.37 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.38 0.37 0 3400 0 109
(2) 인덱스 설정 후
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.38 0.37 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.38 0.37 0 3400 0 109
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.38 0.37 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.38 0.37 0 3400 0 109
(1)인덱스 설정 전
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
(2) 인덱스 설정 후
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_COURSE_CD_IDX01' (NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
(2) 인덱스 설정 후
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_COURSE_CD_IDX01' (NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
view 사용 (인덱스 사용 전후 비교)
(1)인덱스 설정 전
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.26 0.24 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.24 0 3400 0 109
(2) 인덱스 설정 후
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.26 0.24 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.24 0 3400 0 109
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.26 0.24 0 3400 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.26 0.24 0 3400 0 109
(1)인덱스 설정 전
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 VIEW
51 COUNT
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
(2) 인덱스 설정 후
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 VIEW
51 COUNT
51 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_COURSE_CD_IDX01'
(NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 VIEW
51 COUNT
51 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_COURSE_CD_IDX01'
(NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'EC_USER_CNT_PK' (UNIQUE)
full table scan에 따른 성능 저하는 문제가 아니다.....
설계상의 문제가 있는 것이다. 결합인덱스의 문제를 살펴보아야 한다.
생성한 인덱스 삭제하기
SQL> DROP INDEX EC_COURSE_CD_IDX01;
Index dropped.
설계상의 이슈를 해결하기 위해 새로운 인덱스를 생성한다.
CREATE INDEX EC_USER_CNT_IDX
ON EC_USER_CNT(DIM_COURSE_KEY, DIM_TIME_KEY)
TABLESPACE TS_EC_IDX
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
UNRECOVERABLE;
▶ 인덱스 정보
EC_COURSE_CD : EC_COURSE_CD_PK : DIM_COURSE_KEY
(전) USER_CNT : EC_USER_CNT_PK : DIM_TIME_KEY + DIM_COURSE_KEY + DIM_GEN_KEY
(전) USER_CNT : EC_USER_CNT_PK : DIM_COURSE_KEY + DIM_TIME_KEY+ DIM_GEN_KEY
(전) USER_CNT : EC_USER_CNT_PK : DIM_TIME_KEY + DIM_COURSE_KEY + DIM_GEN_KEY
(전) USER_CNT : EC_USER_CNT_PK : DIM_COURSE_KEY + DIM_TIME_KEY+ DIM_GEN_KEY
제대로된 인덱스 생성 후 sql 성능 재 비교
hint 사용
SELECT /*+ ORDERED USE_NL(A B) */
A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.01 0.00 18 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.02 0.01 18 242 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.01 0.00 18 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.02 0.01 18 242 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_IDX' (NON-UNIQUE)
Suppressing 사용
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM EC_COURSE_CD A, EC_USER_CNT B
WHERE A.DIM_COURSE_KEY + 0 = B.DIM_COURSE_KEY
AND A.DIM_BIG_KEY = '02'
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 241 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_IDX' (NON-UNIQUE)
view 사용
SELECT A.DIM_COURSE_NAME,B.DIM_TIME_KEY,
SUM(B.USER_CNT)
FROM (SELECT DIM_COURSE_KEY,
DIM_COURSE_NAME,
ROWNUM
FROM EC_COURSE_CD
WHERE DIM_BIG_KEY = '02') A,
EC_USER_CNT B
WHERE A.DIM_COURSE_KEY = B.DIM_COURSE_KEY
AND B.DIM_TIME_KEY LIKE '2001%'
GROUP BY A.DIM_COURSE_NAME, B.DIM_TIME_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.01 0.00 0 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.01 0.00 0 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.01 0.00 0 241 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.01 0.00 0 241 0 109
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
109 SORT (GROUP BY)
952 TABLE ACCESS (BY INDEX ROWID) OF 'EC_USER_CNT'
1004 NESTED LOOPS
51 VIEW
51 COUNT
51 TABLE ACCESS (FULL) OF 'EC_COURSE_CD'
952 INDEX (RANGE SCAN) OF 'EC_USER_CNT_IDX' (NON-UNIQUE)
'04번. IT 힌트얻기 > ▶ DB Tuning' 카테고리의 다른 글
| [Tuning] SET AUTOTRACE TRACEONLY EXPLAIN (0) | 2011.10.12 |
|---|---|
| [Tuning] 서브쿼리를 통한 튜닝 (0) | 2011.10.12 |
| [Tuning] 인덱스에 의한 Sort 대체 (0) | 2011.10.11 |
| [Tuning] perfect_study_01 (0) | 2011.10.11 |
| [Tuning] Nested Loop Join (튜닝포인트) (0) | 2011.10.10 |