Oracle 19c Database: Larger SGA

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

Category	Oracle 19c Database
Product	Oracle 19c
Type of best practice	Performance Optimization
Day and value	Day 3, Fine Tuning

Overview

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

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.The AWR reports showed that db file sequential read wait events took just over 76% of the total database time. This indicates that increasing the SGA size could improve performance by enabling more data to be retained in the database buffer pool facilitating shorter wait times. 

In this test case we followed the SGA sizing recommendation and increased the SGA_TARGET size from 64 GB to 72 GB, an increase of 8 GB. We expected this would cause the number of physical reads to decrease. 

Recommendation

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 was minor, the overall impact did not show a significant increase in overall database performance. 

None of the following metrics showed significant performance improvements. 

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

To validate these best practices, we place a substantial load on the database infrastructure, therefore increasing the SGA size might show greater returns in performance for other systems. We recommend this to be considered a Day 3, Fine Tuning best practice.   

Implementation Steps

Use the following steps to increase the SGA size to 72 GB, execute the following SQL statements after connecting to the database instance with the sysdba privilege:

        alter database sga_target = 72G  scope=spfile; 

        alter database sga_max = 72G scope=spfile;

Then restart the database instance.