Home > Workload Solutions > Oracle > Best Practices > AMD-Based Oracle Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > Oracle 19c Database: DB_FILE_MULTIBLOCK_READ_COUNT
In this best practice we updated the database parameter db_file_multiblock_read_count to optimize database performance for an online transaction processing workload.
Category | Oracle 19c Database |
Product | Oracle 19c |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
Overview
The database configuration parameter db_file_multiblock_read_count enables the administrator to specify the maximum number of blocks read in one I/O operation to storage during a sequential scan. For workloads like data warehouses, decision support systems, and online analytical processing a large value for db_file_multiblock_read_count optimizes full table scans. Larger values allow the database to read more blocks which optimizes full table scans.
Conversely, Online Transaction Processing (OLTP) workloads can allow fewer full table scans and the default recommendation is to set db_file_multiblock_read_count parameter to a lower value. Setting the db_file_multiblock_read_count to a lower value optimizes database I/O for many small reads from the database files. We set the db_file_multiblock_read_count to 4 for this best practice.
Recommendation
Very minor performance improvements were made for the following metrics:
Overall, setting db_file_multiblock_read_count = 4 slightly optimized the database systems, allowing more gains to be realized over time. As the performance improvements were minor, this best practice is recommended as a Day 3, Fine Tuning activity.
Implementation Steps
Use the following steps to set db_file_multiblock_read_count = 4
SQL> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=4 scope=both; SQL> Exit
[oracle@rp2vm2 ~]$ srvctl stop database -d rdpp1 [oracle@rp2vm2 ~]$ srvctl start database -d rdpp1
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT;
Follow these steps for all the eight database nodes/VMs (rp2vm2, rp2vm3 to rp2vm9)
Additional Resources