소스
https://gitlab.com/kimdongy1000/sqltuningproject
대량데이터 NL 조인 연습
이번 시간에는 대량 데이터를 넣고 한번 NL 조인 튜닝에 대해서 심층 분석을 해보자
간단 스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DROP TABLE Ex18_emp
CREATE TABLE Ex18_emp(
emp_code VARCHAR(100) PRIMARY KEY ,
emp_dept_cd VARCHAR(10),
join_date VARCHAR(8)
)
CREATE INDEX Ex18_emp_idx ON Ex18_emp(join_date)
CREATE TABLE Ex18_custom(
custom_code VARCHAR(100) PRIMARY KEY,
custom_sal_amt NUMBER,
sal_date VARCHAR(8),
emp_code VARCHAR(100)
)
CREATE INDEX ex18_custom_idx1 ON Ex18_custom(emp_code)
CREATE INDEX ex18_custom_idx2 ON Ex18_custom(custom_sal_amt)
트레이스 로그
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
* FROM Ex18_emp e INNER JOIN Ex18_custom c ON e.emp_cd = c.emp_code
WHERE e.join_date BETWEEN '19950101' AND '19992131'
AND e.emp_dept_cd >= 20
AND c.custom_sal_amt > 3000
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 52 0.01 0.01 0 335 0 758
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 54 0.01 0.01 0 335 0 758
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
758 758 758 NESTED LOOPS (cr=335 pr=0 pw=0 time=11442 us starts=1 cost=225 size=104832 card=728)
1550 1550 1550 NESTED LOOPS (cr=87 pr=0 pw=0 time=7821 us starts=1 cost=225 size=104832 card=1500)
3 3 3 TABLE ACCESS BY INDEX ROWID BATCHED EX18_EMP (cr=13 pr=0 pw=0 time=121 us starts=1 cost=6 size=153 card=3)
4 4 4 INDEX RANGE SCAN EX18_EMP_IDX (cr=10 pr=0 pw=0 time=73 us starts=1 cost=5 size=0 card=6)(object id 77964)
1550 1550 1550 INDEX RANGE SCAN EX18_CUSTOM_IDX1 (cr=74 pr=0 pw=0 time=6449 us starts=3 cost=5 size=0 card=500)(object id 77960)
758 758 758 TABLE ACCESS BY INDEX ROWID EX18_CUSTOM (cr=248 pr=0 pw=0 time=4563 us starts=1550 cost=73 size=22599 card=243)
먼저 트레이스 로그를 분석해보자
먼저 트레이스 로그를 분석해 보자
INDEX RANGE SCAN EX18_EMP_IDX으로 4건을 먼저 찾았다
4건으로 EX18_EMP 테이블 3건을 찾았다
이 3건으로 EX18_CUSTOM_IDX1 사용해서 1550 찾았고
3번의 1550 본 테이블 EX18_CUSTOM 방문해서 총 758 만큼 찾았다
50% 정도 나쁘지 않은 정도이다 그럼 다음을 보자 이건은 임의로 만든 트레이서 로그이다
Rows (1st) Rows (avg) Rows (max) Row Source Operation ———- ———- ———- ————————————————— 5 5 5 NESTED LOOPS
3 3 3 TABLE ACCESS BY INDEX ROWID BATCHED EX18_EMP 2780 2780 2780 INDEX RANGE SCAN EX18_EMP_IDX 5 5 5 INDEX RANGE SCAN EX18_CUSTOM_IDX1 6 6 6 TABLE ACCESS BY INDEX ROWID EX18_CUSTOM
1
2
예를 들어 이런 트레이서를 보자 EX18_EMP_IDX 스캔량은 2780인데 결국 본 테이블인 EX18_EMP 가서는 겨우 3건을 뽑아온다 이럴때에는 필터조건을 인덱스에 달아놓는것이 좋다 그렇게 해서 다음처럼 처리 되었다고 보자
Rows (1st) Rows (avg) Rows (max) Row Source Operation ———- ———- ———- ————————————————— 5 5 5 NESTED LOOPS
3 3 3 TABLE ACCESS BY INDEX ROWID BATCHED EX18_EMP 3 3 3 INDEX RANGE SCAN EX18_EMP_IDX (cr = 102 pr 31 …) 5 5 5 INDEX RANGE SCAN EX18_CUSTOM_IDX1 6 6 6 TABLE ACCESS BY INDEX ROWID EX18_CUSTOM
1
2
3
4
5
6
7
8
9
10
11
12
이렇게 처리되면 숫자만 보면 비효율적인 테이블 액세스는 없지만 스캔 단계에서 정말로 줄어들었는지 확인이 필요하다 예를 들어서 최종적으로 3row를 불러오지만 그중 읽은 블록은 총 133개입니다 한 블록에 통상적으로 500개 평균 레코드가 저장이 되어 있으니 고작 3건을 얻기 위해서 133 * 500 = 66000 개의 레코드를 읽게 된 것입니다 EX18_EMP_IDX 추가가 필요하지만 이 작업은 꽤나 난이도가 있는 작업이다 인덱스 컬럼 추가라던지 인덱스 자체 추가 같은 경우는 다른 쿼리의 영향도 분석은 필요하다 다음을 보자
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5 5 5 NESTED LOOPS
2780 2780 2780 TABLE ACCESS BY INDEX ROWID BATCHED EX18_EMP
2780 2780 2780 INDEX RANGE SCAN EX18_EMP_IDX
5 5 5 INDEX RANGE SCAN EX18_CUSTOM_IDX1
6 6 6 TABLE ACCESS BY INDEX ROWID EX18_CUSTOM
이런 트레이스 결과가 있다고 할 때 EX18_CUSTOM_IDX1 - EX18_EMP_IDX 문제가 있는가? 그렇지 않다 스캔 한 양만큼 본 테이블도 조회했기 때문에 효율성이 아주 좋을 뿐이다 아무래도 양이 많을 뿐이다 하지만 이제 다음 문제는 NL 조인이다 EX18_EMP_IDX 2780번 횟수로 EX18_CUSTOM_IDX1 테이블을 액세스한다 고작 5건을 얻기 위해서 이런 경우라면 조인 순서를 변경하는 방법을 먼저 구상을 해봐야 한다 하지만 그렇다고 해서 꼭 성공하는 방법은 아니니 해보고 트레이서를 떠보는 수밖에 없다 옵티마이저는 현재 있는 쿼리와 인덱스를 이용해서 최선을 다해서 제일 낮은 방법을 찾기 때문에 보통 상황이 아니라면 단독으로 옵티마이저를 이기기란 힘들다
우리는 간단하게 조인을 했을 때 어떤 방식으로 조인을 해야 하는지 알아보았다 물론 예시를 든 것이긴 하지만 이런 방법으로 간단한 조인 문구를 먼저 분석을 해보는 것을 추천한다