Shulou(Shulou.com)06/01 Report--

In this issue, Xiaobian will bring you research and analysis on db_file_multiblock_read_count. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

The initialization parameter db_file_multiblock_read_count affects the number of blocks Oracle reads at once when performing a full table scan.

The db_file_multiblock_read_count setting is affected by the maximum IO capacity of the OS, that is, if your system has limited hardware IO capacity, even the largest db_file_multiblock_read_count setting is useless.

Theoretically, maximum db_file_multiblock_read_count and system IO capability should have the following relationship:

Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size

Of course, this Max(db_file_multiblock_read_count) is also limited by Oracle. Currently, the maximum db_file_multiblock_read_count value supported by Oracle is 128.

The data block is the most basic unit of reading and writing in Oracle, but the data required by the user is not the whole block, but the rows or columns in the block. When the user issues an SQL statement, the statement is parsed and executed, and the data fetching phase begins. In this phase, the server process first reads the data block where the row is located from the data file into the buffer cache. This process is called physical reading. Each block read counts as a physical read.

As far as I understand it, the server process may read as many blocks as possible to the buffer cache at a time, so does each block read count as a physical read? Or is every read (the number of blocks read depends on the db_file_multiblock_read_count parameter) a physical read?

In fact, when db_file_multiblock_read_count is used, if 16 blocks are read at a time, it is still calculated according to 16 physical reads in oracle, not according to one physical read. At the operating system level, it should be counted as one I/O request.

Below, we test the above situation through an experiment.

SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count integer 16

This value is actually the block of data to be read during a full table scan.

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

To test the requirements, a new table space is created, and a new data table is added. To view the block allocation of this table in the table space, the table is as follows:

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

Since this data segment is the first initial segment of the test table space, you can see that the first block of extent 0 starts from 9 #, 1- 2 #is used for the data file header, and 3- 8 #is the bitmap management information.

To further test the physical reading problem, verify this by reading an example data table.

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

According to pctfree 50 set when creating data segments, it can be estimated that about three lines of data can be stored in a block.

Below, insert test data.

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

It can be seen that the data segment occupies 21 data blocks under HWM, and 20 is displayed here because one block is the segment header, and 5 blocks out of 20 blocks are above 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

By tracing event 10046 at the session level, we get that SQL execution generates 21 physical reads at once.

The above is a small series of research and analysis on db_file_multiblock_read_count shared by everyone. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.