Shulou(Shulou.com)06/01 Report--

I. Environment

Oracle 11g RAC

II. Test process

SQL> show parameter db_fileNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count integer 128db_file_name_convert string /oracle/oradata/, +DATADGdb_files integer 200SQL> set timing onSQL> select count(*) from tt; COUNT(*)---------- 5524288Elapsed: 00:00:11.50SQL> alter system flush buffer_cache;System altered.Elapsed: 00:00:00.20SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.39SQL> alter session set db_file_multiblock_read_count=16;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt; COUNT(*)---------- 5524288Elapsed: 00:00:08.91SQL> alter system flush buffer_cache; System altered.Elapsed: 00:00:00.12SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.06SQL> alter session set db_file_multiblock_read_count=32;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt; COUNT(*)---------- 5524288Elapsed: 00:00:07.87SQL> alter system flush buffer_cache; System altered.Elapsed: 00:00:00.14SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.06SQL> alter session set db_file_multiblock_read_count=64;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt; COUNT(*)---------- 5524288Elapsed: 00:00:07.05SQL> alter system flush buffer_cache;System altered.Elapsed: 00:00:00.15SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.06SQL> alter session set db_file_multiblock_read_count=128;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt; COUNT(*)---------- 5524288Elapsed: 00:00:06.62SQL> SQL>

III. Summary

For full table scans, multi-block reads, increasing the number of blocks read each time, can improve performance.

2. It is recommended to set this parameter to 8, 16, 32 in OLTP system.

3. It is recommended to set this parameter to the maximum value of 128 in OLAP system.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.