The New AUTO_ONLY option for USE_LARGE_PAGES parameter [19c Version]
Tags: tutorial, oracle, database, memory
Published: 2020-10-31 15:21:46

Setting up the memory of a Database is always a process that requires planification, specially if you want to get the maximum of your server and setup Memory Huge Pages in your Linux system, which is recommended. 
The concern is that other applications/processes wont be able to use that portion of memory, causing instability if we push the system to only hugepages portions of memory, even if no Database instances are using it.

Before 19c, we had 3 different Values for USE_LARGE_PAGES database parameter:
[Administering Oracle Database Exadata Cloud Service (OCI-C)](https://docs.oracle.com/en/cloud/paas/exadata-cloud/csexa/manage-huge-pages.html)

> - TRUE — specifies that the database instance can use Huge Pages if they are available. For all versions of Oracle Database after 11.2.0.3, Oracle allocates as much of the SGA as it can using Huge Pages. When the Huge Page allocation is exhausted, standard memory pages are used.
> 
> - FALSE — specifies that the database instance does not use Huge Pages. This setting is generally not recommended if Huge Pages are available.
> 
> - ONLY — specifies that the database instance must use Huge Pages. With this setting, the database instance fails to start if the entire SGA cannot be accommodated in Huge Pages


Now, with 19c database, we have a fourth option:
[19c Database Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/USE_LARGE_PAGES.html#GUID-1B0F4D27-8222-439E-A01D-E50758C88390)

> - AUTO_ONLY — This setting is available starting with Oracle Database 19c and it is the default setting for Exadata systems. It specifies that, during startup, the instance will calculate and request the number of large pages it requires. If the operating system can fulfill this request, then the instance will start successfully. If the operating system cannot fulfill this request, then the instance will fail to start. This ensures that no instances will run with under-provisioned large pages.

The major advantage of AUTO_ONLY is the fact that we don't longer need to recalculate total of hugepages we setup the OS with, we can leave the Database itself to create hugepages from the available memory, making provisioning on large system and specially rapid orchestration much, much easier.

Btw, as mentioned in the Documentation, this is the new default option if you are using Exadata systems, so you may already using it without you realizing it.. that is what happened to me, that was someone spotting different hugepages values on servers running new 19c instances

Let's test the new option.

To start, we have the OS without any Hugepages configuration:
```bash
-- Only ASM Instance running
[oracle@rac1-node1 ~]$ pmon
oracle    7324     1  0 12:28 ?        00:00:00 asm_pmon_+ASM1
[oracle@rac1-node1 ~]$ grep Huge /proc/meminfo 
AnonHugePages:   1048576 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
```

If we go and set "TRUE" value, we can see how the system will be able to start, but won't use any Hugepages, which is a big compromise for good performance:
```bash
-- db19 running with USE_LARGE_PAGES = TRUE
[oracle@rac1-node1 ~]$ srvctl start database -d db19

[oracle@rac1-node1 ~]$ grep Huge /proc/meminfo 
AnonHugePages:    792576 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
[oracle@rac1-node1 ~]$ 
```

If we set the parameter to "ONLY" it won't start, as expected:
```bash
-- db19 "running" with USE_LARGE_PAGES = ONLY
[oracle@rac1-node1 ~]$ srvctl start database -d db19
PRCC-1014 : db19 was already running
PRCR-1004 : Resource ora.db19.db is already running
PRCR-1079 : Failed to start resource ora.db19.db
CRS-5017: The resource action "ora.db19.db start" encountered the following error: 
ORA-27106: system pages not available to allocate memory
Additional information: 6122
Additional information: 2
Additional information: 3
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1-node2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.db19.db' on 'rac1-node2' failed
CRS-2528: Unable to place an instance of 'ora.db19.db' as all possible servers are occupied by the resource
[oracle@rac1-node1 ~]$ 
```

Now, let's start a 19c Database Instance with this new value "AUTO_ONLY":
```bash
-- db19 running with USE_LARGE_PAGES = AUTO_ONLY
[oracle@rac1-node1 ~]$ srvctl start database -d db19

[oracle@rac1-node1 ~]$ grep Huge /proc/meminfo 
AnonHugePages:    927744 kB
ShmemHugePages:        0 kB
HugePages_Total:    2370  <<<<<<<<<<<
HugePages_Free:        4
HugePages_Rsvd:        4
HugePages_Surp:        0
Hugepagesize:       2048 kB
[oracle@rac1-node1 ~]$ 
```

And ***voila!***, as we can see above, the system just created the necessary hugepages for it to start correctly

This is a *huge* change! :P