返回首页
当前位置: 主页 > 数据库 > Oracle教程 >

如何调整db_file_multiblock_read_count参数减少IO次数

时间:2016-09-23 23:41来源:电脑教程学习网 www.etwiki.cn 编辑:admin

 在CBO优化模式下,也是可以调整全表扫面的成本的,这个小技术需要用到一个Oracle的一个重要的参数db_file_multiblock_read_count,这个参数控制着Oracle在进行全表扫描时单次I/O读取的块数。因此这个参数值越大,对应的全扫描的成本就越低。
下面通过这个实验简单展示一下这个参数的使用效果。我的系统中有一个70万行的表。
SQL> select count(*) from order_detail$;

COUNT(*)
-------------------
708437
1、查一下系统中db_file_multiblock_read_count参数的默认值
SQL> show parameter db_file_multiblock_read_count

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

2、使用autotrace功能查看一下执行计划,得到的cost是1540
SQL> set autotrace traceonly explain
SQL> select * from order_detail$;

Execution Plan
----------------------------------------------------------
Plan hash value: 979479613

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

| 0 | SELECT STATEMENT | | 703K| 42M| 1540 (5)| 00:00:1 9 |

| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1540 (5)| 00:00:19 |

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


3、修改 db_file_multiblock_read_count为32,得到的cost是1364
SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> select * from order_detail$;

Execution Plan
----------------------------------------------------------
Plan hash value: 979479613

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

| 0 | SELECT STATEMENT | | 703K| 42M| 1364 (5)| 00:00:17|

| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1364 (5)| 00:00:17 |

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


4、修改 db_file_multiblock_read_count为64,得到的cost是1276

SQL> alter session set db_file_multiblock_read_count=64;

Session altered.

SQL> select * from order_detail$;

Execution Plan

---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 979479613

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


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 703K| 42M| 1276 (6)| 00:00:16|

| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1276 (6)| 00:00:16 |


---------------------------------------------------------------------------------------------------------------------------------
5、修改 db_file_multiblock_read_count为128,得到的cost是1233

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> select * from order_detail$;

Execution Plan
----------------------------------------------------------
Plan hash value: 979479613

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 703K| 42M| 1233 (6)| 00:00:15|

| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1233 (6)| 00:00:15 |

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

6、修改 db_file_multiblock_read_count为256,得到的cost是1233

SQL> alter session set db_file_multiblock_read_count=256;

Session altered.

SQL> select * from order_detail$;

Execution Plan
-----------------------------------------------------------------------------------------------
Plan hash value: 979479613

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 703K| 42M| 1233 (6)| 00:00:15|
| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1233 (6)| 00:00:15 |

---------------------------------------------------------------------------------------------------------------------------------
小结
Oracle一次I/O所读的数据库个数,除了跟db_file_multiblock_read_count参数的有关,还跟段中的每个区大小有关,已经操作系统及硬件的I/O能力有关;单从Oracle层面来讲,实验中该表的每个区大小是1M,所有最大I/O块个数是128,再增加db_file_multiblock_read_count参数,也不能提供一次I/O的块个数,所有cost不会下降。
在Oracle10g中,db_file_multiblock_read_count参数的默认值是16,在oracle11g中,db_file_multiblock_read_count参数的默认值是128,虽然修改参数对于区大小不是很大的段来讲,效果变化不大,但在某些数据仓库系统中,可能会选择较大的区单位,那么增大该参数,对减少全表扫描的I/O次数是非常有效的。

------分隔线----------------------------
标签(Tag):Oracle oracle数据库 数据库
------分隔线----------------------------
推荐内容
猜你感兴趣