db_file_multiblock_read_count参数设置取值测试

1.创建测试表
create table big_table as select * from all_objects;

2.获取等待时间
create or replace procedure full_scan_big_table(p_dbmrc in number)
as
 l_cnt number;
begin
 execute immediate 'alter tablespace users offline';
  execute immediate 'alter tablespace users online';
 
  execute immediate 'alter session set db_file_multiblock_read_count=' || p_dbmrc;
 
  execute immediate 'alter session set events ''10046 trace name context forever, level 12'' ';
 
  execute immediate 'select /*+ FULL(bt_mbrc_'|| p_dbmrc || ') */ count(*) from big_table bt_mbrc_'||p_dbmrc into l_cnt;
 
  execute immediate 'alter session set events ''10046 trace name context off'' ';
 
end;

执行如下脚本，对trc文件运行tkprof，可以得到不同db_file_multiblock_read_count设置时执行类似
select /*+ FULL(b) */ count(*) from big_table b;查询的等待时间：
begin
 for i in 0..9
 loop
   full_scan_big_table(power(2,i));
 end loop;
end;

3.测试查询成本

set autotrace traceonly explain

alter session set db_file_multiblock_read_count=4;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：
SQL> alter session set db_file_multiblock_read_count=4;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   280   (6)| 00:00:04 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   280   (6)| 00:00:04 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=8;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果:
SQL> alter session set db_file_multiblock_read_count=8;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   207   (8)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   207   (8)| 00:00:03 |
------------------------------------------------------------------------

 

alter session set db_file_multiblock_read_count=16;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：
SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   170   (9)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   170   (9)| 00:00:03 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=32;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：

SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   152  (10)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   152  (10)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=64;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：

SQL> alter session set db_file_multiblock_read_count=64;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   143  (11)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   143  (11)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=128;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果:

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   138  (11)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |                    |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   138  (11)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=256;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果:

SQL> alter session set db_file_multiblock_read_count=256;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   138  (11)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   138  (11)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=512;
select /*+ FULL(b) */ count(*) from big_table b;

SQL> alter session set db_file_multiblock_read_count=512;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   138  (11)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 51035 |   138  (11)| 00:00:02 |
------------------------------------------------------------------------

观察不同db_file_multiblock_read_count参数设置下：查询成本/1000+等待时间
可以得出：最好的db_file_multiblock_read_count值应该为16，该参数取值的阈
值依赖于操作系统和硬件，应该对不同的操作系统和硬件，多作测试。