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，该参数取值的阈
值依赖于操作系统和硬件，应该对不同的操作系统和硬件，多作测试。