在CBO优化模式下也是可以调整全表扫面的成本的，通过这个技术可以适当的改变CBO下的SQL执行路径。
这个小技术需要用到一个Oracle的一个重要的参数db_file_multiblock_read_count，这个参数控制着Oracle在进行全表扫描时单次I/O读取的块数。因此这个参数值越大，对应的全扫描的成本就越低。

下面通过这个实验简单展示一下这个参数的使用效果。

1.创建一个包含10万行数据的表t，在t表的x字段上创建有主键
sec@ora10g> select count(x) from t;

COUNT(X)
----------
    100000

2.查一下系统中db_file_multiblock_read_count参数的默认值
sec@ora10g> show parameter db_file_multiblock_read_count

NAME                                     TYPE                 VALUE
---------------------------------------- -------------------- ------------
db_file_multiblock_read_count            integer              16

3.使用autotrace功能查看一下执行计划，得到的cost是305
sec@ora10g> set autotrace traceonly explain
sec@ora10g> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   100K| 8886K|   305   (1)| 00:00:04 |
|   1 | TABLE ACCESS FULL| T    |   100K| 8886K|   305   (1)| 00:00:04 |
--------------------------------------------------------------------------

4.将db_file_multiblock_read_count参数提高到32，再次查看一下，得到的cost是269，比默认的305代价低了一些
sec@ora10g> alter session set db_file_multiblock_read_count=32;

Session altered.

sec@ora10g> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   100K| 8886K|   269   (2)| 00:00:04 |
|   1 | TABLE ACCESS FULL| T    |   100K| 8886K|   269   (2)| 00:00:04 |
--------------------------------------------------------------------------

5.再次提高参数值到64，此时的cost减少到251，比269的代价又小了一些
sec@ora10g> alter session set db_file_multiblock_read_count=64;

Session altered.

sec@ora10g> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   100K| 8886K|   251   (2)| 00:00:04 |
|   1 | TABLE ACCESS FULL| T    |   100K| 8886K|   251   (2)| 00:00:04 |
--------------------------------------------------------------------------

6.我们再将该参数调整到128，看到cost值是242，又进一步减少了
sec@ora10g> alter session set db_file_multiblock_read_count=128;

Session altered.

sec@ora10g> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   100K| 8886K|   242   (2)| 00:00:03 |
|   1 | TABLE ACCESS FULL| T    |   100K| 8886K|   242   (2)| 00:00:03 |
--------------------------------------------------------------------------

7.再次加大该参数到256，此时我们看到的cost与上面的cost 242一样，说明这个参数可控的范围是有限的，其实这个参数与具体的操作系统的I/O能力有关
sec@ora10g> alter session set db_file_multiblock_read_count=256;

Session altered.

sec@ora10g> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   100K| 8886K|   242   (2)| 00:00:03 |
|   1 | TABLE ACCESS FULL| T    |   100K| 8886K|   242   (2)| 00:00:03 |
--------------------------------------------------------------------------

8.小结
db_file_multiblock_read_count参数的有效范围是根据不同的操作系统变化的；
全表扫描的代价越低，被选用的可能性就越大，达到一定程度后，Oracle优化器将会回避一些索引的使用，在某些数据仓库系统中这样的优化方法是非常的有效的；
对操作系统的I/O越少，全表扫描执行就越快。

-- The End --

ITPub博客

【参数】使用db_file_multiblock_read_count参数控制全表扫描成本

最新文章