I’ve been struggling with that for a while now, so in this post I’d like to explain what I’ve found so far and what I’m missing. If you can help me to complete the picture that would be excellent!
Oracle Memory Usage
The main question I have is: how much memory does Oracle take? A simple question, but the answer, it seems, is not so much.
Oracle, as we know, have two main memory structures: SGA (which is shared between the processes) and PGA (which is private for each process). The SGA is defined by the SGA_TARGET and SGA_MAX_SIZE initialization parameters, and the PGA by the PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT. With this it’s quite simple to calculate the theoretical max memory usage, but what about the real one?
Theoretical Max Memory Usage
The maximum amount of memory Oracle can take is the value of SGA_MAX_SIZE + PGA_AGGREGATE_LIMIT. While in most cases we can take SGA_MAX_SIZE + PGA_AGGREGATE_TARGET (unless it’s not configured properly and we exceed it all the time, but let’s ignore this).
However, this is only the theoretical maximum. How do I get the REAL usage?
PGA Real Usage
PGA real usage is not hard to find, as it is tracked by Oracle. We can query V$SESSTAT where I can see the real PGA for each session and simply sum everything. Like this:
SQL> select name.name,sum(stat.value)/1024/1024 PGA_MB
2 from v$statname name,
3 v$sesstat stat
4 where name.statistic#=stat.statistic#
5 and name.name like '%pga%'
6 group by name.name;
NAME PGA_MB
---------------------------------------------------------------- ----------
session pga memory 504.936539
session pga memory max 549.626602
SGA Real Usage
This is where it gets tricky. In Linux there are all kind of tools to see memory usage of processes (like pmap, ps, top, etc.). The problem is that it will provide the memory each process uses, but unfortunately, some of it is the SGA so it’s actually shared. If one process uses 1GB of memory and another process also uses 1GB, it doesn’t mean that both together use 2GB. Some of it is the same memory (the shared SGA that both are using), but how much?
Oracle Real Memory Usage
When searching on that I talked to Baruch, a colleague from the past and an amazing Linux expert (as well as DBA). We talked about pmap as well as other options to look at the memory from the Linux side. We also found Ludo‘s post about it, so I looked at it. He uses pmap and use sort and uniq to eliminate the duplicate SGA pieces, which is smart. But something still doesn’t work for me…
Look at this:
[root@server scripts_new]# ./mem_usage.sh
orcl : 13638096
[root@server scripts_new]# free
total used free shared buffers cached
Mem: 8172752 8040508 132244 5429972 183756 6540192
-/+ buffers/cache: 1316560 6856192
Swap: 15625212 11688 15613524
As you can see, Ludo’s script shows that the database total memory usage is 13.5GB. But this server has only 8GB and the Linux claims that only 8GB is in use, so how can Oracle take so much? Also, this database has SGA_MAX_SIZE of 5GB and a PGA_AGGREGATE_LIMIT of 2GB, so it shouldn’t exceed 7GB. However, this database uses ASM, so maybe there is an issue with that, but even if I eliminate any process related to ASM I still get 11.5GB.
How About Grid?
As I mentioned, this database uses ASM, so it has GI for standalone server installed as well. How much memory does the GI take? I couldn’t find anything about it in the documentation, I only found that the minimum requirement is 8GB (for 18c based in the installation guide). But does it mean that the GI itself take 8GB and if I’d like to have a database I need more? It’s not really clear.
I used the same technique that Ludo used to get the GI and ASM memory usage on the same server, and I got that the GRID takes almost 6GB and the ASM about 3.5GB. This might fit the 8GB requirement, but that’s on top of the DB usage. So on this server the GRID takes 6GB, the ASM takes 3.5GB and the database takes 11.5GB, that’s 21GB on an 8GB server while the OS says only 8GB is in use.
Do You Have Any Input?
That’s where I’m at right now. Do you know anything I don’t? Any idea? Any other pieces that might help completing this puzzle? Feel free to comment below and if I learn interesting things I’ll compose another post about it.
Hi Liron,
Isn’t V$SGASTAT.BYTES supposed to give you exactly the memory info that you are looking for ?
Just an idea … as you know, I am just a developer, not a DBA … 🙂
Cheers & Best Regards,
Iudith Mentzel
Hi Liron,
In your calculations did you take into account the memory assigned to each connection?
I usually use this formula, although it is not exact, it generates an approximation to size the RAM memory.
(SGA_MAX + PGA_LIMIT + PROCESSES * 4MB) * (number of CDBs)
Regards.
Hi Facundo,
I didn’t add process memory that is not part of the PGA, you are right.
However, are you sure you need to multiply the PGA+SGA +processes in #CDBs? Aren’t these global for the entire instance?