OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle Database 19.31.0.0
방법 : 오라클 19c Batch I/O 동작 및 버퍼 사용량 테스트
TABLE ACCESS BY INDEX ROWID BATCHED는 인덱스에서 일부 ROWID를 가져온 후 해당 ROWID가 가리키는 행을 블록 순서로 접근하도록 시도하는 기능임
CF가 좋은 경우에는 인덱스 키 순서와 테이블의 물리적인 저장 순서가 비슷하므로 일반적인 TABLE ACCESS BY INDEX ROWID 방식으로 접근해도 같은 블록의 행을 연속해서 읽을 가능성이 높음
따라서 Batch I/O 방식이 적용되더라도 성능 차이가 크지 않을 수 있음
반대로 CF가 나쁜 경우에는 인덱스 키 순서와 테이블의 물리적인 저장 순서가 다르기 때문에 테이블 블록을 반복해서 접근할 가능성이 높음
이때 일부 ROWID를 모아 블록 순서로 접근하면 같은 블록에 대한 반복 접근을 줄이고 I/O 효율을 높일 수 있음
이 기능이 적용되면 실행계획에 TABLE ACCESS BY INDEX ROWID BATCHED로 표시됨
그리고 Batch I/O 방식에서는 인덱스에서 읽은 ROWID 순서와 다르게 테이블 블록을 접근할 수 있기 때문에 인덱스 키 순서대로 행이 반환되지 않을 수 있음
오라클 공식 가이드로 ORDER BY가 없는 SQL은 원래 결과 순서를 보장하지 않는다고 명시하고 있기 때문에 정렬된 결과가 필요한 경우 ORDER BY를 명시해줘야 함
Batch I/O 방식을 제어하려면 세션 또는 시스템 레벨에서 "_optimizer_batch_table_access_by_rowid" 히든 파라미터를 사용하거나
SQL 레벨에서 BATCH_TABLE_ACCESS_BY_ROWID 또는 NO_BATCH_TABLE_ACCESS_BY_ROWID 힌트를 사용하면 됨
본문에서는 Batch I/O 방식 적용 여부에 따라 인덱스 키 순서가 유지되는지 확인하고 Buffers와 Reads 사용량에 어떤 차이가 발생하는지 확인해봄
테스트
사전 설정
배치 io 비활성화 후 별도 테이블에 삽입
배치 io 활성화 후 별도 테이블에 삽입
batch io 비활성화시 데이터 순서 확인
batch io 비활성화시 데이터 순서 확인
테스트
사전 설정
|
1
2
3
4
5
6
7
8
|
SQL>
set lines 250 pages 1000
set timing on
set serveroutput off
alter session set statistics_level = all;
alter session set optimizer_dynamic_sampling = 0;
alter session set "_optimizer_gather_stats_on_load" = false;
|
샘플 테이블 생성 및 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
drop table t_batch_sort purge;
create table t_batch_sort (
c1 number,
c2 varchar2(1000)
);
insert into t_batch_sort
select mod(level - 1, 100) as c1,
rpad(to_char(level), 1000, 'X') as c2
from dual
connect by level <= 1000;
commit;
|
인덱스 생성 및 통계 정보 수집(c1 컬럼으로 정렬된 인덱스를 생성함)
|
1
2
3
|
SQL>
create index ix1_t_batch_sort on t_batch_sort(c1);
exec dbms_stats.gather_table_stats(user, 'T_BATCH_SORT', cascade => true);
|
배치 io 비활성화 후 별도 테이블에 삽입
(ix1_t_batch_sort 인덱스를 이용하기 때문에 c1 컬럼값으로 정렬된채로 데이터가 들어감)
|
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
|
SQL>
drop table t_batch_off purge;
alter system flush buffer_cache;
alter session set "_optimizer_batch_table_access_by_rowid" = false;
create table t_batch_off as
select /*+ index(t ix1_t_batch_sort)
no_batch_table_access_by_rowid(t) */
rownum as rn,
t.c1,
substr(t.c2, 1, 6) as c2,
t.rowid as rid
from t_batch_sort t
where t.c1 >= 0;
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 1215737383
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 0 |00:00:00.03 | 1063 | 171 | 5 | | | |
| 1 | LOAD AS SELECT | T_BATCH_OFF | 1 | 0 |00:00:00.03 | 1063 | 171 | 5 | 1042K| 1042K| 1042K (0)|
| 2 | COUNT | | 1 | 1000 |00:00:00.01 | 1003 | 160 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T_BATCH_SORT | 1 | 1000 |00:00:00.01 | 1003 | 160 | 0 | | | |
|* 4 | INDEX RANGE SCAN | IX1_T_BATCH_SORT | 1 | 1000 |00:00:00.01 | 3 | 8 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
|
배치 io 비활성화 시 buffer를 1003, reads를 160 소모함
배치 io 활성화 후 별도 테이블에 삽입
|
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
|
SQL>
drop table t_batch_on purge;
alter system flush buffer_cache;
alter session set "_optimizer_batch_table_access_by_rowid" = true;
create table t_batch_on as
select /*+ index(t ix1_t_batch_sort)
batch_table_access_by_rowid(t) */
rownum as rn,
t.c1,
substr(t.c2, 1, 6) as c2,
t.rowid as rid
from t_batch_sort t
where t.c1 >= 0;
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 1927831086
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 0 |00:00:00.01 | 872 | 166 | 5 | | | |
| 1 | LOAD AS SELECT | T_BATCH_ON | 1 | 0 |00:00:00.01 | 872 | 166 | 5 | 1042K| 1042K| 1042K (0)|
| 2 | COUNT | | 1 | 1000 |00:00:00.01 | 812 | 155 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T_BATCH_SORT | 1 | 1000 |00:00:00.01 | 812 | 155 | 0 | | | |
|* 4 | INDEX RANGE SCAN | IX1_T_BATCH_SORT | 1 | 1000 |00:00:00.01 | 3 | 8 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|
배치 io 활성화 시 buffer를 812, reads를 155 소모함
batch io 비활성화시 데이터 순서 확인
|
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
|
SQL>
select rn, c1, c2, rid
from t_batch_off
where rn between 1 and 20
order by rn;
RN C1 C2 RID
---------- ---------- ------------ ------------------
1 0 1XXXXX AAAP8VAAEAADZy+AAA
2 0 101XXX AAAP8VAAEAADZzKAAC
3 0 201XXX AAAP8VAAEAADZzZAAE
4 0 301XXX AAAP8VAAEAADZzkAAG
5 0 401XXX AAAP8VAAEAADZz0AAB
6 0 501XXX AAAP8VAAEAADa+DAAD
7 0 601XXX AAAP8VAAEAADa+WAAF
8 0 701XXX AAAP8VAAEAADa+mAAA
9 0 801XXX AAAP8VAAEAADa+1AAC
10 0 901XXX AAAP8VAAEAADbBbAAE
11 1 2XXXXX AAAP8VAAEAADZy+AAB
12 1 102XXX AAAP8VAAEAADZzKAAD
13 1 202XXX AAAP8VAAEAADZzZAAF
14 1 302XXX AAAP8VAAEAADZzpAAA
15 1 402XXX AAAP8VAAEAADZz0AAC
16 1 502XXX AAAP8VAAEAADa+DAAE
17 1 602XXX AAAP8VAAEAADa+WAAG
18 1 702XXX AAAP8VAAEAADa+mAAB
19 1 802XXX AAAP8VAAEAADa+1AAD
20 1 902XXX AAAP8VAAEAADbBbAAF
|
c1 컬럼이 정상적으로 정렬되어서 들어가 있음
batch io 비활성화시 데이터 순서 확인
|
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
|
SQL>
select rn, c1, c2, rid
from t_batch_on
where rn between 1 and 20
order by rn;
RN C1 C2 RID
---------- ---------- ------------ ------------------
1 0 1XXXXX AAAP8VAAEAADZy+AAA
2 0 101XXX AAAP8VAAEAADZzKAAC
3 1 2XXXXX AAAP8VAAEAADZy+AAB
4 1 102XXX AAAP8VAAEAADZzKAAD
5 2 3XXXXX AAAP8VAAEAADZy+AAC
6 2 103XXX AAAP8VAAEAADZzKAAE
7 2 503XXX AAAP8VAAEAADa+DAAF
8 0 201XXX AAAP8VAAEAADZzZAAE
9 1 202XXX AAAP8VAAEAADZzZAAF
10 2 203XXX AAAP8VAAEAADZzZAAG
11 0 301XXX AAAP8VAAEAADZzkAAG
12 0 401XXX AAAP8VAAEAADZz0AAB
13 1 402XXX AAAP8VAAEAADZz0AAC
14 2 403XXX AAAP8VAAEAADZz0AAD
15 0 501XXX AAAP8VAAEAADa+DAAD
16 1 502XXX AAAP8VAAEAADa+DAAE
17 0 601XXX AAAP8VAAEAADa+WAAF
18 1 602XXX AAAP8VAAEAADa+WAAG
19 0 701XXX AAAP8VAAEAADa+mAAA
20 1 702XXX AAAP8VAAEAADa+mAAB
|
c1 컬럼 정렬이 틀어져 있음
결과 비교 :
Batch I/O를 비활성화한 경우 TABLE ACCESS BY INDEX ROWID 방식(일반 방식)으로 테이블에 접근했고 C1 인덱스의 키 순서대로 결과가 나옴
원본 테이블 접근 구간인 Id 3을 기준으로 Buffers는 1003, Reads는 160이 발생함
Batch I/O를 활성화한 경우 TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 테이블에 접근했고 C1 값이 인덱스 키 순서와 다르게 나옴
원본 테이블 접근 구간인 Id 3을 기준으로 Buffers는 812, Reads는 155가 발생함
두 실행 모두 하위 INDEX RANGE SCAN의 Buffers는 3으로 동일했지만 TABLE ACCESS 시 Buffers가 1003에서 812로 감소함(Reads는 160에서 155로 소폭 감소함)
결론 :
Batch I/O가 비활성화된 경우 이번 테스트에서는 C1 인덱스의 키 순서대로 결과가 출력됨
반면 Batch I/O가 활성화된 경우에는 일부 ROWID를 모아 테이블 블록 순서로 접근하면서 C1 값이 인덱스 키 순서와 다르게 출력됨
오라클 공식 가이드로 ORDER BY가 없는 SQL은 원래 결과 순서를 보장하지 않는다고 명시하고 있기 때문에
인덱스를 사용한다는 이유만으로 결과가 정렬될 것으로 생각하면 안되고 정렬된 결과가 필요한 경우 ORDER BY를 명시해줘야 함
성능 측면에서는 Batch I/O 활성화 후 TABLE ACCESS 계층의 Buffers가 1003에서 812로 감소했고 Reads도 160에서 155로 소폭 감소함
이를 통해 CF가 좋지 않은 인덱스를 이용해 테이블에 접근하는 경우 BATCHED 방식이 같은 블록에 대한 반복 접근을 줄여 버퍼 사용량과 I/O 효율을 개선할 수 있음을 확인함
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-access-paths.html#GUID-720EA54F-AB65-4379-99A3-CAE166590127
오라클 19c 병렬 ITAS를 이용한 Clustering Factor 개선 테스트 ( https://positivemh.tistory.com/1399 )
오라클 19c Prefetch, Batch I/O, Table access by rowid batched 설명 ( https://positivemh.tistory.com/1026 )
https://hrjeong.tistory.com/201
https://hrjeong.tistory.com/213
'ORACLE > Performance Tuning' 카테고리의 다른 글
| 오라클 19c CLUSTER_BY_ROWID 힌트 사용 테스트 (0) | 2026.08.14 |
|---|---|
| 오라클 19c stale percent 변경 및 자동통계수집 (0) | 2026.06.22 |
| 오라클 19c 펜딩 통계(Pending Statistics) (0) | 2026.06.18 |
| 오라클 19c 병렬 ITAS를 이용한 Clustering Factor 개선 테스트 (0) | 2026.06.17 |
| 오라클 19c AWR 스냅샷의 플랜을 SPM(SQL Plan Management)으로 고정 (0) | 2026.05.24 |