A full scan operation on a table reads multiple blocks in one I/O as opposed to single block reads which read only one block in one I/O. The number of blocks read in one multiblock I/O can range anywhere from one to the number of blocks specified in the db_file_multiblock_read_count parameter. For example, if the parameter is set to 64 and there are 640 blocks in the table, the least no. of I/O’s required to get all the blocks = 640/64 = 10.
The no. of I/O’s could be more than one due to the following limitations on multiblock read calls:
– Multiblock reads cannot span extent boundaries i.e.
if db_file_multiblock_read_count = 64 and extent size = 8 blocks, a multiblock read can read a maximum of 8 blocks only in one I/O.
– If a requested block is already in the buffer cache, it will not be read again as part of the multiblock read. Oracle will simply read the blocks up to those not already in memory, then issue another read call that skips those blocks to read the rest. For example,
let’s say db_file_multiblock_read_count = 64 and
the range of blocks to be read is between block number 1 and 64. If block no. 32 is already available in the buffer cache, first multiblock read reads would be done for blocks 1 to 31 and subsequent read will read blocks from block 33 to 64.
• Multiblock reads cannot exceed the operating system limit for multiblock read size
In this post, I will demonstrate that multiblock reads cannot span extent boundaries.
In my next post, I will demonstrate that multiblock reads skip the bocks which already cached.
Overview:
– Db_block_size = 8k
– Set db_file_multiblock_read_count = 64
– create two tables with identical data :
. table mbrc_ext_64k with uniform extent size = 8
. table mbrc_ext_512k with uniform extent size = 64
. table mbrc_ext_1024k with uniform extent size = 128
– Trace FTS on both the tables and check the trace file
– Trace file shows that
. I/O’s made to mbrc_ext_64k are of size 8 blocks (=extent size)
. I/O’s made to mbrc_ext_512k are of size 64 blocks(= db_file_multiblock_read_count )
. I/O’s made to mbrc_ext_1024k are of size 64 blocks(= db_file_multiblock_read_count )
Implementation:
– check that db_block_size = 8k
SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------
db_block_size integer 8192
— Set db_file_multiblock_read_count = 64
SQL> alter system set db_file_multiblock_read_count =64
sho parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- -----------------
db_file_multiblock_read_count integer 64
— check that optimizer_mode = all_rows
SQL> sho parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- -----------------
optimizer_mode string ALL_ROWS
— Create tablespaces
. tbs_64k – Uniform Extent size = 64K = 8 blocks
. tbs_512k – Uniform Extent size = 512K = 64 blocks
. tbs_1024k- Uniform Extent size = 512K = 128 blocks
SQL>drop tablespace tbs_64k including contents and datafiles;
create tablespace tbs_64k datafile 'C:\APP\mbrc8k.dbf' size 100m reuse autoextend on
extent management local uniform size 64k segment space management manual;
drop tablespace tbs_512k including contents and datafiles;
create tablespace tbs_512k datafile 'C:\APP\mbrc512k.dbf' size 100m reuse autoextend on
extent management local uniform size 512k segment space management manual;
drop tablespace tbs_1024k including contents and datafiles;
create tablespace tbs_1024k datafile 'C:\APP\mbrc1024k.dbf' size 100m reuse autoextend on
extent management local uniform size 1024k segment space management manual;
– create three tables with identical data :
. table mbrc_ext_64k in tbs_64k with uniform extent size = 8 blocks
. table mbrc_ext_512k in tbs_512k with uniform extent size = 64 blocks
. table mbrc_ext_1024k in tbs_1024k with uniform extent size = 128 blocks
drop table mbrc_ext_64k purge;
create table mbrc_ext_64k tablespace tbs_64k as select * from sh.sales where rownum<=50000;
drop table mbrc_ext_512k purge;
create table mbrc_ext_512k tablespace tbs_512k as select * from sh.sales where rownum<=50000;
drop table mbrc_ext_1024k purge;
create table mbrc_ext_1024k tablespace tbs_1024k as select * from sh.sales where rownum<=50000;
— Gather statistics
exec dbms_stats.gather_table_stats(USER, 'MBRC_EXT_64K', cascade=> true);
exec dbms_stats.gather_table_stats(USER, 'MBRC_EXT_512K', cascade=> true);
exec dbms_stats.gather_table_stats(USER, 'MBRC_EXT_1024K', cascade=> true);
— check that extent size for
. MBRC_EXT_64K = 8
. MBRC_EXT_512K = 64
. MBRC_EXT_512K = 128
SQL>col segment_name for a20
select segment_name, blocks, count(*)
from dba_extents
where segment_name like 'MBRC_EXT_%K'
group by segment_name, blocks;
SEGMENT_NAME BLOCKS COUNT(*)
-------------------- ---------- ----------
MBRC_EXT_1024K 128 2
MBRC_EXT_64K 8 31
MBRC_EXT_512K 64 4
— Find out object_id’s of the tables
SQL>col object_name for a30
select object_name, object_id from dba_objects where object_name like 'MBRC_EXT_%K%';
OBJECT_NAME OBJECT_ID
------------------------------ ----------
MBRC_EXT_1024K 75139
MBRC_EXT_512K 75131
MBRC_EXT_64K 75130
— Enable tracing and per Full table scan of the tables
conn / as sysdba
alter system flush buffer_cache;
alter session set tracefile_identifier = 'mbrc';
set serveroutput off
exec dbms_monitor.session_trace_enable(waits=> true);
declare
cursor c1_cur is select * from mbrc_ext_64k;
cursor c2_cur is select * from mbrc_ext_512k;
cursor c3_cur is select * from mbrc_ext_1024k;
v1_cur mbrc_ext_64k%rowtype;
v2_cur mbrc_ext_512k%rowtype;
v3_cur mbrc_ext_1024k%rowtype;
begin
open c1_cur;
loop
fetch c1_cur into v1_cur;
exit when c1_cur%NOTFOUND;
end loop ;
close c1_cur;
open c2_cur;
loop
fetch c2_cur into v2_cur;
exit when c2_cur%NOTFOUND;
end loop ;
close c2_cur;
open c3_cur;
loop
fetch c3_cur into v3_cur;
exit when c3_cur%NOTFOUND;
end loop ;
close c3_cur;
end;
/
exec dbms_monitor.session_trace_disable();
— Find out name of trace file generated
SQL>col value for a80
select name, value from v$diag_info where upper(name) like '%TRACE F%';
NAME VALUE
-------------------- -------------------------------------------------------------------------
Default Trace File c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_5476_mbrc.trc
— Read the trace file
SQL>ho notepad c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_5476_mbrc.trc
=====================
In MBRC_EXT_64K (object_id = 75130)
Single block read identified by wait event ‘db file sequential read’
. Reads block# 128
— Let’s verify that block# 128 contains segment header
SQL> select segment_name, header_block from dba_segments where segment_name = 'MBRC_EXT_64k';
SEGMENT_NAME HEADER_BLOCK
-------------------- ------------
MBRC_EXT_64K 128
First multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 129 (Block next to segment header)
.No. of blocks read = 7 (blocks=7= remainig blocks in the extent)
Subsequent multiblock reads (identified by wait event ‘db file scattered read’)
. from block# 136 (129+7) onwards
. No. of blocks read = 8 (blocks = 8)
Last multiblock read (identified by wait event ‘db file scattered read’)
. starting at block# 368
. No. of blocks read = 2 (blocks = 2 = remaining blocks containing data)
Last block# read = 368 + 2 (in last wait, starting block = 368 , blocks read = 2)
= 370
First block# read (after segment header) = 129
Total no. of blocks scanned = 370 – 129
= 241
— Let’s verify that this no. matches the No. of blocks containing data as stored in data dictionary
SQL>select table_name, blocks from user_tables where table_name = 'MBRC_EXT_64K';
TABLE_NAME BLOCKS
------------------------------ ----------
MBRC_EXT_64K 241
=====================
PARSING IN CURSOR #2 len=26 dep=1 uid=0 oct=3 lid=0 tim=249360274955 hv=1652364071 ad=’7ff09780df8′ sqlid=’d3ubygdj7u4t7′
SELECT * FROM MBRC_EXT_64K
END OF STMT
PARSE #2:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3512697650,tim=249360274953
EXEC #2:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3512697650,tim=249360275088
WAIT #2: nam=’Disk file operations I/O’ ela= 211 FileOperation=2 fileno=7 filetype=2 obj#=75130 tim=249360275427
WAIT #2: nam=‘db file sequential read’ ela= 22023 file#=7 block#=128 blocks=1 obj#=75130 tim=249360297493
WAIT #2: nam=‘db file scattered read’ ela= 1305 file#=7 block#=129 blocks=7 obj#=75130 tim=249360299150
WAIT #2: nam=‘db file scattered read‘ ela= 951 file#=7 block#=136 blocks=8 obj#=75130 tim=249360386033
WAIT #2: nam=‘db file scattered read’ ela= 478 file#=7 block#=144 blocks=8 obj#=75130 tim=249360487978
WAIT #2: nam=‘db file scattered read‘ ela= 459 file#=7 block#=152 blocks=8 obj#=75130 tim=249360584467
….
…
WAIT #2: nam=’db file scattered read‘ ela= 213 file#=7 block#=368 blocks=2 obj#=75130 tim=249362258692
STAT #2 id=1 cnt=50000 pid=0 pos=1 obj=75130 op=’TABLE ACCESS FULL MBRC_EXT_64K (cr=50006 pr=242 pw=0 time=251918 us cost=64 size=1450000 card=50000)’
CLOSE #2:c=0,e=4,dep=1,type=3,tim=249362277148
=====================
=====================
In MBRC_EXT_512K (object_id = 75131)
Single block read identified by wait event ‘db file sequential read’
. Reads block# 128
— Let’s verify that block# 128 contains segment header
SQL> select segment_name, header_block from dba_segments where segment_name = 'MBRC_EXT_512k';
SEGMENT_NAME HEADER_BLOCK
-------------------- ------------
MBRC_EXT_512K 128
First multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 129 (Block next to segment header)
.No. of blocks read = 63 (blocks=63 = remainig blocks in the extent)
Subsequent multiblock reads (identified by wait event ‘db file scattered read’)
. from block# 192 (129+63) onwards
. No. of blocks read = 64 (blocks = 64)
Last multiblock read (identified by wait event ‘db file scattered read’)
. starting at block# 320
. No. of blocks read = 50 (blocks = 50 = remaining blocks containing data)
Last block# read = 320 + 50 (in last wait, starting block = 320 , blocks read = 50)
= 370
First block# read (after segment header) = 129
Total no. of blocks scanned = 370 – 129
= 241
— Let’s verify that this no. matches the No. of blocks containing data as stored in data dictionary
SQL>select table_name, blocks from user_tables where table_name = 'MBRC_EXT_512K';
TABLE_NAME BLOCKS
------------------------------ ----------
MBRC_EXT_512K 241
=====================
PARSING IN CURSOR #2 len=27 dep=1 uid=0 oct=3 lid=0 tim=249362277315 hv=3723803804 ad=’7ff097d1ec8′ sqlid=’grmbfwvfz9g4w’
SELECT * FROM MBRC_EXT_512K
END OF STMT
PARSE #2:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=748809365,tim=249362277313
EXEC #2:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=748809365,tim=249362277446
WAIT #2: nam=’Disk file operations I/O’ ela= 191 FileOperation=2 fileno=8 filetype=2 obj#=75131 tim=249362277741
WAIT #2: nam=’db file sequential read‘ ela= 18302 file#=8 block#=128 blocks=1 obj#=75131 tim=249362296085
WAIT #2: nam=‘db file scattered read’ ela= 4297 file#=8 block#=129 blocks=63 obj#=75131 tim=249362300903
WAIT #2: nam=‘db file scattered read‘ ela= 2675 file#=8 block#=192 blocks=64 obj#=75131 tim=249362851282
WAIT #2: nam=‘db file scattered read‘ ela= 3372 file#=8 block#=256 blocks=64 obj#=75131 tim=249363235459
WAIT #2: nam=‘db file scattered read‘ ela= 2651 file#=8 block#=320 blocks=50 obj#=75131 tim=249363612810
STAT #2 id=1 cnt=50000 pid=0 pos=1 obj=75131 op=’TABLE ACCESS FULL MBRC_EXT_512K (cr=50003 pr=242 pw=0 time=192560 us cost=64 size=1450000 card=50000)’
CLOSE #2:c=0,e=2,dep=1,type=3,tim=249363956416
EXEC #1:c=3634823,e=3681748,p=484,cr=100009,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=249363956444
WAIT #1: nam=’SQL*Net message to client’ ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=75131 tim=249363956494
WAIT #1: nam=’SQL*Net message from client’ ela= 2159 driver id=1111838976 #bytes=1 p3=0 obj#=75131 tim=249363958679
CLOSE #1:c=0,e=38,dep=0,type=0,tim=249363958919
=====================
=====================
=====================
In MBRC_EXT_1024K (object_id = 75139)
Single block read identified by wait event ‘db file sequential read’
. Reads block# 128
— Let’s verify that block# 128 contains segment header
SQL> select segment_name, header_block from dba_segments where segment_name = 'MBRC_EXT_1024k';
SEGMENT_NAME HEADER_BLOCK
-------------------- ------------
MBRC_EXT_1024K 128
First multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 129 (Block next to segment header)
.No. of blocks read = 64 (blocks=64 = db_file_multiblock_count since extent contains 128 blocks and there are still 127 blocks left in the extent)
Second multiblock read (identified by wait event ‘db file scattered read’)
.starting at block# 163 (129 + 64)
.No. of blocks read = 63 (blocks=63 = remaining blocks in the extent)
Subsequent multiblock reads (identified by wait event ‘db file scattered read’)
. from block# 256 (193 +63) onwards
. No. of blocks read = 64 (blocks = 64)
Last multiblock read (identified by wait event ‘db file scattered read’)
. starting at block# 320
. No. of blocks read = 50 (blocks = 50 = remaining blocks containing data)
Last block# read = 320 + 50 (in last wait, starting block = 320 , blocks read = 50)
= 370
First block# read (after segment header) = 129
Total no. of blocks scanned = 370 – 129
= 241
— Let’s verify that this no. matches the No. of blocks containing data as stored in data dictionary
SQL>select table_name, blocks from user_tables where table_name = 'MBRC_EXT_1024K';
TABLE_NAME BLOCKS
------------------------------ ----------
MBRC_EXT_1024K 241
==================================
PARSING IN CURSOR #2 len=28 dep=1 uid=0 oct=3 lid=0 tim=262780042519 hv=4010235831 ad=’7ff0d84e9e8′ sqlid=’7rs934mrhfpxr’
SELECT * FROM MBRC_EXT_1024K
END OF STMT
PARSE #2:c=15601,e=4201,p=0,cr=21,cu=0,mis=1,r=0,dep=1,og=1,plh=3425801585,tim=262780042518
EXEC #2:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3425801585,tim=262780042611
WAIT #2: nam=’Disk file operations I/O’ ela= 169 FileOperation=2 fileno=9 filetype=2 obj#=75139 tim=262780042872
WAIT #2: nam=‘db file sequential read‘ ela= 6916 file#=9 block#=128 blocks=1 obj#=75139 tim=262780049822
WAIT #2: nam=’db file scattered read‘ ela= 3837 file#=9 block#=129 blocks=64 obj#=75139 tim=262780053909
WAIT #2: nam=‘db file scattered read‘ ela= 2422 file#=9 block#=193 blocks=63 obj#=75139 tim=262780770697
WAIT #2: nam=’db file scattered read‘ ela= 2005 file#=9 block#=256 blocks=64 obj#=75139 tim=262781424869
WAIT #2: nam=‘db file scattered read‘ ela= 40794 file#=9 block#=320 blocks=50 obj#=75139 tim=262782155450
STAT #2 id=1 cnt=50000 pid=0 pos=1 obj=75139 op=’TABLE ACCESS FULL MBRC_EXT_1024K (cr=50003 pr=242 pw=0 time=274738 us cost=64 size=1450000 card=50000)’
CLOSE #2:c=0,e=3,dep=1,type=3,tim=262782568794
EXEC #1:c=7020045,e=7580902,p=732,cr=150081,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=262782568832
WAIT #1: nam=’SQL*Net message to client’ ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=75139 tim=262782568901
WAIT #1: nam=’SQL*Net message from client’ ela= 2776 driver id=1111838976 #bytes=1 p3=0 obj#=75139 tim=262782571712
CLOSE #1:c=0,e=44,dep=0,type=0,tim=262782571842
=========================
Conclusion:
– During FTS, segment header is accessed using single block read first to get the extent map.
– During first multiblock read,
if remaining blocks in the extent < db_file_multiblock_read_count
remaining blocks in the first extent (excluding segmnet header block) are read.
else (remaining blocks in the extent >= db_file_multiblock_read_count
blocks = db_file_multiblock_read_count are read
– During subsequent multiblock reads,
if remaining blocks in the extent < db_file_multiblock_read_count
remaining blocks in the extent are read.
else (remaining blocks in the extent >= db_file_multiblock_read_count
blocks = db_file_multiblock_read_count are read
– During last multiblock read, remaining blocks containing data are read.
– If extent size <= db_file_multiblock_read_count
No. of blocks read in one I/O = No. of blocks in one extent
– If extent size > db_file_multiblock_read_count
No. of blocks read in one I/O = least (db_file_multiblock_read_count, no. of blocks containing data)
Hence, we have been able to verify that multiblock reads cannot span extents and maximum no. of blocks that can be read in one I/O = db_file_multiblock_read_count
References:
https://forums.oracle.com/forums/thread.jspa?messageID=11051591#11051591
———————————————————————————
Related links:
Home
Tuning Index
Multiblock Reads And Cached Blocks
Oracle Always Full Scans Tables Smaller Than DFMBRC: A Myth
Like this:
Like Loading...