一、环境oracle 11g RAC 二、测试过程 sql> show parameter db_file NAME TYPE
一、环境
oracle 11g RAC
二、测试过程
sql> show parameter db_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
db_file_name_convert string /oracle/oradata/, +DATADG
db_files integer 200
SQL> set timing on
SQL> select count(*) from tt;
COUNT(*)
----------
5524288
Elapsed: 00:00:11.50
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.20
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.39
SQL> alter session set db_file_multiblock_read_count=16;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from tt;
COUNT(*)
----------
5524288
Elapsed: 00:00:08.91
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.12
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.06
SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from tt;
COUNT(*)
----------
5524288
Elapsed: 00:00:07.87
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.14
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.06
SQL> alter session set db_file_multiblock_read_count=64;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from tt;
COUNT(*)
----------
5524288
Elapsed: 00:00:07.05
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.15
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.06
SQL> alter session set db_file_multiblock_read_count=128;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from tt;
COUNT(*)
----------
5524288
Elapsed: 00:00:06.62
SQL>
SQL>
三、小结
1、对于全表扫描来说多块读,增加每次读取的块数,可以提高性能。
2、在OLTP的系统中建议此参数设置为8、16、32
3、在OLAP的系统中建议此参数设置为128最大值
--结束END--
本文标题: DB_FILE_MULTIBLOCK_READ_COUNT 与性能有关的参数
本文链接: https://www.lsjlt.com/news/42094.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0