0

I have a Server that has 128GB RAM. I have read here and there that the size of SGA + PGA can bee 80% of the RAM.
I wonder if there is any best practice for this configuration that could allow Oracle Database to utilize hardware even better?
There is also this consumption that this server will be used only to serve the Oracle database.

1
  • I am using Oracle 11g. and AMM is not an option because experience showed that it does not work well when there is a large memory.
    – Ali Hamidi
    Jan 6, 2020 at 5:26

2 Answers 2

1

What version of Oracle are you using? Are you actually setting values for the SGA components and/ or the PGA separately? If so, why? If you're using a recent version of Oracle, the starting point would normally be to set memory_target and let Oracle size the various memory components automatically. Unless, of course, you have some reason to need to set minimum values for certain memory components.

As for what to set memory_target to, you just need to leave enough memory for the OS and any other processes that need to run on the machine (including things like any backup manager you might be using, any monitoring software that runs on the server, etc.) It seems unlikely to me that you'd need 20% of 128 GB for that software. I'd guess that 8 GB would be more than enough leaving 120 GB for Oracle's memory_target. You could do better than guessing by taking a look at the actual memory utilization on the server over time.

3
  • "If you're using a recent version of Oracle, the starting point would normally be to set memory_target" - I don't think that's true anymore. I had success doing that with 11g, but since 12c Oracle doesn't allow automatic memory management anymore if the system has 4GB of RAM, which effectively makes the feature pointless.
    – Jon Heller
    Jan 5, 2020 at 18:18
  • 1
    Oracle seems to have two mutually exclusive 'recommendations'. First, they recommend the use of Automatic Memory Management (setting memory_target). But on Linux they strongly recommend using OS hugepages, which cannot be done if using AMM.
    – EdStevens
    Jan 5, 2020 at 19:07
  • I am Using Oracle 11g. when you have a large memory size AMM is not working good enough, and when you need to use hugepages you must disable AMM and use ASMM (Automatic shared memory management). so I have to Set SGA_TARGET and PGA_AGGREGATE_TARGET separately.
    – Ali Hamidi
    Jan 6, 2020 at 5:22
0

While AMM seems to work fine, as others have pointed out if you are using Linux, you will save a consiferable amount of memory if you use HugePages. This means that you can't use AMM. You can test what that value will be with AMM and no huge pages for a given SGA/PGA and you can test the same with HugePages and a similar SGA/PGA. If you are on Linux and your SGA is larger than 10G, then HugePAges makes sense.

$:~ > cat /proc/meminfo | grep -i PageTables
PageTables:       282712 kB
2
  • Thanks for the answer and you are right about HugePages. but the main question is about the proportion of SGA/PGA with the available RAM.
    – Ali Hamidi
    Jan 7, 2020 at 6:15
  • I usually allocate 80% of my memory to the database. So if I was asked to size the SGA/PGA, my swag would be 30GB PGA/70GB SGA. Within the SGA I would have about 10GB shared_pool, 65GB db_cache_size, leaving a few GB for large_pool_size, java_pool_size, steams_pool_size, etc. Whatever else seems relevant. I have a similar sized SGA and I have 60,000 huge pages total, 10,000 of which are free.
    – Gandolf989
    Jan 7, 2020 at 19:02

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.