Effect of Non-Standard Block Sizes on Multiblock Reads

Non-Standard Block Sizes were introduced in Oracle 9i that allows database to have tablespaces with different block sizes. This feature was introduced to enable user to plug-in a tablespace from an OLTP production of, say 8k, to a datawarehouse database of, say 16k (Transportable Tablespace). This feature should not be used as a performance feature and tempt the dba's to create larger block size tablespaces so that it reads more number of rows in one I/O. There are many side effects to this and can cause issue in some or the other ways.

What will be the read size (number of multiblock reads) for a table residing in a non-standard block size tablespace ?

A Scenario.

db_file_multiblock_read_count : 8
db_block_size : 4096

Table TEST_16K created in 16k Block Size tablespace.
A Full table scan on TEST_16K table will make use of multiblock reads, but how many blocks will be read in a single I/O ?

For a table in a default block size, the read size will be 32k i.e.8 * 4096(db_file_multiblock_read_count * db_block_size). Db_file_multiblock_read_count=8 directs to read 8 blocks in a single I/O. Ofcourse, the caching effect and extent boundary will cause multiblock reads to turn out into single block reads or may be reads less than the 32k.

Generally, oracle negotiates Read Size with the O/S at Instance Startup. Hence, lets work on the scenario mentioned above :

Read Size will be (db_file_multiblock * db_block_size) = 8 * 4k = 32K. Based on this setting, Oracle has to limit the read sizes to 32K. Hence, if you read a table in 2k block size, the adjusted db_file_multiblock_read_count will be 16 (2 * 16 = 32k). Similarly, for a table in 16k block size, the adjusted multiblock reads will be 2 (16 * 2 = 32k).

SQL> show parameter block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 4096
db_file_multiblock_read_count integer 8

SQL> alter system set db_16k_cache_size=10m scope=spfile;

Shutdown and Startup the database

SQL> create tablespace ts_16k datafile 'C:\ORACLE\ORADATA\ORCL9I\TS_16K_01.dbf' size 100m 2 extent management local uniform size 1m 3 blocksize 16k;

SQL> create table test_16k tablespace ts_16k as select * from all_objects;
Table created.

SQL> exec dbms_stats.gather_table_stats(user,'TEST_16K');
PL/SQL procedure successfully completed.

SQL>alter session set events='10046 trace name context forever, level 12';

SQL>select object_name from test_16k where object_id=100654;

The Trace file shows that for db_file_scattered_read which means multiblock reads read 2 blocks in a single I/O.

EXEC #1:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=16171637327
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 13968 p1=11 p2=5 p3=1 <-- Caching Effect or Extent Boundary
WAIT #1: nam='db file scattered read' ela= 1306 p1=11 p2=6 p3=2 <===
WAIT #1: nam='db file scattered read' ela= 820 p1=11 p2=8 p3=2
WAIT #1: nam='db file scattered read' ela= 843 p1=11 p2=10 p3=2
WAIT #1: nam='db file scattered read' ela= 840 p1=11 p2=12 p3=2
WAIT #1: nam='db file scattered read' ela= 846 p1=11 p2=14 p3=2 multiblock reads
WAIT #1: nam='db file scattered read' ela= 844 p1=11 p2=16 p3=2
WAIT #1: nam='db file scattered read' ela= 801 p1=11 p2=18 p3=2
WAIT #1: nam='db file scattered read' ela= 842 p1=11 p2=20 p3=2 <===

Hence, above snippet from the trace file generated by 10046 event shows that the reads were limited to 32k based on the init.ora settings and the adjusted value for multiblock reads is derived from the default read size.

Happy Reading !

Regards
Vivek