In this best practice we increased the size of the Oracle redo logs to improve database performance. Proper sizing of the database redo logs can reduce wait events in the database, optimizing the database system. 

Overview

The database redo logs consist of a minimum of two files that are responsible for storing any updates made to the database. Redo logs are critical to the database as they protect data and the database from a failure. 

The log file sync event is the time it takes for the log writer (LGWR) to write to the redo log file. 

In the Oracle databases used for validating best practices the log file sync wait events had higher than average frequency and duration of wait times. To improve performance, we changed the log file size 200 MB to 8 GB. 

Recommendation

Increasing the size of the redo log from 200 MB to 8 GB significantly increased performance in the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS
• Server CPU utilization 
• DB File Sequential Read 
• Log File Parallel Write 

Increasing the size of the redo log from 200 MB to 8 GB significantly increased performance. Both NOPM and TPM increase by more than 40%. Array IOPS also increased with a corresponding drop in average array read and write times. Overall server utilization increased which in turn inhibited CPU increases. 

Finally, both db file sequential read and log file parallel write times also showed significant decreases in average wait times. As performance improvements were very strong across all metrics, we recommend this best practice as Highly Recommended Day 1 configuration activities. 

Not every database will show significant performance increases when increasing the size of the redo logs. Customers should evaluate in the AWR if the redo logs have associated high number of waits and wait times. If the redo logs do show a possible bottleneck, then increasing the size of the redo logs could improve performance. 

Implementation Steps

Use the following steps to update redo log size and files:

1. Log in to the database node/VM (rp2vm2) as oracle user
2. Connect to sqlplus
3. Update redo log file size and increase groups using the steps captured below

   SQL>  alter database add logfile group 4  '+REDO' size 8G;
   SQL>  alter database add logfile group 5  '+REDO' size 8G;
   SQL>  alter database add logfile group 6  '+REDO' size 8G;
   SQL>  alter database add logfile group 7  '+REDO' size 8G;
   SQL>  alter database add logfile group 8  '+REDO' size 8G;

4. Switch the log file to the newly added redo log groups and delete the old inactive groups. Switch the baseline/default 3 redo log groups to inactive state so that they can be deleted.

   SQL>  alter system switch logfile;
   Based on the current redo log state and need make the old groups inactive 
   SQL>   alter system archive log group 1;
   SQL>   alter system archive log group 2;
   SQL>   alter system archive log group 3;

5. Drop the old groups once they are inactive

   SQL> ALTER DATABASE DROP LOGFILE GROUP 1; 
   SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
   SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
   SQL> Exit

6. Verfiy the updated redo log files
7. Connect to sqlplus

   SQL>  select group#,sequence#,bytes,archived,status from v$log;

Follow these steps for all the eight database nodes/VMs (rp2vm2, rp2vm3 to rp2vm9) 

Additional Resources

Oracle 19c Database: Managing the Redo Log

Oracle 19c Database: Larger SGA 

We tested the Oracle Force Full Cache (FFC) feature before validating all the best practices. The Oracle FFC results were impressive, so we decided to include the results in a best practice.  

Overview 

When FFC mode is implemented the Oracle server attempts to cache the full database in memory. This means all the datafiles that are related to the database are cached in memory. This reduces read latency from storage system to memory access speeds. Writes are still sent to storage to ensure durability and recovery of the database.  

We highly recommend using memory technologies like FFC mode for the enterprise as AMD EPYC processors enable the PowerEdge R7525 servers to support up to 4 TB of LRDIMM. Depending on the database and its growth rate, this could support many small to medium sized systems. The benefit for business is the capability to accelerate the database by configuring a Dell EMC PowerEdge R7525 server and AMD processors with enough memory to cache the entire database. 

Table 1: Virtual Machine configuration and SGA Size 

Table 1 shows that the main distinction between the baseline and FFC mode is the SGA size. By using FFC, we wanted to fit the entire database in memory thus, the SGA size is 784 GB. We used HammerDB to create a TPROC-C workload with 5,000 simulated warehouses which generated approximately 500 GB of data, which was small enough to be loaded into the SGA.  

Recommendation 

New Orders per Minute for the baseline configuration reached 169,481 and 413,577 for FFC mode, which is a 244% performance improvement. Enabling Oracle FFC mode requires architecture planning to ensure the PowerEdge server has enough memory to cache the entire database. We recommend using FFC mode as a Highly Recommended, Day 1 activity.  

Implementation Steps

Use the following procedure to enable FFC mode

1. Connect to the database instance in mount state(not open) as a (oracle) user with access to alter database previlige
2. Enable the FFC mode using the commands captured below:

     SQL> ALTER DATABASE FORCE FULL DATABASE CACHING;
    
           SQL> ALTER DATABASE OPEN;

3. Verify the caching mode

       SQL> SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

Additional Resources 

AMD EPYC Milan Processors and FFC Mode Accelerate Oracle 244% 

Oracle Database 19c: Using Force Full Database Caching Mode

In this best practice we used an Oracle configuration parameter to set database processes to high priority. The Linux operating system enables setting priorities on processes to ensure critical processes gain priority of the operating systems. 

 Overview

The Oracle configuration parameter _high_priority_processes enables the database administrator to set related database processes to a higher priority. The following command shows the default value of the parameter:

_high_priority_processess = ’LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|LM8*|LM9*’

