잠깐 복습을 해보자 앞의 일부분은 데이터베이스가 어떤 원리로 데이터를 가져오는지에 대해서 공부했다 특히 쿼리의 성능은 디스크 랜덤 I/O 가 얼마나 적게 발생하냐에 따라서 쿼리 성능의 척도가 된다고 배웠다 그리고 다양한 인덱스의 종류와 실행계획을 비교해 보면서 어떨 때 인덱스가 타고 어떨 때 인덱스가 타지 않는지에 대해서 공부했다
테이블 랜덤 액세스
튜닝을 공부하다 보면 2번 놀란다 첫 번째는 아무리 데이터가 많아도 인덱스를 사용하니 금방 조회된다는 사실에 놀라고 인덱스를 사용하니 오히려 테이블 전체를 스캔할 때보다도 성능이 나오지 않을 때 놀란다 우리는 이와 같은 경우를 비록 적은 데이터지만 모두 경험은 해보았다
인덱스 ROWID
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
EXPLAIN PLAN FOR
SELECT * FROM ex1_student
WHERE grade_id = 1
AND class_id > 1
AND student_id = 1
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------------------------+
Plan hash value: 1095574889 |
|
----------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 4 | 76 | 3 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX1_STUDENT | 4 | 76 | 3 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX1_STUDENT_PK_IDX | 4 | | 2 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("GRADE_ID"=1 AND "CLASS_ID">1 AND "STUDENT_ID"=1) |
filter("STUDENT_ID"=1) |
앞장에 했었던 실행계획을 불러왔다 이곳에서는 Index Range Scan 과 더불어서 TABLE ACCESS BY INDEX ROWID BATCHED 가 있는 모습을 볼 수 있다 이번엔 이 ROWID에 대해서 알아보자 옵티마이저가 인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 해당 인덱스가 가리키는 테이블 레코드를 찾아가기 위한 주솟값 즉 ROWID를 얻으려는 데 있다 이 ROWID는 테이블 레코드를 찾아가기 위한 논리적 주소 정보가 담겨있다 즉 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다
I/O 메커니즘
이는 앞장에서 한번 본 적이 있는데 옵티마이저가 데이터를 읽을 때 매번 디스크에서 읽을 수 없으니 버퍼캐시를 활용하는 것을 알고 있을 것이다 그래서 디스크로 가기 전에 먼저 버퍼캐시를 찾아서 이전에 사용되어 캐싱 된 데이터가 있는지 먼저 살펴본다 있으면 바로 그 데이터를 찾아오고 그렇지 않으면 디스크에서 찾아오는 것입니다 하지만 인덱스 ROWID는 생각보다 고비용 구조입니다 따라서 읽어야 할 데이터가 일정량을 넘는 순간 테이블 전체를 스캔하는 것보다 오히려 느려집니다 이는 Index Range Scan 과 Table Full Scan 사이에서 발생하는 손익분기점이라고 합니다
Table Full Scan 성능체감 소스
https://gitlab.com/kimdongy1000/sqltuningproject
Ex8_Student 참조하시면됩니다 소스코드를 보시면 총 1백만개를 넣는 조건이고
간단 스키마
1
2
3
4
5
6
7
8
9
10
drop table Ex8_Student
CREATE TABLE Ex8_Student(
student_id NUMBER,
student_name varchar(10)
)
CREATE INDEX Ex8_Student_idx ON Ex8_Student(student_id);
스키마와 인덱스는 간단하게 만들었습니다 먼저 선두 컬럼을 사용한 채로 index를 태웠을 때 실행계획을 비교하겠습니다 쿼리 없이 실행계획만 보겠습니다
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 691850560 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10 | 150 | 4 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX8_STUDENT | 10 | 150 | 4 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX8_STUDENT_IDX | 10 | | 3 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("STUDENT_ID"<10) |
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 691850560 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 100 | 1500 | 4 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX8_STUDENT | 100 | 1500 | 4 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX8_STUDENT_IDX | 100 | | 3 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("STUDENT_ID"<100) |
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 691850560 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1000 | 15000 | 7 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX8_STUDENT | 1000 | 15000 | 7 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX8_STUDENT_IDX | 1000 | | 4 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("STUDENT_ID"<1000) |
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 691850560 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10000 | 146K| 50 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX8_STUDENT | 10000 | 146K| 50 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX8_STUDENT_IDX | 10000 | | 22 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 691850560 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 100K| 1464K| 482 (1)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX8_STUDENT | 100K| 1464K| 482 (1)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX8_STUDENT_IDX | 100K| | 203 (1)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("STUDENT_ID"<100000) |
조회해야 하는 데이터가 늘어나면 늘어날수록 Index Range Scan 은 점점 cost 가 늘어가는 것을 볼 수 있습니다 반면에 테이블 fUll scan 을 보겠습니다
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 1816169908 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10 | 150 | 756 (1)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX8_STUDENT | 10 | 150 | 756 (1)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("STUDENT_ID"<10) |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 1816169908 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 100 | 1500 | 756 (1)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX8_STUDENT | 100 | 1500 | 756 (1)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("STUDENT_ID"<100) |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 1816169908 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1000 | 15000 | 756 (1)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX8_STUDENT | 1000 | 15000 | 756 (1)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("STUDENT_ID"<1000) |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 1816169908 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 100K| 1464K| 756 (1)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX8_STUDENT | 100K| 1464K| 756 (1)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
지금 보면 index range scan 은 스캔해야 할 범위가 늘어나면 늘어남에 따라 비용이 점점 증가하는 것을 볼 수 있습니다 그러는 이유는 소량의 데이터를 찾아올 때는 인덱스를 바탕으로 범위 스캔이 도움이 돼지만 그러한 범위가 점점 커지면 인덱스 테이블 자체를 스캔하는 것도 비용에 한 축에 속하게 됩니다 그렇기에 늘어나는 범위에 비례해서 cost 가 올라가는 것을 볼 수 있습니다
반면에 Table Full Scan 경우에는 처음부터 높은 cost를 자랑합니다 당연하게도 테이블을 인덱스 없이 전부 스캔하라는 힌트를 주었기 때문에 결과 셋을 만족하는 데이터를 다 찾았음에도 불구하고 테이블을 전체 스캔하는 비용 때문에 높게 나옵니다 하지만 Full Scan의 장점은 스캔 범위가 적은 비용과 많은 비용이 차이가 나지 않습니다 그렇기에 정말로 엄청난 대용량 같은 경우는 인덱스를 활용하는 방법보다는 Table Full Access Scan 이 유리한 것을 알 수 있습니다
그럼 왜 이런 현상이 일어나는가?
Table Full Scan 은 시퀀셜 액세스인 반면 인덱스 ROWID는 랜덤 액세스 방식입니다
Table Full Scan Multiblock I/O인 반명 인덱스 ROWID는 Single Block I/O입니다
시퀀셜 액세스
논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식입니다 블록들은 자신의 앞과 뒤를 가리키는 주솟값으로 연결이 되어 있는 논리적인 상태입니다 즉 어떤 하나의 블록을 가져올 때 그 앞과 뒤 블록에 무슨 데이터가 들어가 있는지 알기 때문에 가져올려는 범위가 크면 클수록 한 번의 스캔에 여러 블록이 같이 오게 됩니다 그렇기에 대용량 데이터를 조회 시 성능이 일정한 모습을 보여주게 됩니다
랜덤 액세스
랜덤 액세스 말 그대로 랜덤하게 접근하는 것을 말합니다 이는 논리적 물리적 순서를 따르지 않고 레코드 하나를 읽기 위한 한 블록씩 접근하는 방식을 말합니다 즉 ROWID의 해당하는 주소의 블록에 직접 찾아가서 데이터를 스캔 해오는 방식이기 때문에 고비용입니다 그래서 소량의 데이터를 불러올 때는 장점이지만 대용량의 데이터를 읽을 때에는 엄청난 단점을 가져오게 됩니다
논리적 정렬 vs 물리적 정렬
사실 인덱스를 정의하고 인덱스 테이블 데이터를 보면 정렬되어 있습니다 하지만 이는 논리적인 정렬입니다 실제 데이터가 보관되어 있는 하드디스크에서는 여기저기 흩어져 있습니다 그렇기 때문에 인덱스 테이블을 스캔해서 얻은 ROWID를 물리적인 위치로 가서 가져오는 방식은 너무나도 고비용입니다 심지어 ROWID를 활용한 스캔은 한 번에 하나만 블록을 스캔하는 Single Block 방식이기 때문에 물리적인 데이터들이 흩어져 있다면 인덱스를 활용한 방식은 손익분기점을 넘을 수 있습니다
인덱스 타는것이 항상좋은가?
개발을 하다 보면 인덱스 타는 게 끝이라고 생각할 수 있지만 오산이다 지금처럼 손익분기점이 넘어가는 순간부터는 인덱스도 엄청난 비용을 낳기 때문에 반드시 인덱스를 태울 필요도 없다 결국은 실행계획 여러 번을 떠보면서 인덱스가 유리한지 Table Full Scan 이 유리한지는 직접 판단해 보는 것이 중요하다