db_file_multiblock_read_count的研究

转载自：http://blog.itpub.net/9399028/viewspace-681876/

初始化参数db_file_multiblock_read_count 影响Oracle在执行全表扫描时一次读取的block的数量。

  db_file_multiblock_read_count的设置要受OS最大IO能力影响，也就是说，如果你系统的硬件IO能力有限，即使设置再大的db_file_multiblock_read_count也是没有用的。

  理论上，最大db_file_multiblock_read_count和系统IO能力应该有如下关系：

      Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size

  当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制，目前Oracle所支持的最大db_file_multiblock_read_count 值为128。

  data block是oracle最基本的读写单位，但用户所需要的数据，并不是整个块，而是块中的行或列。当用户发出SQL语句时，此语句被解析执行完毕，就开始了数据的抓取阶段，在此阶段，服务器进程会先将行所在的数据块从数据文件中读入buffer cache，这个过程叫做物理读。每读取一个块，就算一次物理读。

  以我的理解，server process可能会尽可能一次多读一些相关行所属的block到buffer cache中，那么每读一个块都算做一个物理读吗？还是说每读一次（读的block数量和参数db_file_multiblock_read_count有关）就算一个物理读。

  实际上，在使用db_file_multiblock_read_count的时候，一次如果读１６个块，在oracle中仍按照１６次物理读计算，并不按一次物理读计算。在操作系统级应该按一次Ｉ／Ｏ请求来计算。

  下面，我们通过一个试验来验证以上情况。

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

这个值实际上是一次全表扫描的时要读取的数据块。

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oracle/database/orcl/users01.dbf
/oracle/database/orcl/sysaux01.dbf
/oracle/database/orcl/undotbs01.dbf
/oracle/database/orcl/system01.dbf
/oracle/database/orcl/DAT_DB
/oracle/database/orcl/xb_db

6 rows selected

SQL> create tablespace test
  2   datafile '/oracle/database/orcl/testdb.dbf' size 10M
  3   extent management local uniform. size 64K
  4   segment space management manual;

Tablespace created

SQL> create table first_table(id int,name varchar(40)) tablespace test;

Table created

为了测试需要，创建了一个新的表空间，并新增了一个数据表，查看表空间中该table的block分配情况如下：

SQL> select extent_id, block_id, blocks
  2    from dba_extents
  3   where wner = 'MS'
  4     and segment_name = upper('first_table');

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9          8

由于这个数据段是test表空间的第一个初始段，可以看到extent 0的第一个block是从9#开始，1-2#用于数据文件头，3-8#就是位图管理的信息，这里就不多介绍了。

为了进一步测试物理读的问题，通过举例数据表的读取来验证一下。

SQL> create table data_table(id int,name char(1000))
  2   storage(freelists 1 freelist groups 1)
  3   pctfree 50
  4   pctused 50
  5   tablespace test;

Table created

SQL> show parameter db_block_size;

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

根据创建数据段时设置的pctfree 50可以推算，一个block中可以存储大约三行的数据。

下面，插入测试数据。

SQL> begin
  2    for i in 1 .. 47 loop
  3      insert into data_table values (i, 'just test data block allocate');
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

SQL>
SQL> select substr(rowid, 1, 15) blockID, count(0)
  2    from data_table
  3   group by substr(rowid, 1, 15);

BLOCKID                          COUNT(0)
------------------------------ ----------
AAAuRMAAHAAAAAV                         3
AAAuRMAAHAAAAAZ                         3
AAAuRMAAHAAAAAX                         3
AAAuRMAAHAAAAAh                         2
AAAuRMAAHAAAAAS                         3
AAAuRMAAHAAAAAd                         3
AAAuRMAAHAAAAAb                         3
AAAuRMAAHAAAAAW                         3
AAAuRMAAHAAAAAf                         3
AAAuRMAAHAAAAAc                         3
AAAuRMAAHAAAAAT                         3
AAAuRMAAHAAAAAU                         3
AAAuRMAAHAAAAAY                         3
AAAuRMAAHAAAAAa                         3
AAAuRMAAHAAAAAe                         3
AAAuRMAAHAAAAAg                         3

16 rows selected

SQL> analyze table data_table compute statistics;

Table analyzed

SQL>
SQL> select num_rows, blocks, empty_blocks, num_freelist_blocks
  2    from dba_tables
  3   where wner = 'MS'
  4     and table_name = upper('data_table');

  NUM_ROWS     BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ---------- ------------ -------------------
        47         20            3                   5

可以看到数据段在HWM下共占有21个数据块，这里显示20是因为有一个块是段头，20个block中有5个block在freelist上面。

SQL> select file_id, extent_id, block_id, blocks
  2    from dba_extents
  3   where wner = 'MS'
  4     and segment_name = upper('data_table');

   FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         7          0         17          8
         7          1         25          8
         7          2         33          8

SQL> alter session set events 'immediate trace name flush_cache';

Session altered

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

Session altered

SQL> select id, name from data_table;

。。。

47 rows selected

SQL> alter session set events '10046 trace name context off';

Session altered

通过session级别的trace event 10046，得到SQL执行一次产生了21次物理读。