• LMSn and LMn - relates to the global cache service process.

• VKTM – is the virtual keeper of time process and provides a wall clock time for measurements. 
• LGWR – is the log writer processes and writes data to the online redo logs. 

The LMSn and LMn process relates to the global cache service process. These processes receive, process, and send requests for the global cache service and the buffer cache resources. 

To optimize the database configuration, we configured the parameter to prioritize these processes:

_high_priority_processess = ’LMS*|VKTM|LGWR’

Changing the _high_priority_processess parameter does require a database restart. 

Recommendation

Updating the _high_priority_processess database parameter slightly increased performance across the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS.
• Server CPU utilization. 
• DB File Sequential Read 
• Log File Parallel Write 

We recommend changing the _high_priority_processess parameter as a Day 3, Fine Tuning activity as it can provide the database with a slight performance boost. We also recommend testing this thoroughly before applying to production systems. Refer to Oracle support DOC: Doc ID 1373500.1

Implementation Steps

Use the following steps to update the parameter related to high priority processes:

1. Log in to the database node/VM (rp2vm2) as oracle user
2. Connect to sqlplus
3. Update the priority of intended processes

            SQL>  alter system set "_high_priority_processes"='LMS*|VKTM|LGWR' scope=spfile;

SQL> Exit 

1. Restart the database

               [oracle@rp2vm2 ~]$ srvctl stop database -d rdpp1
               [oracle@rp2vm2 ~]$ srvctl start database -d rdpp1

1. Verfiy the updated priority using the following steps
2. Connect to sqlplus

SQL> select ksppstvl from x$ksppi join x$ksppcv using (indx) where ksppinm='_high_priority_processes';

Follow these steps for all the eight database nodes/VMs (rp2vm2, rp2vm3 to rp2vm9). 

Additional Resources

Top 5 Database and/or Instance Performance Issues in RAC Environment (requires login)

In this best practice we updated the database parameter db_file_multiblock_read_count to optimize database performance for an online transaction processing workload. 

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:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS
• Server CPU utilization
• DB File Sequential Read 
• Log File Parallel Write 

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

1. Log in to the database node/VM (rp2vm2) as oracle user
2. Connect to sqlplus
3. Update the db_file_multiblock_read_count parameter value using the following steps: 

   1. SQL>  alter system set DB_FILE_MULTIBLOCK_READ_COUNT=4 scope=both;
      SQL> Exit

4. Restart the database

                  [oracle@rp2vm2 ~]$ srvctl stop database -d rdpp1
                  [oracle@rp2vm2 ~]$ srvctl start database -d rdpp1

5. Verfiy the value of the parameter db_file_multiblock_read_count
6. Connect to sqlplus

               SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT;

Follow these steps for all the eight database nodes/VMs (rp2vm2, rp2vm3 to rp2vm9)

Additional Resources

Oracle Database Reference: DB_FILE_MULTIBLOCK_READ_COUNT

In this best practice the System Global Area (SGA) size is adjusted per the recommendation of the AWR report. Allocating additional memory to the Oracle database can improve performance by enabling more local reads. 

Overview

The SGA of an Oracle database contains memory pools that are used by the database to accelerate access to data. For example, the database buffer cache holds a subset of data enabling user processes to access data without having to read from disk. Reading data from the buffer cache is called a logical read and is significantly faster than accessing the data from storage. 

The Automatic Workload Repository (AWR) reports the db file sequential read wait event took just over 76% of the total database time. The db file sequential read wait metric indicates the database has requested to read a block from storage and the time to retrieve the data is captured as wait time. Because the db file sequential wait time requires the majority of total database time, increasing the SGA size could improve performance by enabling more data to be retained in the database buffer pool. 

The AWR reports have a section in which the DBA can review sizing recommendations for the SGA. In this test case the SGA recommends increasing the SGA_TARGET size from the original 64 GB to 72 GB, an increase of 8 GB. The SGA_TARGET was set to the recommended size of 72 GB with the expectation that the number of physical reads would decrease. 

Recommendation

No significant performance improvements were seen in the following metrics: 

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS
• Server CPU utilization 
• DB File Sequential Read 
• Log File Parallel Write 

Increasing the SGA size to 72 GB slightly decreased the storage related average read time and database average db file sequential read times. However, because the decrease in storage read and db file sequential read times were minor, the overall impact did not demonstrate a significant increase in overall database performance.

As the goal of validating best practices means placing a substantial load on the database infrastructure increasing the SGA size might show greater returns in performance for other systems. For this reason, we recommend increasing the SGA size as a Day 3, Fine Tuning best practice.  

Implementation Steps

Use the following steps to update SGA:

1. Log in to the database node/VM (rp2vm2) as oracle user
2. Connect to sqlplus
3. Update SGA 

   SQL>  alter system set sga_target=72G scope=spfile sid='*';
   SQL> Exit

4. Restart the database

   [oracle@rp2vm2 ~]$ srvctl stop database -d rdpp1
   [oracle@rp2vm2 ~]$ srvctl start database -d rdpp1

5. Verfiy the value of the parameter sga
6. Connect to sqlplus

   SQL> show parameter sga;

Follow these steps for all the eight database nodes/VMs (rp2vm2, rp2vm3 to rp2vm9)

Additional Resources

Database Performance Tuning Guide: Tuning the System Global Area