考量参数DB_FILE_MULTIBLOCK_READ_COUNT的脚本

show parameters db_file_multiblock_read_count;--sys用户登录查看当前参数值
set timing on;
set autotrace on explain;
alter session set db_file_multiblock_read_count = 1;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 2;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 4;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 8;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 16;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 32;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 64;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 128;
select /*+ Full(b) */ count(*) from big_table b;
alter session set db_file_multiblock_read_count = 512;
select /*+ Full(b) */ count(*) from big_table b; 

 

 Cost + ExecTime得到最佳的block_read_count大小,需要是幕为2 à power(2,i)

 

scott@COLM> set timing on;

scott@COLM> set autotrace on explain;

scott@COLM> alter session set db_file_multiblock_read_count = 1;

 

会话已更改。

 

已用时间 : 00: 00: 00.00

scott@COLM> select /*+ Full(b) */ count(*) from big_table b;

 

 COUNT(*)

----------

   1000000

 

已用时间 : 00: 00: 11.01

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8627 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=8627 Card=1000000)

 

 

 

scott@COLM> alter session set db_file_multiblock_read_count = 2;

 

会话已更改。

 

已用时间 : 00: 00: 00.00

scott@COLM> select /*+ Full(b) */ count(*) from big_table b;

 

 COUNT(*)

----------

   1000000

 

已用时间 : 00: 00: 10.04

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5468 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=5468 Card=1000000)

 

 

 

scott@COLM> alter session set db_file_multiblock_read_count = 4;

 

会话已更改。

 

已用时间 : 00: 00: 00.00

scott@COLM> select /*+ Full(b) */ count(*) from big_table b;

 

 COUNT(*)

----------

   1000000

 

已用时间 : 00: 00: 09.05

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3465 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=3465 Card=1000000)

 

 

 

scott@COLM> alter session set db_file_multiblock_read_count = 8;

 

会话已更改。

 

已用时间 : 00: 00: 00.00

scott@COLM> select /*+ Full(b) */ count(*) from big_table b;

 

 COUNT(*)

----------

   1000000

 

已用时间 : 00: 00: 09.00

 

。。。。。