메뉴 건너뛰기

Korea Oracle User Group

Admin

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배 정도 빠르다고 볼 수 있다.

 

정리

  1. 대기이벤트로 비교할 때 db file sequential read 이벤트가 db file scattered read 이벤트보다 더 짧은 시간을 대기한다. 당연하다. 1 block을 읽는 이벤트보다 128 block을 읽는 작업에 대한 대기 이벤트가 오래 걸리지 않겠나? 
     
  2. block 당으로 시간을 비교하면 mbr 이 sbr 보다 5배 정도 빠르다. 하지만 여기에는 읽어 들이는 데이터의 효용성에 따라서 각각에 맞는 방식이 있다. 
    즉, 많은 데이터를 읽어 들이고 필터조건으로 상당수를 버린다면 mbr 방식이 아무리 빠르더라도 비효율이 많다는 이야기이다.
    간혹 오라클 문서를 볼 때 두 방식에 대해 20% 이상의 데이터를 읽을 때 mbr 방식이 낫다고 하는 내용이 있는데 이는 5배 차이에서 나온 수치가 아닐까 예상도 해 본다.
     
  3. 위 두가지 방식에 대해서 절대적으로 우위에 있는 방식은 없다. 상황에 맞는 적절한 방식이 있을 뿐이다.

   

 

번호 제목 글쓴이 날짜 조회 수
34 Windows 접속 에러 ORA-12638 [1] file Talros 2024.02.14 54
33 Python 을 이용해 파일 업로드 해 보기 [1] file Talros 2023.04.19 492
32 수집된 통계가 바로 반영되지 않고 원할 때 반영하도록 하는 방식 명품관 2023.04.04 224
31 ORA-28014: Cannot Drop Administrative Users 에러 현상 [1] 명품관 2023.03.27 341
30 ORA-3136 inbound connection time out & JDBC Io exception: Connection reset [1] Talros 2023.02.24 594
29 default 값을 포함한 컬럼 추가시 오라클 버전별 개선 사항 [1] 명품관 2023.01.17 443
» mbr(multi block read) 과 sbr(single block read) 사이에 과연 어떤 방식을 선택해야 할까요? [1] 명품관 2022.09.16 299
27 Schema password 재 사용 불가능 하게 하기 [1] Talros 2022.01.26 1193
26 Pga 메모리 설정관련 내용 [1] 우뽕 2021.03.05 1742
25 DB option Enable / Disable 정리 방법 - Mos 참고 file 우뽕 2021.01.31 1186
24 RECO 프로세스 트레이스 발생 하면서 ORA-02019 에러 발생시 명품관 2021.01.12 951
23 OS 터미널 접속 없이 오라클 접속을 통해 OS CPU 사용율 모니터링 하기 [3] file 명품관 2020.12.22 783
22 SQL Plan Management(SPM) - 3 Manual Plan Capture 명품관 2020.06.01 614
21 SQL Plan Management(SPM) - 2 DBMS_SPM.CONFIG로 필터링 file 명품관 2020.03.26 551
20 SQL Plan Management(SPM) - 1 파라미터의 기능 확인 명품관 2020.03.19 907
19 [12cR2 이상] 오브젝트 이름 30자이상 사용 가능 우뽕 2020.01.22 1400
18 Alert log 에서 갑자기 패치 정보가 나타나는 현상 Talros 2019.09.23 1598
17 External Table 기능을 사용해 파일 읽기 명품관 2017.04.05 947
16 DBMS_SCHEDULER을 통해 OS 레벨(EXTERNAL)의 shell 수행하기 명품관 2017.04.05 5755
15 오라클 패치 정보를 조회할 수 있는 뷰 DBA_REGISTRY_SQLPATCH 명품관 2017.03.02 5411
위로