首页 » ORACLE 9i-23c » DB_FILE_MULTIBLOCK_READ_COUNT parameter

DB_FILE_MULTIBLOCK_READ_COUNT parameter

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during

a sequential scan,controls the number of blocks pre-fetched into the buffer cache during scan operations, such as full table scan and index fast full scan.

The maximum value is the operating system’s maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter With the I/O costing model, the effective multiblock read count value is derived directly from the db_file_multiblock_read_count parameter. However, the CBO doesn’t use the actual db_file_multiblock_read_count value as the CBO knows that most multiblock read operations are unlikely to read the maximum possible number of blocks.

when using CPU costing model, don’t set the db_file_multiblock_read_count parameter, let Oracle determine the maximum optimal size for you and ensure the system statistics are accurate and reflect the actual average MBRC size in your database environment.

Starting in Oracle 10g release 2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.Oracle Database 10g Release 2 automatically selects the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.

In 10gr2 and beyond, the db_file_multiblock_read_count is not used to estimate the average number of blocks read and a separate metric for the estimated number of actual block

reads. Instead, the optimizer computes two new values, one for optimizer costing and another for the number of I/O requests. multi block read count.

_db_file_optimizer_read_count: mbrc for optimizer
_db_file_exec_read_count: mbrc for execution


SQL> create table test(id int,addre varchar2(2000));
Table created.

SQL> insert into test
  2  select rownum r,lpad(rownum,2000,'o') nm from dual connect by rownum<=10000;

10000 rows created.

exec dbms_stats.gather_table_stats(user,'TEST',null,20); 

SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx and nam.ksppinm like '%db_file_%' order by 1;

NAME                                               VALUE
-------------------------------------------------- --------------------
_db_file_direct_io_count                           1048576
_db_file_exec_read_count                           8
_db_file_format_io_buffers                         4
_db_file_noncontig_mblock_read_count               11
_db_file_optimizer_read_count                      8
db_file_multiblock_read_count                      8
db_file_name_convert
db_files                                           200

8 rows selected.

SQL> explain plan for select /*testsql*/ * from test where id=10000;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  2004 |   745   (1)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |  2004 |   745   (1)| 00:00:09 |
--------------------------------------------------------------------------

SQL> conn / as sysdba
Connected.
SQL> alter system set db_file_multiblock_read_count=64;
System altered.

SQL> conn anbob/anbob
Connected.
SQL> explain plan for select /*testsql*/ * from test where id=10000;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  2004 |   745   (1)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |  2004 |   745   (1)| 00:00:09 |
--------------------------------------------------------------------------

Oops!difference MBRC,same Cost

This absolutely seems like an optimizer bug.

The interesting thing is that Oracle accepts the changed mbrc value when executing multiblock read. This means that

Optimizer ignores _db_file_optimizer_read_count change at system level.
But query executor uses the changed _db_file_exec_read_count.


Solutions are

1,Session level changes are applied successfully

2,"Change another (meaningless) system level optimizer parameter along with db_file_multiblock_read_count" ,I tried,but no  effect

Following test case demonstrates the solution.

SQL> alter session set db_file_multiblock_read_count=64;

Session altered.

SQL> explain plan for select /*testsql*/ * from test where id=10000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  2004 |   613   (1)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |  2004 |   613   (1)| 00:00:08 |
--------------------------------------------------------------------------


SQL> alter session set db_file_multiblock_read_count=8;
Session altered.

SQL> explain plan for select /*testsql*/ * from test where id=10000;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  2004 |   922   (1)| 00:00:12 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |  2004 |   922   (1)| 00:00:12 |
--------------------------------------------------------------------------


打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Cavansir | #1
    2012-11-01 at 06:00

    I was drawn by the hontesy of what you write