0

Our database is slow in some very important use cases. I have traced it to a problem with some queries performing full text searches and single row accesses on a lot of records (db file sequential read) afterwards. Once the data is in memory, those queries become very fast.

The solution from us developers is now to put the hottest table into memory.

We currently have a SGA_MAX of 20 GB, SGA_TARGET is 14GB. ADDM and AWR recommend 25GB of SGA space.

Our infrastructure team will now upgrade our database to 40GB, however, the database admin sais that that much ram can kill oracle performance.

Is that true? I cannot possibly imagine that 20GB more RAM will make the database go slower.

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Improve this question
4
  • With such amount of RAM you have to use huge pages. That's on Linux. Otherwise your DBA is right especially if you have many connections to the database. With huge pages though performance will improve with bigger SGA.
    – Mindaugas Riauba
    Apr 1, 2015 at 12:09
  • Hi, thank you. We are on an old Red Hat Enterprise Linux with 2.6.17 Kernel. Do we have to alter the Kernel? Can you please give me a link?
    – Falcon
    Apr 1, 2015 at 12:10
  • oracle-base.com/articles/linux/…
    – Mindaugas Riauba
    Apr 1, 2015 at 12:16
  • You have quite mature version, so you should not be afraid. There were bug related to HUGE_PAGES, or when using MEMORY_TARGET Oracle won't start if more than 2GB of RAM were used. But it should be over. 25GB is not very much.
    – ibre5041
    Apr 1, 2015 at 12:52

1 Answer 1

Reset to default
3

Yes and No. With improper configuration, too much RAM can cause performance degradation.

For example on Linux platform, if HugePages is not configured, with large SGA and many database sessions, the pagetable will hold a significant amount of memory. Once at one our clients, the database server had 512 GB memory installed. They had about 4000-5000 database sessions with a 150 GB SGA, and this resulted in a 360GB pagetable size - so the server ran out of memory, and this caused hangs and reboots. After enabling HugePages, the size of the pagetable decreased to 1,5 GB (from 360 GB!!), saving and allowing to use more than 350 GB extra memory.

Another case, where the customer used Solaris x86-64 11.1, is the reverse of the above case. The database used 1 GB pages with 300 GB SGA, but because of a Solaris bug, 99.8% of the time spent handling large pages at OS level was overhead, caused the CPU to run in kernel mode, causing huge load on the server (300+ with 32 cores). The database was running fine with 24 GB SGA, but could not handle the load with 300 GB SGA. Disabling large pages in Solaris resolved this issue (there is a Solaris patch for it, but the local admins did not install it...).

These are however not Oracle limitations, but configuration issues. Oracle can handle large amount of memory. One of our customers has been using 500 GB+ SGA for a long time without any problems, and they are planning to increase it further.

Improve this answer
2
  • Thank you, we will try huge pages on our Red Hat Enterprise Linux 5. I have not found any problem reports for that system on the web. So i assume, it will be kinda safe, but still leaves some sort of feeling.
    – Falcon
    Apr 1, 2015 at 15:02
  • At my last job we had a production database server running Redhat 5.11 and an 11.2.0.4 database with an 80GB SGA. We used huge pages and it was fine. We also upgraded to Redhat 7.7, given that Redhat 5.11 hasn't been supported in a year.
    – Gandolf989
    Mar 4, 2020 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.