OCP Blog
Super DBA
DB_FILE_MULTIBLOCK_READ_COUNT parameter
by Vazha Mantua Friday, July 8, 2011 4:54 PM

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.
The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count,
and whether parallel execution is being utilized for the operation.


Let see example:

Set db_file_multiblock_read_count=1

1. alter system set db_file_multiblock_read_count=1
2. alter system flush buffer_cache

And now we watch sql_plan for following statment "select /*+ full(a) */count(*) from TABLE_1 a"


SQL> explain plan for
select /*+ full(a) */count(*) from TABLE_1 a
;

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 635235748
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21893 (2)| 00:05:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TABLE_1 | 8567K| 21893 (2)| 00:05:22 |
--------------------------------------------------------------------------

 

Time of execution statement is 387 second

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


Now change db_file_multiblock_read_count

1.alter system set db_file_multiblock_read_count=64
2. Run statement for collecting new system statistics:
begin
dbms_stats.gather_system_stats();
end;

alter system flush buffer_cache


SQL> explain plan for
2 select /*+ full(a) */count(*) from TABLE_1 a;

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 635235748
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23709 (3)| 00:03:57 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TABLE_1 | 8567K| 23709 (3)| 00:03:57 |
--------------------------------------------------------------------------


Time of execution statement is 69 second


Result told that in our case increasing of db_file_multiblock_read_count decrease runtime of statement but increase cost

The value of db_file_multiblock_read_count can have a significant impact on the overall database performance and it is not easy for the administrator to determine its most appropriate value.

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.

Before 10g R2, DBA's used the db_file_multiblock_read_count initialization parameter to tell Oracle how many block to retrieve in the single I/O operation.

Before Oracle10g R2, the permitted values for db_file_multiblock_read_count were platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.

Tags:

Comments (1) -

6/3/2012 2:26:23 AM #

Directory Submission Service

It specifies the maximum number of blocks read in one I/O operation during a sequential scan.

Directory Submission Service United Kingdom

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  October 2018  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

TextBox