What does the Oracle Documentation mentions about the initialization parameter db_file_multiblock_read_count
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.
As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.
Not mentionned here is that there is a relationship between the cost of a full table scan, system statistics and db_file_multiblock_read_count. Any new Oracle 10G database has by default the no workload statistics. With the dbms stats procedure we can gather workload statistics. If system statistics are not gathered either are removed then the cost of the full table scan is derived from the value for db_file_multiblock_read_count.
Thus
1. Without system stats and if we increase the value for db_file_multiblock_read_count the cost for the FTS decreases
2. Without system stats and if we decrease the value for db_file_multiblock_read_count the cost for the FTS increases
SQL> begin
2 dbms_stats.delete_system_stats;
3 end;
4 /
SQL> select * from sys.aux_stats$;
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
09-25-2010 17:23
SYSSTATS_INFO DSTOP
09-25-2010 17:23
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 266.513
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
SQL> alter session set db_file_multiblock_read_count=16;
Session altered.
SQL> set autotrace traceonly explain;
SQL> select /*+ FULL */ * from pm.t_exchangerate_er;
Execution Plan
---------------------------------------------------------------------------------------------------------------
Plan hash value: 2138012165
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1701K| 64M| 2826 (5)| 00:00:34 |
| 1 | TABLE ACCESS FULL | T_EXCHANGERATE_ER | 1701K| 64M| 2826 (5)| 00:00:34 |
---------------------------------------------------------------------------------------------------------------
SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
SQL> select /*+ FULL */ * from pm.t_exchangerate_er;
Execution Plan
----------------------------------------------------------
Plan hash value: 2138012165
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1701K| 64M| 2506 (6)| 00:00:31 |
| 1 | TABLE ACCESS FULL | T_EXCHANGERATE_ER | 1701K| 64M| 2506 (6)| 00:00:31 |
---------------------------------------------------------------------------------------------------------------
We gather system statistics
SQL> begin
2 dbms_stats.gather_system_stats('start');
3 end;
4 /
PL/SQL procedure successfully completed.
We wait for an hour or two, we assure normal database processing is going on. After this period of representative database processing we stop the gather system stats.
SQL> begin
2 dbms_stats.gather_system_stats('stop');
3 end;
4 /
PL/SQL procedure successfully completed.
We query the view aux_stats$ and notice there are entries for SREADTIM, MREADTIM, MBRC.
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
09-26-2010 18:51
SYSSTATS_INFO DSTOP
09-26-2010 20:50
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 266.513
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 15398.365
SYSSTATS_MAIN MREADTIM 26972.559
SYSSTATS_MAIN CPUSPEED 828
SYSSTATS_MAIN MBRC 66
SYSSTATS_MAIN MAXTHR 31055872
SYSSTATS_MAIN SLAVETHR 8192
13 rows selected.
We notice that no matter what the value for db_file_multiblock_read_count is the cost of the ful table scan does not differ anymore. The cost is derived from sys.aux$_stats.MBRC.
SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
SQL> set autotrace traceonly explain;
SQL> select /*+ FULL */ * from pm.t_exchangerate_er;
Execution Plan
----------------------------------------------------------
Plan hash value: 2138012165
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1701K| 64M| 327 (0)| 01:23:56 |
| 1 | TABLE ACCESS FULL | T_EXCHANGERATE_ER | 1701K| 64M| 327 (0)| 01:23:56 |
------------------------------------------------------------------------------------------------------------
SQL> set autotrace off;
SQL> alter session set db_file_multiblock_read_count=16;
Session altered.
SQL> set autotrace traceonly explain;
SQL> select /*+ FULL */ * from pm.t_exchangerate_er;
Execution Plan
----------------------------------------------------------
Plan hash value: 2138012165
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1701K| 64M| 327 (0)| 01:23:56 |
| 1 | TABLE ACCESS FULL | T_EXCHANGERATE_ER | 1701K| 64M| 327 (0)| 01:23:56 |
-------------------------------------------------------------------------------------------------------------
So once we gathered system statistics we can benefit from raising the value for db_file_multiblock_read_count to the maximum what the operating system allows, WITHOUT
increasing the amount of full table scans ( multiblock read ). So if the optimizer decided to go for a full table scan then the maximum amount of blocks are retrieved with 1 single disk IO read.
Note, if the value for db_file_multiblock_read_count has not been set in the server parameter file or init file then the value is derived from the hidden parameter _db_file_exec_read_count
SQL> select KSPPSTVL,KSPPSTDVL from
2 x$ksppi i,x$ksppcv v
3 where v.indx = i.indx
4 and v.inst_id = i.inst_id
5 and i.ksppinm = '_db_file_exec_read_count';
KSPPSTVL
--------------------------------------------------------------------------------
KSPPSTDVL
--------------------------------------------------------------------------------
49
49
SQL>
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 49
So we have learnt that db_file_multiblock_read_count can influence the cost of full table scans.
The higher the value the lower the cost for a full table scan when there are no workload statistics.
The lower the value the higher the cost for a full table scan when there are no workload statistics.
Oracle 8.1 has introduced 2 powerful CBO parameters : optimizer_index_cost_adj and optimizer_index_caching. These parameter should NOT anymore be used in Oracle 10G
The lower the value for optimizer_index_cost_adj the lower the cost for index access
Optimizer_index_caching is used to "tell" the CBO that a percentage of the index blocks are already in the buffer cache.
Consider using dbms_stats.gather_system_stats in stead of using optimizer_index_cost_adj and optimizer_index_caching.
Valuable documentation