OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c Prefetch, Batch I/O, Table access by rowid batched 설명
테이블 Prefetch, 배치 I/O, 배치 I/O 테이블 엑세스 ROWID에 대해 설명함
테이블 Prefetch
한번에 여러개 Single Block I/O를 동시에 수행하는것(이로인해 디스크 I/O에 의한 대기 회수를 감소시킴)
인덱스를 경유해 테이블 레코드를 엑세스 하는 도중 디스크에서 캐시로 블록을 적재해야 하는 상황이 발생하는데
이때 다른 테이블 블록까지 미리 적재해두는 기능
클러스터링 팩터가 나쁠때(이때는 logical/physical I/O가 높음) 효과가 더 좋음
Prefetch 시 대기이벤트는 db file parallel read 로 관측됨
관련 파라미터
_table_lookup_prefetch_size : 40(기본값)
_table_lookup_prefetch_thresh : 2(기본값)
_multi_join_key_table_lookup : true(기본값)
관련 힌트
nlj_prefetch, no_nlj_prefetch
NL조인 배치 I/O
읽는 블록마다 건건이 I/O call을 발생시키는 비요율을 줄이기 위해 고안된 기능
인덱스를 이용해 테이블을 엑세스하다가 버퍼 캐시에서 블록을 찾지 못했을 때 일반적으로는 디스크 블록을 바로 읽음
하지만 배치 I/O 작동 시 테이블블록에 대한 디스크 I/O call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리함
11g까지는 nl조인 inner 쪽 테이블에 엑세스 할때만 이 기능이 작동함
관련 힌트
nlj_batching, no_nlj_batching
ROWID 배치 I/O (TABLE ACCESS BY INDEX ROWID BATCHED)
12c부터는 인덱스 rowid로 테이블을 엑세스하는 어떤 부분에서도 배치 I/O 기능이 동작 가능함
동작방식(https://hrjeong.tistory.com/201)
1. 리프 블록의 rowid로 버퍼캐시를 조회한 뒤(single block I/O) 이후 실패(miss)한 rowid를 저장
2. 실패한 rowid가 일정량 이상 모이면 블록 번호로 정렬하여 물리적 읽기를 실행함(multi block I/O)
2번 과정으로 인해 물리 읽기가 발생한 경우 인덱스 정렬순서와 무관하게 행이 반환될 수 있음
그렇기 때문에 order by 가 있을때 인덱스로 정렬이 생략 가능한 경우에도 sort order by 실행계획이 추가로 나옴
관련 힌트
batch_table_access_by_rowid, no_batch_table_access_by_rowid
실행계획 비교
샘플 테이블 생성 및 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
drop table t1 purge;
drop table t2 purge;
create table t1(c1 number, c2 number, c3 number);
create table t2(c1 number, c2 number, c3 number);
insert into t1 select object_id, namespace, data_object_id from dba_objects;
insert into t2 select object_id, namespace, data_object_id from dba_objects;
commit;
create index t1_ix01 on t1(c1,c2);
create index t2_ix01 on t2(c1);
|
0.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
27
28
29
30
31
32
33
34
35
|
SQL>
alter session set statistics_level = all;
select /*+ leading(t1) use_nl(t2) full(t2) opt_param('OPTIMIZER_FEATURES_ENABLE', '9.2.0') */ t1.*, t2.*
from t1, t2
where t2.c1 = t1.c2
and t1.c1 >= 25000
order by t1.c1, t1.c2;
select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1553 | 497 |00:00:00.37 | 51042 | 72 |
| 1 | NESTED LOOPS | | 1 | 204 | 15912 | 1553 | 497 |00:00:00.37 | 51042 | 72 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 204 | 7956 | 3 | 962 |00:00:00.01 | 48 | 22 |
|* 3 | INDEX RANGE SCAN | T1_IX01 | 1 | 37 | | 2 | 962 |00:00:00.01 | 12 | 6 |
|* 4 | TABLE ACCESS FULL | T2 | 962 | 1 | 39 | 8 | 497 |00:00:00.37 | 50994 | 50 |
---------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('9.2.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2"))
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
|
ID1 NESTED LOOPS 안에(밑에) T1, T2 가 모두 들어와 있음
Prefetch stat 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col name for a50
select name, value
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name like 'physical reads%';
NAME VALUE
-------------------------------------------------- ----------
physical reads 363
physical reads cache 355
physical reads direct 8
physical reads direct temporary tablespace 0
physical reads cache prefetch 57 <<<<<
physical reads prefetch warmup 14
|
outline 힌트에도 Prefetch가 안나와서 정상적이라면 physical reads cache prefetch 값이 안올라가야 정상인데
이 테스트는 19c에서 옵티마이저 힌트만 바꿔서 실행 한거라 이 값이 증가한것으로 보임
*참고로 아래 테스트시 세션 재접속 및 buffer cache flush 했음에도
여러번 테스트 했을때 가끔 Prefetch stat 값이 너무 낮거나 0으로 나옴(어딘가 cache에 prefetch한 블록을 저장하고 있어서 그런게 아닌가 싶음)
그래서 매 테스트 전 db를 재기동 하고 진행함
1. 테이블 Prefetch 시 나타나는 실행계획
|
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
|
SQL>
(재기동 및 세션 재접속 후 진행)
alter session set statistics_level = all;
select /*+ leading(t1) use_nl(t2) index(t2) opt_param('OPTIMIZER_FEATURES_ENABLE', '9.2.0') */ t1.*, t2.*
from t1, t2
where t2.c1 = t1.c2
and t1.c1 >= 25000
order by t1.c1, t1.c2;
select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | | 411 | 0 |00:00:00.01 | 0 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 39 | 2 | 497 |00:00:00.01 | 81 | 23 |
| 2 | NESTED LOOPS | | 1 | 204 | 15912 | 411 | 1460 |00:00:00.01 | 72 | 22 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 204 | 7956 | 3 | 962 |00:00:00.01 | 48 | 20 |
|* 4 | INDEX RANGE SCAN | T1_IX01 | 1 | 37 | | 2 | 962 |00:00:00.01 | 12 | 6 |
|* 5 | INDEX RANGE SCAN | T2_IX01 | 962 | 1 | | 1 | 497 |00:00:00.01 | 24 | 2 |
----------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('9.2.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2"))
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."C1"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_PREFETCH(@"SEL$1" "T2"@"SEL$1") <<<<<
END_OUTLINE_DATA
*/
|
outline data에 NLJ_PREFETCH 힌트가 사용되었다고 나옴(Prefetch가 동작했음을 알수 있음)
ID5에 T2_IX01 인덱스 실행계획이 있고 ID1에 T2 테이블 실행계획이 나옴(ID1인 T2 테이블 엑세스 실행계획이 ID2인 NESTED LOOPS 밖(위)에 있음)
Prefetch stat 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col name for a50
select name, value
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name like 'physical reads%';
NAME VALUE
-------------------------------------------------- ----------
physical reads 409
physical reads cache 401
physical reads direct 8
physical reads direct temporary tablespace 0
physical reads cache prefetch 12 <<<<<
physical reads prefetch warmup 12
|
Prefetch가 동작해 physical reads cache Prefetch의 stat 값이 12로 확인됨
2. NL조인 배치 I/O 시 나타나는 실행계획
|
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
|
SQL>
(재기동 및 세션 재접속 후 진행)
alter session set statistics_level = all;
select /*+ leading(t1) use_nl(t2) */ t1.*, t2.*
from t1, t2
where t2.c1 = t1.c2
and t1.c1 >= 25000
order by t1.c1, t1.c2;
select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1934 (100)| | 497 |00:00:00.01 | 81 | 5 |
| 1 | NESTED LOOPS | | 1 | 962 | 75036 | 1934 (1)| 00:00:01 | 497 |00:00:00.01 | 81 | 5 |
| 2 | NESTED LOOPS | | 1 | 962 | 75036 | 1934 (1)| 00:00:01 | 497 |00:00:00.01 | 72 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 962 | 37518 | 9 (0)| 00:00:01 | 962 |00:00:00.01 | 48 | 0 |
|* 4 | INDEX RANGE SCAN | T1_IX01 | 1 | 962 | | 4 (0)| 00:00:01 | 962 |00:00:00.01 | 12 | 0 |
|* 5 | INDEX RANGE SCAN | T2_IX01 | 962 | 1 | | 1 (0)| 00:00:01 | 497 |00:00:00.01 | 24 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 497 | 1 | 39 | 2 (0)| 00:00:01 | 497 |00:00:00.01 | 9 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2"))
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."C1"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1") <<<<<
END_OUTLINE_DATA
*/
|
outline data에 NLJ_BATCHING 힌트가 사용되었다고 나옴(배치 I/O가 동작했음을 알수 있음)
ID5에 T2_IX01 인덱스 실행계획이 있고 ID6에 T2 테이블 실행계획이 나옴(테이블 실행계획이 ID1,2인 NESTED LOOPS 안에 있음)
NESTED LOOPS도 ID1, ID2에 각각 1개씩 총 2개 나옴
그리고 T1_IX01 인덱스를 통해 정렬을 생략함
Prefetch stat 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col name for a50
select name, value
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name like 'physical reads%';
NAME VALUE
-------------------------------------------------- ----------
physical reads 406
physical reads cache 398
physical reads direct 8
physical reads direct temporary tablespace 0
physical reads cache prefetch 90 <<<<<
physical reads prefetch warmup 4
|
Prefetch가 동작해 stat 값이 90으로 확인됨
outline data에는 NLJ_PREFETCH가 없지만 실제로는 Prefetch가 동작함을 알수 있음
그리고 Prefetch만 동작했을때 보다 값이 3배 이상 더 많음
Prefetch만 동작했을 때와 배치 I/O가 같이 동작했을때 실행계획 비교
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
Prefetch only
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | | 411 | 0 |00:00:00.01 | 0 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 39 | 2 | 497 |00:00:00.01 | 81 | 23 |
| 2 | NESTED LOOPS | | 1 | 204 | 15912 | 411 | 1460 |00:00:00.01 | 72 | 22 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 204 | 7956 | 3 | 962 |00:00:00.01 | 48 | 20 |
|* 4 | INDEX RANGE SCAN | T1_IX01 | 1 | 37 | | 2 | 962 |00:00:00.01 | 12 | 6 |
|* 5 | INDEX RANGE SCAN | T2_IX01 | 962 | 1 | | 1 | 497 |00:00:00.01 | 24 | 2 |
----------------------------------------------------------------------------------------------------------------------------
Prefetch + Batch I/O
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------- ------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1934 | 497 |00:00:00.01 | 81 | 5 |
| 1 | NESTED LOOPS | | 1 | 962 | 75036 | 1934 | 497 |00:00:00.01 | 81 | 5 |
| 2 | NESTED LOOPS | | 1 | 962 | 75036 | 1934 | 497 |00:00:00.01 | 72 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 962 | 37518 | 9 | 962 |00:00:00.01 | 48 | 0 |
|* 4 | INDEX RANGE SCAN | T1_IX01 | 1 | 962 | | 4 | 962 |00:00:00.01 | 12 | 0 |
|* 5 | INDEX RANGE SCAN | T2_IX01 | 962 | 1 | | 1 | 497 |00:00:00.01 | 24 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 497 | 1 | 39 | 2 | 497 |00:00:00.01 | 9 | 0 |
----------------------------------------------------------------------------------------------------------------------------
|
배치 I/O가 동작했을때가 reads 값이 더 작음
3. 배치 I/O + 배치 테이블 rowid 엑세스(TABLE ACCESS BY INDEX ROWID BATHCED)방식 사용시 나타나는 실행계획
|
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
|
SQL>
(재기동 및 세션 재접속 후 진행)
alter session set statistics_level = all;
select /*+ leading(t1) use_nl(t2) batch_table_access_by_rowid(t1) */ t1.*, t2.*
from t1, t2
where t2.c1 = t1.c2
and t1.c1 >= 25000
order by t1.c1, t1.c2;
select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1934 (100)| | 497 |00:00:00.01 | 41 | 5 | | | |
| 1 | SORT ORDER BY | | 1 | 962 | 75036 | 1934 (1)| 00:00:01 | 497 |00:00:00.01 | 41 | 5 | 50176 | 50176 |45056 (0)|
| 2 | NESTED LOOPS | | 1 | 962 | 75036 | 1934 (1)| 00:00:01 | 497 |00:00:00.01 | 41 | 5 | | | |
| 3 | NESTED LOOPS | | 1 | 962 | 75036 | 1934 (1)| 00:00:01 | 497 |00:00:00.01 | 40 | 5 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 962 | 37518 | 9 (0)| 00:00:01 | 962 |00:00:00.01 | 32 | 0 | | | |
|* 5 | INDEX RANGE SCAN | T1_IX01 | 1 | 962 | | 4 (0)| 00:00:01 | 962 |00:00:00.01 | 4 | 0 | | | |
|* 6 | INDEX RANGE SCAN | T2_IX01 | 962 | 1 | | 1 (0)| 00:00:01 | 497 |00:00:00.01 | 8 | 5 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 497 | 1 | 39 | 2 (0)| 00:00:01 | 497 |00:00:00.01 | 1 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") <<<<<
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."C1"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1") <<<<<
END_OUTLINE_DATA
*/
|
outline data에 NLJ_BATCHING 힌트가 사용되었다고 나옴(배치 I/O가 동작했음을 알수 있음)
outline data에 BATCH_TABLE_ACCESS_BY_ROWID도 표시됨(힌트가 제대로 동작했음을 알수있음)
ID6에 T2_IX01 인덱스 실행계획이 있고 ID7에 T2 테이블 실행계획이 나옴(테이블 실행계획이 ID1,2인 NESTED LOOPS 안에 있음)
NESTED LOOPS도 ID1, ID2에 각각 1개씩 총 2개 나옴
이보다 중요한건 원래라면 T1_IX01 인덱스를 통해 정렬을 할수있지만, 배치 테이블 rowid 엑세스방식이 작동해 ID1에 SORT ORDER BY(정렬)이 동작되었다고 나옴
Prefetch stat 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col name for a50
select name, value
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'physical reads cache prefetch';
NAME VALUE
-------------------------------------------------- ----------
physical reads 596
physical reads cache 490
physical reads direct 106
physical reads direct temporary tablespace 98
physical reads cache prefetch 92 <<<<<
physical reads prefetch warmup 6
|
Prefetch가 동작해 stat 값이 92로 확인됨
outline data에는 NLJ_PREFETCH가 없지만 실제로는 Prefetch가 동작함을 알수 있음
그리고 Prefetch만 동작했을때 보다 값이 3배 이상 더 많음
참조 :
https://positivemh.tistory.com/782
오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, vector I/O)
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 11.1.0.1 방법 : 오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, Batch I/O, Vector I/O) 일반 NL 조인에서는 아래와 같은 실행계획이 나
positivemh.tistory.com
오라클 성능고도화 원리와 해법 1권 452p
오라클 성능고도화 원리와 해법 2권 222p
친절한 SQL 튜닝 270p
http://www.gurubee.net/article/86261
https://www.cnblogs.com/suncoolcat/p/3290251.html
https://www.gurubee.net/wiki/pages/267422213
https://scidb.tistory.com/entry/Nested-Loop-Join-성능향상과-관련된-2가지-원리
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/influencing-the-optimizer.html#GUID-8758EF88-1CC6-41BD-8581-246702414D1D
https://hrjeong.tistory.com/201
https://cafe.naver.com/dbian/1424
https://cafe.naver.com/dbian/1581
https://cafe.naver.com/dbian/1583
https://cafe.naver.com/dbian/5206
https://hrjeong.tistory.com/350
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c FULL TABLE SCAN (FTS)시 direct path read 이벤트 관련 (0) | 2024.03.21 |
|---|---|
| 오라클 19c group by 컬럼 나열 및 집계함수 적용 성능 비교 (0) | 2024.03.19 |
| 오라클 19c sqlplus의 statementcache(애플리케이션 커서 캐시) (0) | 2024.02.24 |
| 오라클 19c 인덱스 생성시 nosort 옵션 대기 이벤트 확인 (0) | 2024.02.23 |
| 오라클 19c 하나의 쿼리블록에 힌트 여러개 작성시 나타나는 현상 (0) | 2024.01.21 |