개요
db_file_multiblock_read_count 파라미터는 multiblock read 수행 시 한 번의 I/O 작업에서 읽을 수 있는 최대 블록 수를 지정합니다. 기본값은 플랫폼이 효율적으로 수행할 수 있는 최대 I/O 크기(대부분의 플랫폼은 1MB)를 블록 크기(db_block_size = 8K)로 나눈 값인 128로 설정됩니다.
10.2 이전 버전은 multiblock read의 수행과 비용 계산에 모두 db_file_multiblock_read_count 파라미터를 사용했지만 10.2 버전부터 multiblock read의 수행은 _db_file_exec_read_count 파라미터, 시스템 통계(MBRC)가 수집되지 않은 경우 multiblock read의 비용 계산은 _db_file_optimizer_read_count 파라미터를 사용합니다. _db_file_exec_read_count 파라미터의 기본값은 db_file_multiblock_read_count 파라미터와 동일하며, _db_file_optimizer_read_count 파라미터의 기본값은 db_file_multiblock_read_count 파라미터가 설정되지 않은 경우 8, db_file_multiblock_read_count 파라미터가 설정되지 않은 경우 db_file_multiblock_read_count 파라미터와 동일합니다.
NAME VALUE ISDEFAULT ISSES_MODIFIABLE DESCRIPTION
----------------------------- ----- --------- ---------------- -----------------------------------------
db_file_multiblock_read_count 128 TRUE TRUE db block to be read each IO
_db_file_exec_read_count 128 TRUE TRUE multiblock read count for regular clients
_db_file_optimizer_read_count 8 TRUE TRUE multiblock read count for regular clients
아래와 같이 db_file_multiblock_read_count 파라미터에 값을 설정하면 _db_file_exec_read_count, _db_file_optimizer_read_count 파라미터가 동일한 값으로 설정되는 것을 확인할 수 있습니다.
-- 2-1
ALTER SESSION SET db_file_multiblock_read_count = 64;
NAME VALUE
----------------------------- -----
db_file_multiblock_read_count 64
_db_file_exec_read_count 64
_db_file_optimizer_read_count 64
-- 2-2
ALTER SESSION SET db_file_multiblock_read_count = 128;
NAME VALUE
----------------------------- -----
db_file_multiblock_read_count 128
_db_file_exec_read_count 128
_db_file_optimizer_read_count 128
-- 2-3
ALTER SESSION SET db_file_multiblock_read_count = 256;
NAME VALUE
----------------------------- -----
db_file_multiblock_read_count 256
_db_file_exec_read_count 256
_db_file_optimizer_read_count 256
준비
테스트 환경은 아래와 같습니다.
-- 2
SELECT version_full FROM product_component_version;
VERSION_FULL
------------
19.24.0.0.0
1 row selected.
테스트를 위해 아래와 같이 테이블스페이스와 테이블을 생성하겠습니다. 한 번의 I/O 작업에서 일정한 블록 수를 조회하기 위해 uniform 테이블스페이스를 생성했습니다. 통계 정보 상의 테이블 블록 수는 1,431,212입니다.
-- 3-1
CREATE BIGFILE TABLESPACE ts_u10m DATAFILE 'DATAC1+' SIZE 100G UNIFORM SIZE 10M;
-- 3-2
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) TABLESPACE ts_u10m AS
SELECT ROWNUM, LPAD ('X', 1000, 'X') FROM XMLTABLE ('1 to 10000000');
EXEC DBMS_STATS.GATHER_TABLE_STATS (NULL, 'T1');
-- 3-3
SELECT blocks FROM user_tab_statistics WHERE table_name = 'T1';
BLOCKS
-------
1431212
1 row selected.
읽기 수행
_db_file_exec_read_count 파라미터가 multiblock read 수행에 미치는 영향을 conventional I/O와 direct path I/O로 구분하여 살펴보겠습니다.
Conventional I/O
RAC 환경인 경우 gc cr multi block grant 이벤트로 인항 성능 저하를 방지하기 위해 아래와 같이 수동으로 resource remastering을 수행해야 합니다.
-- 4: sys
oradebug setmypid
oradebug lkdebug -m pkey 70139 12 -- {user_objects.object_id} {v$tablespace.ts#}
multiblock read를 conventional I/O로 수행하기 위해 _serial_direct_read 파라미터를 NEVER로 설정하고 db_file_multiblock_read_count 파라미터를 64, 128, 256으로 설정한 후 쿼리를 수행하겠습니다. 10046 트레이스를 살펴보면 conventional I/O인 경우 db_file_multiblock_read_count 파라미터를 256으로 설정해도 한 번의 I/O 작업에서 128 블록(blocks=128)만 읽은 것을 확인할 수 있습니다.
-- 5-1
ALTER SESSION SET "_serial_direct_read" = NEVER;
-- 5-2: _db_file_exec_read_count = 64
ALTER SESSION SET db_file_multiblock_read_count = 64;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT COUNT (*) FROM t1;
Elapsed: 00:00:11.74
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 22363 0.00 5.49
WAIT #: nam='db file scattered read' ela= 242 file#=91 block#=2350102 blocks=64 obj#=70139
WAIT #: nam='db file scattered read' ela= 224 file#=91 block#=2350166 blocks=64 obj#=70139
WAIT #: nam='db file scattered read' ela= 269 file#=91 block#=2350230 blocks=64 obj#=70139
-- 5-3: _db_file_exec_read_count = 128
ALTER SESSION SET db_file_multiblock_read_count = 128;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT COUNT (*) FROM t1;
Elapsed: 00:00:10.05
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 11182 0.00 4.17
WAIT #: nam='db file scattered read' ela= 380 file#=91 block#=2350102 blocks=128 obj#=70139
WAIT #: nam='db file scattered read' ela= 360 file#=91 block#=2350230 blocks=128 obj#=70139
WAIT #: nam='db file scattered read' ela= 363 file#=91 block#=2350358 blocks=128 obj#=70139
-- 5-4: _db_file_exec_read_count = 256
ALTER SESSION SET db_file_multiblock_read_count = 256;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT COUNT (*) FROM t1;
Elapsed: 00:00:10.03
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 11182 0.00 4.14
WAIT #: nam='db file scattered read' ela= 377 file#=91 block#=2350102 blocks=128 obj#=70139
WAIT #: nam='db file scattered read' ela= 358 file#=91 block#=2350230 blocks=128 obj#=70139
WAIT #: nam='db file scattered read' ela= 364 file#=91 block#=2350358 blocks=128 obj#=70139
Direct path I/O
multiblock read를 direct path I/O로 수행하기 위해 _serial_direct_read 파라미터를 ALWAYS로 설정하고 db_file_multiblock_read_count 파라미터를 64, 128, 256으로 설정한 후 쿼리를 수행하겠습니다. 10046 트레이스를 살펴보면 direct path I/O인 경우 db_file_multiblock_read_count 파라미터를 256로 설정하면 한 번의 I/O 작업에서 256 블록(blocks=256)을 읽은 것을 확인할 수 있습니다.
-- 6-1
ALTER SESSION SET "_serial_direct_read" = ALWAYS;
-- 6-2: _db_file_exec_read_count = 64
ALTER SESSION SET db_file_multiblock_read_count = 64;
SELECT COUNT (*) FROM t1;
Elapsed: 00:00:04.31
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 14675 0.00 2.38
WAIT #: nam='direct path read' ela= 48 file number=91 first dba=2350144 block cnt=64 obj#=70139
WAIT #: nam='direct path read' ela= 99 file number=91 first dba=2350208 block cnt=64 obj#=70139
WAIT #: nam='direct path read' ela= 92 file number=91 first dba=2350272 block cnt=64 obj#=70139
-- 6-3: _db_file_exec_read_count = 128
ALTER SESSION SET db_file_multiblock_read_count = 128;
SELECT COUNT (*) FROM t1;
Elapsed: 00:00:03.70
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 6714 0.00 2.04
WAIT #: nam='direct path read' ela= 723 file number=91 first dba=2350336 block cnt=128 obj#=70139
WAIT #: nam='direct path read' ela= 110 file number=91 first dba=2350464 block cnt=128 obj#=70139
WAIT #: nam='direct path read' ela= 301 file number=91 first dba=2350592 block cnt=128 obj#=70139
-- 6-4: _db_file_exec_read_count = 256
ALTER SESSION SET db_file_multiblock_read_count = 256;
SELECT COUNT (*) FROM t1;
Elapsed: 00:00:03.76
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 2863 0.00 1.90
WAIT #: nam='direct path read' ela= 459 file number=91 first dba=2350592 block cnt=256 obj#=70139
WAIT #: nam='direct path read' ela= 299 file number=91 first dba=2351104 block cnt=256 obj#=70139
WAIT #: nam='direct path read' ela= 897 file number=91 first dba=2351616 block cnt=256 obj#=70139
상세한 동작을 검증하기 위해 아래와 같이 MBRC를 2의 거듭제곱 단위(8부터 4096까지)로 증가시키며 physical read IO requests 값과 수행 시간을 측정하겠습니다.
-- 7-1
DROP TABLE t_mbrc PURGE;
CREATE TABLE t_mbrc (
mbrc NUMBER
, requests NUMBER
, elapsed NUMBER
);
-- 7-2
DECLARE
v_statistic# NUMBER;
v_value NUMBER;
v_time PLS_INTEGER;
v_result NUMBER;
BEGIN
SELECT statistic#
INTO v_statistic#
FROM v$statname
WHERE name = 'physical read IO requests';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_serial_direct_read" = ALWAYS';
FOR f1 IN (SELECT POWER (2, ROWNUM + 2) AS mbrc FROM DUAL CONNECT BY LEVEL <= 10) LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET "db_file_multiblock_read_count" = ' || f1.mbrc;
SELECT value
INTO v_value
FROM v$mystat
WHERE statistic# = v_statistic#;
v_time := DBMS_UTILITY.GET_TIME();
SELECT COUNT (*) INTO v_result FROM t1;
INSERT
INTO t_mbrc
SELECT f1.mbrc
, value - v_value
, DBMS_UTILITY.GET_TIME() - v_time
FROM v$mystat
WHERE statistic# = v_statistic#;
COMMIT;
END LOOP;
END;
/
아래 측정 결과를 살펴 보면 MBRC가 256인 경우 한 번의 I/O 작업에서 255.98개의 블록을 읽었지만 MBRC가 128일 때보다 수행 시간이 증가한 것을 확인할 수 있습니다. multiblock read의 성능은 플랫폼이 효율적으로 수행할 수 있는 최대 I/O 크기와 관련이 있으므로 특별한 경우가 아니라면 기본값을 사용하는 편이 바람직합니다. 아울러 MBRC가 512인 경우 한 번의 I/O 작업에서 설정한 MBRC보다 적은 426.59개의 블록을 읽은 것을 확인할 수 있습니다.
-- 8: 1431212 = user_tab_statistics.blocks
SELECT mbrc, requests, elapsed, ROUND (1431212 / requests, 2) AS bpr FROM t_mbrc;
MBRC REQUESTS ELAPSED BPR
---- -------- ------- -------
8 178890 885 8.00
16 89445 639 16.00
32 44726 372 32.00
64 22363 386 64.00
128 11182 350 127.99
256 5591 357 255.98
512 3355 319 426.59
1024 2237 379 639.79
2048 1119 260 1279.01
4096 1119 197 1279.01
10 rows selected.
아래 차트는 MBRC 별 요청 횟수를 보여 줍니다.
아래 차트는 MBRC 별 수행 시간을 보여줍니다.
비용 계산
_db_file_optimizer_read_count 파라미터가 multiblock read 비용 계산에 미치는 영향을 살펴보겠습니다. 기본값을 테스트하기 위해 DB에 다시 접속한 후 쿼리의 실행 계획을 생성하고, db_file_multiblock_read_count 파라미터를 64, 128, 256로 설정한 후 쿼리의 실행 계획을 생성하겠습니다. 생성된 실행 계획을 살펴보면 _db_file_optimizer_read_count 값이 증가하면 multiblock read의 비용(Cost)이 감소하는 것을 확인할 수 있습니다.
-- 9-1: _db_file_optimizer_read_count = 8
SELECT COUNT (*) FROM t1;
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 387K (1)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 10M| 387K (1)|
--------------------------------------------------------
-- 9-2: _db_file_optimizer_read_count = 64
ALTER SESSION SET db_file_multiblock_read_count = 64;
SELECT COUNT (*) FROM t1;
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 257K (1)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 10M| 257K (1)|
--------------------------------------------------------
-- 9-3: _db_file_optimizer_read_count = 128
ALTER SESSION SET db_file_multiblock_read_count = 128;
SELECT COUNT (*) FROM t1;
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 248K (1)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 10M| 248K (1)|
--------------------------------------------------------
-- 9-4: _db_file_optimizer_read_count = 256
ALTER SESSION SET db_file_multiblock_read_count = 256;
SELECT COUNT (*) FROM t1;
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 243K (1)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 10M| 243K (1)|
--------------------------------------------------------
비용을 계산하기 위해 시스템 통계가 저장된 sys.aux_stats$ 테이블을 조회하면 SREADTIM, MREADTIM, MBRC 값이 수집되지 않은 것을 확인할 수 있으며, 10053 트레이스에서 NOWORKLOAD 통계가 사용되는 것을 확인할 수 있습니다.
-- 10-1
SELECT pname, pval1
FROM sys.aux_stats$
WHERE sname = 'SYSSTATS_MAIN'
AND pname IN ('IOSEEKTIM', 'IOTFRSPEED', 'SREADTIM', 'MREADTIM', 'MBRC');
PNAME PVAL1
---------- -----
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
MBRC
5 rows selected.
-- 10-2: 10053 trace
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 5071 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
기본값의 multiblock read 비용은 아래와 같이 계산할 수 있습니다. (계산 = 387620, 10053 trace = 387622)
-- 11-1: 1431212 = user_tab_statistics.blocks
SREADTIM = IOSEEKTIM + (db_block_size / IOTFRSPEED) = 10 + (8192 / 4096) = 12
MREADTIM = IOSEEKTIM + (db_block_size * MBRC / IOTFRSPEED) = 10 + (8192 * 8 / 4096) = 26
Scan IO Cost per Block = (MREADTIM / SREADTIM) / MBRC = (26 / 12) / 8 = 0.270833
IO Cost = blocks below HWM * Scan IO Cost per Block = 1431212 * 0.270833 = 387620
-- 11-2: 10053 trace
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Table: T1 Alias: T1
Card: Original: 10000000.000000 Rounded: 10000000 Computed: 10000000.000000 Non Adjusted: 10000000.000000
Scan IO Cost (Disk) = 387622.000000
Scan CPU Cost (Disk) = 11692290385.280001
Total Scan IO Cost = 387622.000000 (scan (Disk))
= 387622.000000
Total Scan CPU Cost = 11692290385.280001 (scan (Disk))
= 11692290385.280001
Access Path: TableScan
Cost: 387814.160299 Resp: 387814.160299 Degree: 0
Cost_io: 387622.000000 Cost_cpu: 11692290385
Resp_io: 387622.000000 Resp_cpu: 11692290385
Best:: AccessPath: TableScan
Cost: 387814.160299 Degree: 1 Resp: 387814.160299 Card: 10000000.000000 Bytes: 0.000000
***************************************
MBRC에 따라 multiblock read의 비용은 아래와 같이 계산됩니다. db_file_multiblock_read_count 파라미터을 8보다 큰 값으로 설정하면 기본값보다 테이블 스캔을 우선하는 실행 계획이 생성될 수 있다는 점에 주의할 필요가 있습니다.
참고
- 정희락 - MBRC에 따른 physical read 성능
- Frits Hoogland - Extra huge database IOs
- Frits Hoogland - Extra huge database IOs, part 2
- Frits Hoogland - Extra huge database IOs, part 3
- Ron Ekins - How to Influence Physical IO sizes with Oracle MBRC
- Ron Ekins - How to reduce Linux Block Storage IO sizes
- Neil Chandler - Oracle Optimizer System Statistics
- Neil Chandler - Exadata System Statistics