Most Oracle DBA are sufficiently educated about benefits using large memory pages for Oracle database SGA to reduce overhead and improve performance. If you want to read more about it you can start from that Oracle blog or read it from other multiple articles and blogs. Oracle is using parameter use_large_pages to direct behaviour of an Oracle instance during startup.
In the previous versions before 19c we had three possible values – “TRUE”, “FALSE and “ONLY”. Since Oracle 11.2.0.3 the “TRUE” meant that the instance will allocate as many hugepages as free available in the system and get the rest from the normal small pages. The “FALSE” would tell it to not use the hugepages at all and the “ONLY” would be able to start an instance only if sufficient number of free hugepages is available in the system to fit all SGA in it. The “TRUE” was default for all databases.
In the 19c version we got one more value – “AUTO_ONLY” and now it is the default value for Exadata systems running Oracle Database 19c. The description in documentation is not totally clear and sounds very similar to the description of “ONLY” value. Here is an excerpt from the documentation:
“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.”
Let me show you how it works. Here is my sandbox with a 19c database and no hugepages is configured on the box by default.
[oracle@dbtest19 ~]$ cat /proc/meminfo | grep Huge AnonHugePages: 0 kB ShmemHugePages: 0 kB FileHugePages: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 0 kB
If I set up the use_large_pages parameter to “AUTO_ONLY” and restart the instance.
srcdb> sho parameter LARGE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ large_pool_size big INTEGER 0 use_large_pages string TRUE srcdb> ALTER system SET use_large_pages='AUTO_ONLY' sid='*' scope=spfile; System altered. ORACLE instance shut down. srcdb> startup ORACLE instance started. Total System Global Area 4865390936 bytes Fixed SIZE 9144664 bytes Variable SIZE 1828716544 bytes DATABASE Buffers 3019898880 bytes Redo Buffers 7630848 bytes DATABASE mounted. DATABASE opened. srcdb>
Then we can see that the hugepages were automatically configured on the system to fit the SGA.
[oracle@dbtest19 ~]$ cat /proc/meminfo | grep Huge AnonHugePages: 0 kB ShmemHugePages: 0 kB FileHugePages: 0 kB HugePages_Total: 2322 HugePages_Free: 3 HugePages_Rsvd: 3 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 4755456 kB
The database instance is able to change kernel configuration and allocate space in memory with 2mb pages by itself, without our intervention. And the number is exactly what is required to fit the SGA.
In the alert log we see that only 2mb pages were allocated for the instance.
instance MAX SHARED GLOBAL AREA (SGA) into memory: 4644M 2021-06-04T16:34:58.386125+00:00 Available system pagesizes: 4K, 2048K 2021-06-04T16:34:58.386213+00:00 Supported system pagesize(s): 2021-06-04T16:34:58.386254+00:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 2021-06-04T16:34:58.386345+00:00 2048K 2322 2322 2322 NONE 2021-06-04T16:34:58.386389+00:00 Reason for not supporting certain system pagesizes: 2021-06-04T16:34:58.386430+00:00 4K - Large pagesizes only 2021-06-04T16:34:58.386472+00:00
What would happen if we shut down the database?
srcdb> shutdown IMMEDIATE DATABASE closed. DATABASE dismounted. ORACLE instance shut down. srcdb>
The database is down but we still have the hugepages allocated from the memory. And it seems like they will be there until reboot or somebody explicitly tries to change it.
[oracle@dbtest19 ~]$ sysctl vm.nr_hugepages vm.nr_hugepages = 2322 [oracle@dbtest19 ~]$
If we start another database with the use_large_pages parameter, for example, setup to “TRUE” then it will allocate hugepages for its SGA from the already configured.
tstdb> startup ORACLE instance started. Total System Global Area 4294963960 bytes Fixed SIZE 9143032 bytes Variable SIZE 838860800 bytes DATABASE Buffers 3439329280 bytes Redo Buffers 7630848 bytes DATABASE mounted. DATABASE opened. tstdb>
And you can see it has allocated 2046 pages.
[oracle@dbtest19 ~]$ cat /proc/meminfo | grep Hu AnonHugePages: 0 kB ShmemHugePages: 0 kB FileHugePages: 0 kB HugePages_Total: 2322 HugePages_Free: 276 HugePages_Rsvd: 3 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 4755456 kB
If we start our previous instance with use_large_pages=AUTO_ONLY it will count the rest of available hugepages and add new to have 2322 pages to fit its SGA.
Here is a quick summary for the feature.
- Database will configure and allocate new hugepages if it is possible on the system.
- Number of the new hugepages depends on SGA max size and available free hugepages already configured on the system.
- The number of allocated hugepages is not going down when the instance is stopped.
- The new parameter works not only on Exadata but on any non-engineered systems too.
- Default value for the use_large_pages parameter on Exadata system on 19c databases is AUTO_ONLY when it is TRUE for any other systems.
- It is feasible to use it on a very fluid system when you don’t know in advance how many databases is going to be placed there. I would use it on the Dev, Test or sandbox environments. You don’t need to calculate hugepages in advance, modify the kernel settings and apply them. All will done automatically. But you have to watch your memory.
- It is probably not so suitable to use the AUTO_ONLY on a system where you want full control on memory allocation. I easily can imagine situation when a simple error in sga_max_size can lead to overallocation of the hugepages and memory starvation on the system.