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: Larger SGA
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.
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 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:
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:
SQL> alter system set sga_target=72G scope=spfile sid='*'; SQL> Exit
[oracle@rp2vm2 ~]$ srvctl stop database -d rdpp1 [oracle@rp2vm2 ~]$ srvctl start database -d rdpp1
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