mbr(multi block read) 과 sbr(single block read) 사이에 과연 어떤 방식을 선택해야 할까요?
오라클에서 데이터 조회시 db file scattered read 와 db file sequential read 두가지 read 방식 중 어떤 방식을 선택하는게 좋을지 고민하게 됩니다.
또 SQL 튜닝을 이제 시작한 분은 어떤 방식을 사용하여 성능을 개선할지 고민하게 됩니다.
공식과도 같이 답이 도출되거나 절대적 기준이 있어서 답을 정하기 편하면 좋을 텐데
그렇지 않은게 세상 일이죠. ^^
그래서 최근에 mbr 과 sbr 에 대한 시간에 대해서 비교해 봤습니다.
대량 데이터 조회나 활용시에는 mbr 이 빠르고 소량 데이터 접근에는 인덱스를 사용한 sbr 이 빠르다는 것은
여러 글과 책을 통해서 알고 있을 겁니다. 튜닝시 경험도 했을 겁니다.
이제 간단한 테스트로 수치로 확인해 볼려고 합니다.
아래 내용은 참고만 하시고 상황에 따라 어떤 것이 좋은 지, 혹은 mbr 과 sbr 의 방식에 대해 알아 두는 정도로만 이해해 주시면 좋겠습니다.
그리고 각 테스트 환경에 따라 수치의 차이가 발생할 수 있습니다.
1. 테스트 환경 구성
1) 테스트용 테이블 생성
|
1
2
3
4
5
6
7
|
create table diff_mbrc_sbr
(
id number
,col1 varchar2(100)
,col2 varchar2(100)
,col3 varchar2(100)
);
|
2) 테스트 데이터 생성
|
1
2
3
4
5
6
7
8
9
|
begin
FOR c IN 1 .. 100000 LOOP
insert into diff_mbrc_sbr
values (c,dbms_random.string('a',100),dbms_random.string('a',100),dbms_random.string('a',100));
END LOOP;
commit;
end;
/
|
3) 인덱스 생성
|
1
|
create index diff_mbrc_sbr_ix1 on diff_mbrc_sbr(id);
|
2. 테스트 수행
1) 트레이스 파일 생성을 위해 이벤트 설정
|
1
2
|
set autotrace traceonly
alter session set events '10046 trace name context forever, level 12';
|
2) physical I/O 발생을 위해 테스트 전 buffer cache flush를 수행시킨다.
|
1
|
alter system flush buffer_cache;
|
3) 인덱스를 사용하여 db file sequential read 이벤트를 유도
|
1
2
3
4
5
6
|
SELECT /*+ INDEX(a diff_mbrc_sbr_ix1) */
*
FROM DIFF_MBRC_SBR A
WHERE ID IN (SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1000000);
|
4) physical I/O 발생을 위해 테스트 전 buffer cache flush를 수행시킨다.
|
1
|
alter system flush buffer_cache;
|
5) Full Table Scan 방식으로 db file scattered read
|
1
2
|
SELECT *
FROM DIFF_MBRC_SBR A;
|
6) 트레이스 이벤트 종료
|
1
|
alter session set sql_trace=false;
|
위 테스트를 통해 트레이스 파일을 얻을 수 있으며 해당 트레이스 파일을 통해 tkprof를 사용하여 아래의 결과를 얻을 수 있다.
먼저 인덱스를 사용한 sbr 에 대한 내용을 확인해 보겠습니다.
|
SELECT /*+ INDEX(a diff_mbrc_sbr_ix1) */
*
FROM DIFF_MBRC_SBR A
WHERE ID IN (SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1000000)
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 66668 3.72 6.68 45707 175993 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66670 3.72 6.68 45707 175993 0 1000000
|
수행된 SQL과 시간, disk 사용 정보들이 담겨있다. disk 부분에 기록된 45707 에 대해 먼저 기억해 두자.
|
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 66668 0.00 0.08
PGA memory operation 113 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 45707 0.00 0.62 => 13.56 us
SQL*Net message from client 66668 0.00 19.61
|
위 내용을 보면 db file sequential read 의 Times Waited 값이 45707 인 것을 확인할 수 있다.
즉 테이블 전체 건수 1000000 것을 읽는데 sbr 만 45707 번 했다는 것을 알 수 있다.
또, Total Waited 값이 0.62 초이므로 0.62 / 45707 = 13.56 us 으로 계산이 됩니다.
즉, db file sequential read 로 sbr시 평균적으로 13.56 us 걸린다고 볼 수 있습니다.
이제 Full Table Scan으로 실행된 문장의 트레이스 내용을 살펴 보겠습니다.
|
SELECT *
FROM DIFF_MBRC_SBR A
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 66668 0.71 2.09 43512 107294 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66670 0.71 2.09 43512 107294 0 1000000
|
수행된 SQL과 시간, disk 사용 정보들이 담겨있다.
|
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 66668 0.00 0.07
db file sequential read 4 0.00 0.00
db file scattered read 358 0.00 0.11 =>117102 => 327 us =>block 당 계산하면 2.69 us
SQL*Net message from client 66668 0.00 17.16
|
위 통계를 보면 43512 block을 읽는 동안 db file scattered read 이벤트가 358회 발생했다는 내용이 있습니다.
또 해당 이벤트 동안 걸린 시간은 0.11 초 입니다.
트레이스 파일의 raw 데이터를 통해서 확인해 보면 db file scattered read 이벤트 동안 대기한 시간은 정확히 117102 us 입니다.
그럼 117102 / 358 = 327 us 계산이 됩니다. 즉 db file scattered read 1회당 327 us 걸리는 것입니다.
또 트레이스 파일의 raw 데이터에서 db file scattered read 이벤트 1회에 평균적으로 121 block을 읽은 것을 확인할 수 있었습니다.
327 / 121 = 2.69 정도의 값이 나옵니다. 그러면 block 당 2.69 us 걸린다고 볼 수 있습니다.
그럼 이제 위에서 인덱스를 사용해서 sbr 로 읽었을 때와 mbr로 읽었을 때의 시간을 비교해 보겠습니다.
| Event Wait Time(us) | Block 수 | Block당 소요시간(us) | |
|---|---|---|---|
| SBR | 13.56 | 1 | 13.56 |
| MBR | 327 | 124 | 2.69 |
Block 당으로 놓고 본다면 mbr 이 sbr에 비해 5배 정도 빠르다고 볼 수 있다.
정리
- 대기이벤트로 비교할 때 db file sequential read 이벤트가 db file scattered read 이벤트보다 더 짧은 시간을 대기한다. 당연하다. 1 block을 읽는 이벤트보다 128 block을 읽는 작업에 대한 대기 이벤트가 오래 걸리지 않겠나?
- block 당으로 시간을 비교하면 mbr 이 sbr 보다 5배 정도 빠르다. 하지만 여기에는 읽어 들이는 데이터의 효용성에 따라서 각각에 맞는 방식이 있다.
즉, 많은 데이터를 읽어 들이고 필터조건으로 상당수를 버린다면 mbr 방식이 아무리 빠르더라도 비효율이 많다는 이야기이다.
간혹 오라클 문서를 볼 때 두 방식에 대해 20% 이상의 데이터를 읽을 때 mbr 방식이 낫다고 하는 내용이 있는데 이는 5배 차이에서 나온 수치가 아닐까 예상도 해 본다.
- 위 두가지 방식에 대해서 절대적으로 우위에 있는 방식은 없다. 상황에 맞는 적절한 방식이 있을 뿐이다.
좋은 내용 감사합니다.