인덱스 스캔 효율성
인덱스 스캔 효율이 좋은지 나쁜지는 어떻게 알 수 있을까? 앞에서는 실행계획을 통해 알아보았다 이때는 단순 cost로 알아보았는데 이번에는 트레이서와 tkprof를 통해서 한번 알아보겠습니다 데이터는 이전 데이터 EX11_EMPLOYEE , EX12_EMPLOYEE를 활용하겠습니다
쿼리
1
2
3
4
5
6
7
8
SELECT *
FROM EX11_EMPLOYEE
WHERE employee_dept_no = 10
SELECT /*+ INDEX(EX12_EMPLOYEE Ex12_Employee_idx)*/ *
FROM EX12_EMPLOYEE WHERE employee_dept_no = 10
쿼리는 다음 2개를 준비했습니다 지난 시간의 실행계획에서 EX12_EMPLOYEE는 인덱스보다 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
SELECT *
FROM EX11_EMPLOYEE
WHERE employee_dept_no = 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 14659 0.68 1.01 0 15382 0 219861
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14661 0.68 1.01 0 15384 0 219861
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
---------- ---------- ---------- ---------------------------------------------------
219861 219861 219861 TABLE ACCESS CLUSTER EX11_EMPLOYEE (cr=15382 pr=0 pw=0 time=503287 us starts=1 cost=723 size=4617081 card=219861)
1 1 1 INDEX UNIQUE SCAN EX11_EMPLOYEE_CLUSTER_DEPT_NO_IDX (cr=1 pr=0 pw=0 time=14 us starts=1 cost=0 size=0 card=1)(object id 77385)
SELECT /*+ INDEX(EX12_EMPLOYEE Ex12_Employee_idx)*/ *
FROM
EX12_EMPLOYEE WHERE employee_dept_no = 10
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 13344 0.95 1.47 387 30377 0 200142
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13346 0.95 1.47 387 30377 0 200142
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
---------- ---------- ---------- ---------------------------------------------------
200142 200142 200142 TABLE ACCESS BY INDEX ROWID BATCHED EX12_EMPLOYEE (cr=30377 pr=387 pw=0 time=16281589 us starts=1 cost=3949 size=4200000 card=200000)
200142 200142 200142 INDEX RANGE SCAN EX12_EMPLOYEE_IDX (cr=13710 pr=387 pw=0 time=15799666 us starts=1 cost=394 size=0 card=200000)(object id 77450)
트레이스 파일 생성과 관련해서는 https://time-kimdongy1000.github.io/posts/SQL_%ED%8A%9C%EB%8B%9D-04/ 에서 tkprof 참조하시면 됩니다
EX11_EMPLOYEE_CLUSTER_DEPT_NO_IDX
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 14659 0.68 1.01 0 15382 0 219861
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14661 0.68 1.01 0 15384 0 219861
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
---------- ---------- ---------- ---------------------------------------------------
219861 219861 219861 TABLE ACCESS CLUSTER EX11_EMPLOYEE (cr=15382 pr=0 pw=0 time=503287 us starts=1 cost=723 size=4617081 card=219861)
1 1 1 INDEX UNIQUE SCAN EX11_EMPLOYEE_CLUSTER_DEPT_NO_IDX (cr=1 pr=0 pw=0 time=14 us starts=1 cost=0 size=0 card=1)(object id 77385)
이 테이블은 우리가 앞에서 클러스터 테이블 설정을 해두었다 그래서 실행계획도 INDEX UNIQUE SCAN으로 만들어지고 이때 cr는 메모리에서 읽은 값을 의미한다 1이 나왔다는 것은 인덱스 테이블 1건을 읽었다는 의미가 되는 것이고 클러스터 테이블은 전부 유니크한 데이터로만 이루어지기 때문에 PK 겸 인덱스인 employee_dept_no = 10 인 값은 1건 존재한다
그리고 여기서 나온 ROWID를 가지고 본 테이블을 조회하게 되는데 (cr=30377 pr=387 pw=0 time=16281589 us starts=1 cost=3949 size=4200000 card=200000) cr 이 30377 즉 메모리에서 가져온 30377개의 블록과 거기서 다 읽지 못해서 하드에 가져온 pr=387 블록의 데이터를 가져온다
Ex12_Employee_idx
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 13344 0.95 1.47 387 30377 0 200142
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13346 0.95 1.47 387 30377 0 200142
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
---------- ---------- ---------- ---------------------------------------------------
200142 200142 200142 TABLE ACCESS BY INDEX ROWID BATCHED EX12_EMPLOYEE (cr=30377 pr=387 pw=0 time=16281589 us starts=1 cost=3949 size=4200000 card=200000)
200142 200142 200142 INDEX RANGE SCAN EX12_EMPLOYEE_IDX (cr=13710 pr=387 pw=0 time=15799666 us starts=1 cost=394 size=0 card=200000)(object id 77450)
이 테이블은 일반 테이블로 인덱스를 걸지 않을 때 효율이 더 좋았는지 비교를 위해서 힌트에 인덱스를 달아두었습니다 그래서 인덱스 테이블에서 총 13710 개의 메모리 블록과 디스크의 387 개의 블록을 읽었고 이를 바탕으로 본 테이블 메모리 30377 블록 개수 그리고 디스크의 387 블록의 개수만큼 읽게 되었습니다 위의 클러스터 테이블과 비교 시 코스트 차이가 많이 나는 것을 볼 수 있으며 랜덤 I/O 가 얼마나 많이 발생했는지 알 수 있는 부분이다
그리고 결정적인 것은 time 값을 비교하면 클러스터 테이블의 최종 시간은 503287us(마이크로초) 일반 인덱스 테이블은 16281589us(마이크로초) 약 30배 정도 차이 난다 마이크로초는 1초의 백만 분의 1이므로 각각 0.503287초, 16.281589초 이렇게 비교될 수 있습니다
액세스 조건과 필터조건
인덱스를 스캔하는 단계에 처리하는 조건절은 액세스 조건과 필터 조건으로 나뉜다 인덱스 액세스 조건은 인덱스 스캔 범위를 결정하는 조건절이다 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 조건절이다 인덱스 필터 조건은 테이블로 액세스할지를 결정하는 조건절이다 그럼 앞의 실행계획 일부분을 보면서 한번 찾아보자
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
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 2269012019 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 24 | 6 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX9_EMPLOYEE | 1 | 24 | 6 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX9_EMPLOYEE_IDX1 | 200 | | 1 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("EMPLOYEE_SAL">3000 AND "EMPLOYEE_JOB"='MANAGER') |
2 - access("EMPLOYEE_DEPT"=30) |
LAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------------------------------+
lan hash value: 1057096137 |
|
--------------------------------------------------------------------------------------------------------|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT | | 1 | 24 | 6 (0)| 00:00:01 ||
* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX9_EMPLOYEE | 1 | 24 | 6 (0)| 00:00:01 ||
* 2 | INDEX RANGE SCAN | EX9_EMPLOYEE_IDX2 | 50 | | 1 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------------------|
|
redicate Information (identified by operation id): |
-------------------------------------------------- |
|
1 - filter("EMPLOYEE_SAL">3000) |
2 - access("EMPLOYEE_DEPT"=30 AND "EMPLOYEE_JOB"='MANAGER') |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 2202526983 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX9_EMPLOYEE | 1 | 24 | 2 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX9_EMPLOYEE_IDX3 | 1 | | 1 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("EMPLOYEE_DEPT"=30 AND "EMPLOYEE_JOB"='MANAGER' AND "EMPLOYEE_SAL">3000 AND |
"EMPLOYEE_SAL" IS NOT NULL) |
총 3개의 쿼리인데 인덱스가 각각 다른 것으로 힌트를 준 것이다 이 예제를 보고 액세스 조건과 필터 조건에 대해서 찾아보도록 하자
EX9_EMPLOYEE_IDX1
EX9_EMPLOYEE_IDX1의 인덱스는 다음과 같이 선언되어 있다 EMPLOYEE_DEPT 즉 이때 인덱스 테이블의 액세스 조건 어디서 시작하고 멈출지에 대한 정보는 EMPLOYEE_DEPT 컬럼의 값으로 결정이 되고 나머지 조건 EMPLOYEE_JOB , EMPLOYEE_SAL 둘은 필터 조건으로 인덱스로 서칭한 ROWID를 통해서 본 테이블로 접근했을 때 그것을 결과 셋에 포함할지 안 할지 결정하는 filter 조건인 것이다
EX9_EMPLOYEE_IDX2
EX9_EMPLOYEE_IDX2의 인덱스는 다음과 같이 선언되어 있다 EMPLOYEE_DEPT EMPLOYEE_JOB 이때 인덱스 테이블의 액세스 조건 어디서 시작하고 멈출지에 대한 정보는 EMPLOYEE_DEPT , EMPLOYEE_JOB으로 결정이 되기 때문에 이 두 컬럼은 인덱스 액세스 조건이고 이것으로 찾은 ROWID로 본 테이블에 접근해서 해당 row를 결과 셋에 포함을 할지 안 할지 결정하는 filter 조건은 EMPLOYEE_SAL 이 되는 것이다
EX9_EMPLOYEE_IDX3
EX9_EMPLOYEE_IDX3은 3개의 컬럼 EMPLOYEE_DEPT , EMPLOYEE_JOB , EMPLOYEE_SAL 이 인덱스에 포함되어 있는 것으로 인덱스 액세스 만으로 데이터를 찾아낸 케이스이다 이곳에서는 인덱스 액세스만 발생하고 필터는 발생하지 않고 액세스의 데이터 그대로가 결과 셋에 포함된다