Oracle Database 19c -- Increase SGA size when HugePages are used

Problem

You need to increase SGA memory allocation for an Oracle database with HugePages in use

 

SQL> show parameter use_large_pages

NAME              TYPE        VALUE
----------------- ----------- -----
use_large_pages   string      ONLY

Solution

Identify the current SGA allocation:

 

SQL> sho parameter sga
 
NAME           TYPE        VALUE
-------------- ----------- ------
sga_max_size   big integer 29184M
sga_target     big integer 29184M

 

 

Check available free memory on the database host:

 

$ free
              total        used        free
Mem:       61578068    33446968    23533764
Swap:      16777212           0    16777212 

 

Check the current hugepages configuration:

 

$  grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:   15322
HugePages_Free:      738
HugePages_Rsvd:       10
HugePages_Surp:        0
Hugepagesize:       2048 kB

Confirm the hugepages usage with the alert log file for the database:

2021-09-16T15:43:09.663791+00:00
 Expected per process system memlock (soft) limit to lock
 instance MAX SHARED GLOBAL AREA (SGA) into memory: 29G
2021-09-16T15:43:09.663960+00:00
 Available system pagesizes:
  4K, 2048K
2021-09-16T15:43:09.664101+00:00
 Supported system pagesize(s):
2021-09-16T15:43:09.664173+00:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2021-09-16T15:43:09.664333+00:00
     2048K            15322           14594           14594        NONE

To extend the SGA, first increase the number of hugepages. 

 

From the outputs above, it is seen that around 20GB free memory is available, and the pagesize is 2MB, so to increase the SGA by 20GB, for example, 10000 hugepages need to be added:

# sysctl -w vm.nr_hugepages=25332
vm.nr_hugepages = 25332

# grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:   25332
HugePages_Free:    10748
HugePages_Rsvd:       10
HugePages_Surp:        0
Hugepagesize:       2048 kB

Now increase the SGA allocation from 30GB to 40GB:

SQL> alter system set sga_max_size=40g scope=spfile;

System altered.

SQL> alter system set sga_target=40g scope=spfile;

System altered.

 

Restart the instance and verify SGA allocation:

 

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 12686048 bytes
Variable Size            3623878656 bytes
Database Buffers         3.9192E+10 bytes
Redo Buffers              121528320 bytes 

 

From the alert log:

 

2021-09-16T16:43:23.008713+00:00
 Expected per process system memlock (soft) limit to lock
 instance MAX SHARED GLOBAL AREA (SGA) into memory: 40G
2021-09-16T16:43:23.008884+00:00
 Available system pagesizes:
  4K, 2048K
2021-09-16T16:43:23.009027+00:00
 Supported system pagesize(s):
2021-09-16T16:43:23.009100+00:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2021-09-16T16:43:23.009250+00:00
     2048K            25332           20482           20482        NONE 

To make the hugepages configuration permanent, edit the /etc/sysctl.conf file, update the value for the vm.nr_hugepages entry,