LINK:
http://www.eygle.com/faq/db_file_multiblock_read_count&iacleio.htm
Initialization parameters DB_FILE_MULTIBLOCK_READ_COUNT affects the number of blocks read at once when performing a full table scan.
DB_FILE_MULTIBLOCK_READ_COUNT settings are affected by the maximum IO capability of OS, that is, if your system's hardware IO ability is limited, even if set up, DB_FILE_MULTIBLOCK_READ_COUNT is useless.
In theory, the largest DB_FILE_MULTIBLOCK_READ_COUNT and system IO capabilities should have the following relationships:
Max (DB_FILE_MULTIBLOCK_READ_COUNT) = maxosiosize / db_block_size
Of course, this max (DB_FILE_MULTIBLOCK_READ_COUNT) is also subject to Oracle, and the maximum DB_File_Multiblock_read_count value supported by Oracle is 128.
We can test Oracle under different systems via DB_FILE_MULTIBLOCK_READ_COUNT, and the maximum read data volume of the single IO can be read.
$ SQLPLUS "/ as sysdba" SQL * Plus: Release 10.1.0.2.0 - Production On WED AUG 11 23:43:52 2004 Copyright (C) 1982, 2004, Oracle. All Rights Reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit costwith the partitioning, OLAP AND DATA mining optionssys as sysdba on 11-aug-04> show parameter read_countname type value ------------------- ------------------------------------------------ -------- db_file_multiblock_read_count integer 16SYS AS SYSDBA on 11-AUG-04> create tablespace dfmbrc 2 datafile '/opt/oracle/oradata/eygle/dfmbrc.dbf' 3 size 20M extent management local uniform size 2M; Tablespace created.SYS AS SYSDBA on 11-AUG-04> create table t tablespace dfmbrc as select * from dba_objects; Table created.SYS AS SYSDBA on 11-AUG-04> insert into t select * from t; 9149 rows created.SYS AS Sysdba on 11-aug-04> / 18298 rows created.sys as sysdba on 11-aug-04> / 36596 rows created.sys as sysdba on 11-au G-04> commit; Commit complete.SYS AS SYSDBA on 11-AUG-04> alter session set db_file_multiblock_read_count = 1000; Session altered.SYS AS SYSDBA on 12-AUG-04> show parameter read_countNAME TYPE VALUE ------ ------------------------------------ -------------------- DB_FILE_MULTIBLOCK_READ_COUNT INTEGER 128SYS AS SYSDBA on 11-AUG-04> ALTER Session Set Events '10046 Trace Name Context Forever, Level 12'; session altered .Sys as sysdba on 11-aug-04> ALTER system flush buffer_cache; system altered.sys as sysdba on 11-aug-04> select count (*) from t; count (*) --------- - 73192SYS AS SYSDBA on 12-aug-04> @
GetTraceTrace_file_name ------------------------------------------------- ------------------------------- / OPT / Oracle / Soft / Eygle_ora_244432.trc $ CAT / OPT / ORACLE / SOFT / Eygle_ora_24432.trc | Grep Scaway # 26: Nam = 'DB File Scattered Read' ELA = 18267 P1 = 10 P2 = 10 P3 = 128Wait # 26: Nam = 'DB File Scattered Read' ELA = 8836 P1 = 10 P2 = 138 P3 = 127Wait # 26: nam = 'DB file scattered read' ELA = 8923 P1 = 10 p2 = 265 p3 = 128Wait # 26: Nam = 'db file scattered read' ELA = 8853 P1 = 10 p2 = 393 p3 = 128wait # 26 : nam = 'db file scattered read' ELA = 8985 P1 = 10 p2 = 521 P3 = 128Wait # 26: Nam = 'db file scattered read' ELA = 8997 P1 = 10 p2 = 649 p3 = 128Wait # 26: Nam = ' DB File Scattered Read 'ELA = 9096 P1 = 10 P2 = 777 P3 = 128Wait # 26: Nam =' DB File Scattered Read 'ELA = 583 P1 = 10 P2 = 905 P3 = 12 $ We can see, at the above test platform In the middle, Oracle can read 128 blocks each time IO, because block_size is 8K, that is, read 1M data each time. System platform is:
$ uname -asunos billing 5.8 generic_108528-23 Sun4u sparc sunw, ULTRA-4
Of course, how many blocks can be read, such as whether the storage is contiguous, such as whether the storage is continuous, whether the disk is divided by the strip, and the single IO reading of Oracle cannot span the extent boundary. Some platforms And the parameter settings for the operating system. You can test different platforms, and the Oracle's single IO can read the number of Blocks.
