MULTIBLOCK READS AND CACHED BLOCKS

Database, Tuning, Uncategorized, ,

 

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 an earlier post, I had  demonstrated that multiblock reads cannot span extent boundaries.
In this post, I will demonstrate that multiblock reads skip blocks which are already cached.

Overview:

– Db_block_size = 8k
– create table MBRC_CACHE with uniform extent size = 8
– Populate table with records for id = 1 to 60 such that each block contains records for one id only
– Create index on the table on id column
– Trace the following
. Read records for id = 2
. Perform full scan on the table

– Trace file shows that
to read records for id = 2
. Index segment header block is read
. Index leaf blocks are read
. Table block containing records for id = 2 is read

to perform FTS on the table
. Table segment header block is read
. Table block containing records for id =1 is read
. Table block containing records for id = 2 is skipped
. Remaining Table blocks containing records for id = 3 to 60 are read

Implementation:

>SQL> sho parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> sho parameter db_file_multi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8

SQL> sho parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS<

- create table MBRC_CACHE with uniform extent size = 8

SQL> drop table mbrc_cache purge;
        create table mbrc_cache (id number , text char(700));

- Populate table with records for id = 1 to 60 such that each block contains records for one id only

begin
for i in 1..60 loop
for j in 1..10 loop
insert into mbrc_cache values (i, 'txt'||j);
commit;
end loop;
commit;
end loop;
end;
/

– check that records for each id are placed in one block each

SQL>select id, dbms_rowid.rowid_block_number(rowid),count(*)
from mbrc_cache
group by id, dbms_rowid.rowid_block_number(rowid)
order by id, dbms_rowid.rowid_block_number(rowid);

ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
---------- ------------------------------------ ----------
1                                88809         10
2                                88810         10
3                                88811         10
4                                88812         10
5                                88813         10
6                                88814         10
7                                88815         10
8                                88816         10
9                                88817         10
10                                88818         10
11                                88819         10

...
56                                88872         10
57                                88873         10
58                                88874         10
59                                88875         10
60                                88876         10

- Create index on the table on id column and gather statistics

SQL>create index mbrc_cache_idx on mbrc_cache(id);

exec dbms_stats.gather_table_stats(USER, 'MBRC_CACHE', cascade=> true);

select table_name, num_rows, blocks from user_tables where table_name= 'MBRC_CACHE';

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
MBRC_CACHE                            600         60

– Find out object_id for mbrc_cache table and mbrc_)cache_idx index

SQL>col object_name for a30
select object_name, object_id from dba_objects where object_name like 'MBRC_CACHE%';

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
MBRC_CACHE                          75140
MBRC_CACHE_IDX                      75225

– Note that for table MBRC_CACHE, segment header is in block# 88808
— Note that for index MBRC_CACHE_IDX, segment header is in block# 88880

SQL> select segment_name, header_block from dba_segments where segment_name LIKE 'MBRC_CACHE%';

SEGMENT_NAME         HEADER_BLOCK
-------------------- ------------
MBRC_CACHE                  88808
MBRC_CACHE_IDX              88880

– Note that 8 blocks (88880 – 88888) are assigned to index mbrc_cache
— Block 88880 contains segment header
— Two index leaf blocks are located in block# 88881 and 88882

SQL> select SEGMENT_NAME, BLOCK_ID, BLOCKS from dba_extents where segment_name='MBRC_CACHE_IDX';

SEGMENT_NAME           BLOCK_ID     BLOCKS
——————– ———- ———-
MBRC_CACHE_IDX            88880          8

SQL> select index_name, leaf_blocks from user_indexes where index_name=’MBRC_CACHE_IDX';

INDEX_NAME                     LEAF_BLOCKS
—————————— ———–
MBRC_CACHE_IDX                           2

- Trace the following
. Read records for id = 2
. Perform full scan on the table

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);
select * from mbrc_cache where id=2;
select * from mbrc_cache ;

declare
cursor c1_cur  is select * from mbrc_cache where id=2;
v1_cur mbrc_cache%rowtype;
cursor c2_cur  is select * from mbrc_cache;
v2_cur mbrc_cache%rowtype;
exec dbms_monitor.session_trace_disable();

begin
open c1_cur;
fetch c1_cur into v1_cur;

loop
fetch c1_cur into v1_cur;
exit when c1_cur%NOTFOUND;
end loop ;
close c1_cur;
end;
/
exec dbms_monitor.session_trace_disable();

SQL> col name for a15
col value for a60
select name, value from v$diag_info where upper(name) like '%TRACE F%';

NAME            VALUE
--------------- ------------------------------------------------------------
Default Trace F c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_
ile             3828_mbrc.trc

– Read trace file

SQL> ho notepad c:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_3828_mbrc.trc

- Trace file (trimmed to show only read waits) shows that
to read records for id = 2
. Index segment header block is read
. Index leaf blocks are read
. Table block containing records for id = 2 is read

