Huge Pages on Exadata

Quick note to myself: Oracle has deprecated the initialisation parameter use_large_pages=”true” on Exadata as of 19.13 (OK – could be 19.12, I’ve not checked)

If you started your database in 19.11, and had this set, you got yourself a warning in the alert log (if you have a look):
“WARNING: use_large_pages=TRUE is not a recommended setting for database on Exadata”

from 19.13, this has changed to

“ERROR: use_large_pages = TRUE is not a supported setting for database on Exadata”
” : suggested values are AUTO_ONLY or ONLY”

So what’s the difference?

use_large_pages = TRUE – allocate memory out of huge pages. If there aren’t enough huge pages, allocate the rest out of standard pages. This can result in performance degradation and increased transaction lookaside buffer allocation to manage the standard pages (taking even more memory).

use_large_pages = ONLY – allocate memory out of large pages. If there aren’t enough large pages, fail to start the database.

use_large_pages = AUTO_ONLY – allocate memory out of large pages. If there aren’t enough large pages configured, configure some more by increasing the kernel parameter vm.nr_hugepages. If that fails (e.g. if it can’t get enough contiguous memory to deallocate from standard pages and allocate the new huge pages) fail to start the database. This does result in an “up-only” scenario for huge page allocation as there is no mechanism to automatically deallocate the hugepages once they have been allocated, but the changes are not written permanently to the kernel config so a reboot would take care of it.

So what should you do?

Well, the idea behind AUTO_ONLY is seems to be Oracle ongoing “death of the DBA”, whereby the DBA doesn’t need to understand huge pages and automatically gets the memory and performance benefits associated with using huge pages.

No huge pages allocated:
$ cat /proc/meminfo | grep -i 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

------------------------------------------------------------------------

SQL> ALTER system SET use_large_pages='AUTO_ONLY' sid='*' scope=spfile;
 
System altered.

SQL> shutdown immediate 
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3154114080 bytes
Fixed Size                  9139744 bytes
Variable Size            1207959552 bytes
Database Buffers         1929379840 bytes
Redo Buffers                7634944 bytes

(note: "startup nomount" is enough to allocate memory, so the huge_pages are generated at this point)

------------------------------------------------------------------------
And they have magically appeared! (note: total allocation will vary by a page or two)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    1506
HugePages_Free:        3
HugePages_Rsvd:        3
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         3082240 kB

$ sysctl vm.nr_hugepages
vm.nr_hugepages = 1506
Oracle must be changing the setting and performing a "sysctl" to allocate the pages. If anyone knows what their algorithm is to decide if the pages should be allocated to SGA (and "stolen" from potential PGA and O/S utilisation), and at what percentage will Oracle stop taking from the PGA and O/S? 

An 8G (7972M available) server is allowed an SGA of 6,906M (86% of available memory). Not a lot left for PGA there.

set it too large and you could hit 
"ORA-27104: system-defined limits for shared memory was misconfigured"
which might result in
"ORA-00700: soft internal error, arguments: [sms physmem limit], [6828326912]... "
or
"ORA-00700: soft internal error, arguments: [pga physmem limit], [1048576000]..."

due to being unable to allocate PGA/SGA. I'm talking:

"WARNING: pga_aggregate_limit value is too high for the
amount of physical memory on the system


I'd upped the SGA_MAX_SIZE to 6516M, started the DB, lowered it to 3000M and restarted the DB without a server reboot and...

Total System Global Area 3154114080 bytes
Fixed Size                  9139744 bytes
Variable Size             637534208 bytes
Database Buffers         2499805184 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.

SQL > !cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    3258 <- that's a lot of 2M pages (6516M)
HugePages_Free:     1752 <- we've only allocated 3000M. No cleanup without a reboot
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         6672384 kB


Happy huge pages everyone and keep checking that alert log.

SQL Developer insight

When using SQL Developer, there is a feature called Completion Insight which help you write SQL and PL/SQL in the SQL Worksheets. It will auto-pop helpful things, like object name when typing a SELECT statement, column names if it is aware of the object which you are accessing and things like that.

The pop-up seems to pop-up inconsistently, appearing when I don’t want it and not appearing when I do. I finally snapped and decided to work out (look up) exactly how this feature works. And it’s like this.

1. Make sure your SQL is correctly formed. If you have multiple bits of SQL in the Worksheet, and they are not terminated with a “;” or “/“, then it can get confused about where you are contextually within a statement and not pop-up.

2. The Auto-popup has a delay of either 0.3s (SQL) or 0.6s (PL/SQL). This can be changed in Tools -> Preferences -> Code Editor -> Completion Insight. I have now set a 1.0s delay so it’s not quite so eager to appear.

3. You want the pop-up to appear? Press CTRL + SPACE. If it still does not appear, then it cannot determine your SQL Context (see point 1) or there’s nothing to pop.

Hope this helps you. It’s certainly helped me.