关于db_file_multiblock_read_count

一直觉得db_file_multiblock_read_count这个参数是个很神奇的参数，通过修改它可以改变ORACLE一次读的最大的块数，从而决定全表扫描的效率。下面是官方关于这个参数的说明：

DB_FILE_MULTIBLOCK_READ_COUNT

[@more@]

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.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The maximum value is always less than 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.

Parameter type: Integer

Default value: 8

Range of values: Operating system-dependent

Parameter class: Dynamic:ALTER SESSION, ALTER SYSTEM

db_file_multiblock_read_count的默认值是和版本有关系的，在我的10G中这个默认值已经变成了16。

简单的说，这个参数控制一次可以读的最大块的个数。增大这个参数，全表扫描的COST会变小，那么优化器就会更加倾向于全表扫描，反之则会倾向于索引。

在8i中，当修改db_file_multiblock_read_count增大后，AUTOTRACE中的COST就会变小。那么在10G中呢，还是这个样子么？下面做个例子测试一下。

sunwg@ORCL> create table sunwg as select * from dba_objects;

表已创建。

sunwg@ORCL> alter session set db_file_multiblock_read_count = 4;

会话已更改。

sunwg@ORCL> set autot traceonly

sunwg@ORCL> select /*+no_cpu_costing*/* from sunwg;

执行计划

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

Plan hash value: 856969577

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

| Id | Operation | Name | Rows | Bytes | Cost |

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

| 0 | SELECT STATEMENT | | 43003 | 7433K| 179 |

| 1 | TABLE ACCESS FULL | SUNWG | 43003 | 7433K| 179 |

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

Note

-----

- cpu costing is off (consider enabling it)

- dynamic sampling used for this statement

sunwg@ORCL> alter session set db_file_multiblock_read_count = 8;

会话已更改。

sunwg@ORCL> select /*+no_cpu_costing*/* from sunwg;

已选择52453行。

执行计划

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

Plan hash value: 856969577

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

| Id | Operation | Name | Rows | Bytes | Cost |

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

| 0 | SELECT STATEMENT | | 43003 | 7433K| 114 |

| 1 | TABLE ACCESS FULL| SUNWG | 43003 | 7433K| 114 |

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

Note

-----

- cpu costing is off (consider enabling it)

- dynamic sampling used for this statement

sunwg@ORCL> alter session set db_file_multiblock_read_count = 16;

会话已更改

sunwg@ORCL> select /*+no_cpu_costing*/* from sunwg;

已选择52453行。

执行计划

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

Plan hash value: 856969577

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

| Id | Operation | Name | Rows | Bytes | Cost |

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

| 0 | SELECT STATEMENT | | 43003 | 7433K| 73 |

| 1 | TABLE ACCESS FULL| SUNWG | 43003 | 7433K| 73 |

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

Note

-----

- cpu costing is off (consider enabling it)

- dynamic sampling used for this statement

从上面可以看得出来10G也是这个样子的。细心的可能会发现，我在SELECT中增加了一个提示/*+no_cpu_costing*/，这个提示的目的是去掉CPU的COST信息。在10G中CBO中增加了对CPU的COST的统计，这个cpu costing在全表扫描的时候对COST值的影响会很大，所以在这里把cpu costing给去掉了。

SQL> analyze table sunwg compute statistics;

Table analyzed

SQL> select blocks from user_tables where table_name='SUNWG';

BLOCKS

----------

738

db_file_multiblock_read_count	COST	ADJUSTED DBF_MBRC
4	179	738/179 = 4.12
8	114	738/114 = 6.47
16	73	738/73 = 10.1

ADJUSTED DBF_MBRC是个很重要的参数，其实表扫描的成本也就是高水位标记下的块的数量除以ADJUSTED DBF_MBRC的结果。然后上面表中的ADJUSTED DBF_MBRC仅仅是个近似的值，因为并没有加上位图块的数量。因为本地管理的表空间是用一些位图来表示块的可用性的，所以如果算上位图块的话，ADJUSTED DBF_MBRC应该会大一些。

最近在看那本极其出名的《 COST-BASED ORACLE FUNDAMENTALS 》，里面对 CBO 的讲解清晰透彻，是本难度的好书。我也会多写

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8394333/viewspace-996014/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8394333/viewspace-996014/