to perform FTS on the table
. Table segment header block is read
. Table block containing records for id =1 is read
. Table block containing records for id = 2 is skipped
. Remaining Table blocks containing records for id = 3 to 60 are read

=====================
PARSING IN CURSOR #1 len=39 dep=0 uid=0 oct=3 lid=0 tim=283016701180 hv=2279949904 ad=’7ff0da36078′ sqlid=’42bhbr63yajkh’
select * from mbrc_cache where id=2
END OF STMT
PARSE #1:c=0,e=1668,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3589681151,tim=283016701177
EXEC #1:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3589681151,tim=283016701337

– Read index mbrc_cache_idx (object_id  =75225 )
— Read two index leaf blocks  88881 and 88882 (as found earlier)

WAIT #1: nam=’db file sequential read’ ela= 1045 file#=1 block#=88881 blocks=1 obj#=75225 tim=283016702567
WAIT #1: nam=’db file sequential read’ ela= 20967 file#=1 block#=88882 blocks=1 obj#=75225 tim=283016723719

– Read table mbrc_cache (object_id = 75140)
— Read block 88810 contaning records for id = 2 (as found earlier)

WAIT #1: nam=’db file sequential read’ ela= 14929 file#=1 block#=88810 blocks=1 obj#=75140 tim=283016738901

WAIT #1: nam=’SQL*Net message from client’ ela= 72910 driver id=1111838976 #bytes=1 p3=0 obj#=75140 tim=283016812709
CLOSE #1:c=0,e=32,dep=0,type=0,tim=283016812906
=====================

PARSING IN CURSOR #2 len=29 dep=0 uid=0 oct=3 lid=0 tim=283016814397 hv=4285579899 ad=’7ff097aab18′ sqlid=’1qvcxnbzr1hmv’
select * from mbrc_cache
END OF STMT
PARSE #2:c=0,e=1425,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2256522414,tim=283016814394
EXEC #2:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2256522414,tim=283016814549

– During FTS, read segment header for table mbrc_cache (header block# = 88808 as found earlier)

WAIT #2: nam=’db file sequential read’ ela= 1843 file#=1 block#=88808 blocks=1 obj#=75140 tim=283016816604

– Read block 88809 containing rows for id = 1 ( as found earlier)

WAIT #2: nam=’db file sequential read’ ela= 384 file#=1 block#=88809 blocks=1 obj#=75140 tim=283016817170

– Skip block 88810 containing records for id=2 as that block has already been read into buffer
— Read 5 remaining blocks in the extent starting from block 88811

WAIT #2: nam=’db file scattered read’ ela= 22144 file#=1 block#=88811 blocks=5 obj#=75140 tim=283016947620

– Read subdsequent extents in units of 8 blocks

WAIT #2: nam=’db file scattered read’ ela= 302 file#=1 block#=88816 blocks=8 obj#=75140 tim=283017221664

WAIT #2: nam=’db file scattered read’ ela= 33 file#=1 block#=88824 blocks=8 obj#=75140 tim=283017573336

WAIT #2: nam=’db file scattered read’ ela= 17389 file#=1 block#=88832 blocks=8 obj#=75140 tim=283017918715

WAIT #2: nam=’db file scattered read’ ela= 496 file#=1 block#=88840 blocks=8 obj#=75140 tim=283018212031

WAIT #2: nam=’db file scattered read’ ela= 28 file#=1 block#=88848 blocks=8 obj#=75140 tim=283018477971

WAIT #2: nam=’db file scattered read’ ela= 31 file#=1 block#=88856 blocks=8 obj#=75140 tim=283018786018

– Read 5 blocks  from last extent
— Last block 88876 contains records for id = 60 (as found earlier)
— Read 4 blocks ( 88872 – 88876) containing data
— Read one more block to ensure that there are no more blocks containing data

WAIT #2: nam=’db file scattered read’ ela= 28 file#=1 block#=88872 blocks=5 obj#=75140 tim=283019045215

WAIT #2: nam=’SQL*Net message from client’ ela= 54378 driver id=1111838976 #bytes=1 p3=0 obj#=75140 tim=283019256210
CLOSE #2:c=0,e=25,dep=0,type=0,tim=283019260582

=====================

Conclusion:

– 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.

References:

Pro Oracle SQL by Karen Morton, Kerry Osborne,  Robyn Sands, Riyaz and Jared Still

Oracle Documentation

————————————————————————————————-

Related links:

Home

Tuning Index

Oracle always full scans tables smaller than DBFMRC:  A myth

DB_FILE_MULTIBLOCK_READ_COUNT And Extent Size

 

——————–

 

2 thoughts on “MULTIBLOCK READS AND CACHED BLOCKS”

Leave a Reply to Anju Garg Cancel reply