꿈꾸는 개발자, DBA 커뮤니티 구루비

신규로 오라클 DBMS를 설치/구축해야 할 때 DB BLOCK SIZE를 고민해본 사람은 그리 많지 않을 것이다.

하지만 DBMS 설치 시 기본값으로만 설치를 하게 된다면, 이 후 그 데이터베이스는 성능 문제와 또 다른 문제와 만나게 될 가능성이 크다.

DBMS를 설치해야 할 때에는 어떠한 성격의 데이터를 보관할 것인지, 향후 어떻게 운영될 것인지를 생각하여 적절한 옵션을 선택하여 설치해야 한다.

이번 강좌에서는 DBMS 설치/구축 시 DATA BLOCK SIZE를 결정하는데 있어 도움이 될만한 내용을 소개하고자 한다. 정확한 테스트 결과를 보기 위해 DIRECT PATH READ 기능은 FALSE로 설정하여 테스트했다.

테스트 환경

[표 1] 테스트 환경을 명시한 표

Oracle Version	Oracle 11.2.0.2.0 (single)
8K_DB_CACHE_SIZE	500MB
16K_DB_CACHE_SIZE	500MB
TABLE SIZE	약 2G
수행횟수	각 100회
DB_FILE_MULTIBLOCK_READ_COUNT	16
EXTENT SIZE	128 KB

테스트 수행

테스트에는 조건에 만족하는 데이터가 없도록 WHERE 조건을 추가했다. 패치(Fetch)에 따른 수행 속도가 포함되지 않게 하기 위해서다.

모든 블록을 디스크로부터 엑세스하도록 매번 수행할 때마다 버퍼 캐시 플러시(Buffer cache flush)를 수행했다. 16K 테이블도 동일한 방법으로 수행했다.

[리스트 1] 테스트 수행

ALTER SYSTEM FLUSH BUFFER_CACHE;

!SLEEP 1

SELECT col1, col2, col3, col4
  FROM test_ksh.tb_b8k_e128k
 WHERE col1 = '333333';
 
!SLEEP 1

테스트 결과

테스트 수행에 따른 결과를 정리했다.

[리스트 2] 테스트 결과


-- 8K Block Table

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      201      0.00       0.00          0          0          0           0
Execute    201      0.04       2.97          0          0          0           0
Fetch      100    146.40     164.49   27765871   30675901          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      502    146.45     167.48   27765871   30675901          0           0

Misses in library cache during parse: 1
Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     201        0.00          0.00
  SQL*Net message from client                   201        1.15        200.99
  rdbms ipc reply                               100        0.19          2.90
  db file sequential read                    147002        0.08          2.22
  Disk file operations I/O                        2        0.00          0.00
  db file scattered read                    1742700        0.06         72.52


-- 16K Block Table
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      201      0.00       0.00          0          0          0           0
Execute    201      0.04       1.86          0          0          0           0
Fetch      100    114.79     138.77   13731181   15265501          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      502    114.83     140.63   13731181    15265501         0           0


Misses in library cache during parse: 1
Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     201        0.00          0.00
  SQL*Net message from client                   201        1.02        200.87
  rdbms ipc reply                               100        0.11          1.78
  db file sequential read                    143297        0.07          2.83
  Disk file operations I/O                        2        0.00          0.00
  db file scattered read                    1730100        0.12         74.06

16K 블록을 사용하는 테이블을 엑세스하는 경우가 138.77초로, 8K 블록을 사용하는 테이블을 엑세스 하는 경우 164.49초에 비해 약 20% 정도 성능이 좋다는 결과가 나왔다.

단 Excute의 수행시간은 버퍼 캐시 플러시에 의한 수행시간이 포함돼 무시했다. 이를 토대로 정말 DB 구축 시 무조건 16K 블록으로 DB를 구축해야 한다는 결론을 낼 수 있는 것일까?

결과를 잘 보면 수행 속도는 16K인 블록이 성능이 좋아 보이지만 8K에 비해 2배가 큰 블록을 메모리로 적재해야 하는 비용이 있기 때문에 db file scattered read에 대한 I/O 부담은 16K인 블록이 더 높다는 것을 알 수 있다.

그렇다면 TABLE FULL SCAN이 아닌 OLTP환경과 비슷하게 각 테이블마다 인덱스를 추가하고 한 건의 데이터를 가져오는 환경으로 테스트를 수행해보면 어떤 결과가 나올까?

각 테이블에서 인덱스 스캔(Index scan)으로 한 건의 데이터만 fetch하며, 테이블로 1회 Random Access하도록 유도했으며 테스트 정확성을 위해 각 1만 번 수행했다.

[리스트 3] 테이블마다 인덱스 추가해 테스트한 결과


-- 8K Block Table
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    20000      0.13       0.38          0          0          0           0
Execute  20000      2.58      74.22          0          0          0           0
Fetch    10000     15.04      14.70      40000      40000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    50000     17.77      89.31      40000      40000          0       10000

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                             10000        0.02         72.43
  Disk file operations I/O                        1        0.00          0.00
db file sequential read                     40000        0.00          0.92




-- 16K Block Table
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    20000      0.12       0.37          0          0          0           0
Execute  20000      2.20      68.40          0          0          0           0
Fetch    10000     16.33      15.53      40000      40000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    50000     18.66      84.30      40000      40000          0       10000

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                             10000        0.04         66.64
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                     40000        0.00          1.68

수행 시간은 두 경우 차이가 없으나 I/O에 대한 Wait은 여전히 16K Block이 높은 것을 볼 수 있다.