this is not a post to ask for something but a warning for those who have configured big SGA instances ( > 15,16 GB) . Yesterday I experienced a "system freeze" when I increase the SGA for one of the databases. Suddenly all executions of simple querys were slowly not only for this instance but for all the instances in the Exadata database node. As this Exadata manages the core of databases for the company we open a S.R. with level 1. Oracle Support after request the OS Watcher logs and alert logs for all the databases in the Exadata node told us that the problem was with use of Huge Pages.
After huge pages have been configured on Exadata its default value was not enough so we increased this value and set the: "use_large_pages" parameter to "only" (only for >= 188.8.131.52). All the instances were restarted and the problem was solved. As I could see, when there are not enough huge pages the process works with both types, 4KB for traditional pages and 2MB for huge pages, which is too bad. Perhaps this is due to excesive switch contexts, I am not sure.
Without hugepages, there is memory overhead to store the additional page table entries, and this overhead increases as you add larger SGA and more concurrent sessions. So do you have to enable hugepages for >16G SGA? No, but you just need to make sure you have enough free memory to hold the larger page tables.
You can find the current size of the page tables by running "grep PageTables /proc/meminfo"
having a SGA greater than 16GB can be a problem if there is not enough huge pages configured. In my situation I have several instances running on the same Exadata node so all of them compete for huge pages. If there are not enough huge pages processes will start to consume 4KB and 2MB page sizes which is too bad as points a Metalink note 361468.1. It is only an advice, the idea is SGA have enough large pages to avoid. From this ML note:
"Database started successfully and the performance is slow: The SGA of the specific database could not find available HugePages and therefore the SGA is handled by regular pages, which leads to slow performance. Make sure that the HugePages are many enough to cover all your database SGAs".
I think today having big SGA is very common so, perhaps, Exadata should be configured by default with enough large pages configured for each database node. Keep in mind that you must sum the ASM SGA too. In my case all worked fine until, suddenly, a little increment in SGA( + 2GB) in one of the instances triggered a global lost of performance in the node.
I am not an O.S. expert but I think large pages should be configured with large SGAs due to table pages size. Conventional pages are 4KB so for large SGAs table pages will be huge and swapping will be more probable. On the other side with huge pages the probability of swapping is lower.
My post is only an advice, not a must. After reading the ML supplied by Oracle Support I read the advice for enabling enough large pages with SGA´s greater than 8GB and, in my case, I can ensure this is right since tuesday. I must clarify, I have several database instances with more than 15GB, but in other Exadata I performed tests with 50GB of SGA and 40GB for pga with default huge pages configuration without issues. It is the sessions concurrency and several big instances on the same node which triggered this problem.
The question user1779355 asked was "do you need huge pages for a >16G SGA". And the answer it no, you do not have to use hugepages provided you have enough memory available.
"Should you use huge pages" is an entirely different question. And to that question I agree with you: not only will hugepages save you memory, but it will reduce performance overhead.
(And to take a stab at the related question "hot do I use hugepages", there's a great MOS document (361468.1) with a script to give you the value to use.
The thing I don't like about MOS document (361468.1) is the fact you need to reboot after the huge pages setting has been changed. On the X2-2 compute nodes there is a 96 GB of RAM available and the oracle user has a standard limit of around 75 GB set.
For now I have configured 56 GB for huge pages on all my db nodes leaving some memory for normal pages.
Rumour has it you shouldn't use all available memory (75 GB for oracle) for huge pages.
This is an interesting point and thanks for bringing it up. The perceived need for reboots to change HugePage values is the most frequent argument against hugepages I run into. In reality, decreasing the number of hugepages does not require a reboot, and increasing it only sometimes require a reboot. So I recommend that clients try increasing the value and see if they can do so online. Running a script to attempt to claim hugepages in a loop over a 10-minute period can also be effective.
I recently ran into this issue on an Exadata X2-8 environment, which runs the Oracle UEK1 kernel. The machine had been running for some time and with fragmented memory, so I wasn't sure I could increase the hugepages. I modified /proc/sys/vm/nr_hugepages, and the command waited for about 45 seconds. But it did complete and get me the hugepages, suggesting that there might be some work going on in the background to help get the memory required. I didn't have a change to investigate further though.
Looking to the future, I expect the eventual resolution will be transparent hugepages; they are in the latest UEK2 kernel, but even the very latest Exadata kernel is still UEK1 (2.6.32-400).
And to your other point, I wouldn't use all available memory for hugepages, as PGA, OS memory, etc don't use hugepages and must use what's left.
The perceived need for reboots to change HugePage values is the most frequent argument against hugepages I run into.
Maybe ACS should set the value during delivery to end the discussion.
Thanks for the info as from second hand I got told a customer setting huge pages near the 75GB limit suffered from memory leaks and instable compute nodes.
The advice was to set the value to 56 GB (but not higher) if you only run database instances on the compute nodes and if you need to run other applications decrease the setting according to the memory consumption of the app.
I remember discussing hugepages with ACS people in early Exadata days when hugepages were not set at all, and their issue is that they only give you a default "dbm" database. The SGA isn't sized to customer requirements and it has no data. So it would be possible to size hugepages for the default database, but there's no single value that would work for every client.
As for setting hugepages too high, this is one parameter that can be particularly dangerous to set excessively high, since hugepage memory is not swappable, so everything that doesn't use hugepages (effectively, everything but your SGA) must fit in the remaining memory, including PGA and the operating itself. So if you wouldn't size your SGA to 95% of system memory, you definitely shouldn't set hugepages to this value either. The tricky thing with hugepages is that the parameter itself is in 2m increments, so it might not be immediately obvious that you've overcommitted your available memory.
another point is Oracle doc 1067520.1 is recommending , for Data Warehouse systems, it is recommended to start with SGA_TARGET=8G and PGA_AGGREGRATE_TARGET = 16G with PARALLEL_MIN_SERVERS=32, PARALLEL_MAX_SERVERS=128. And to go upto sga_target=16GB. So, increasing sga_target > 16GB will it decrease the smart scans. What do you suggest?