Oracle Real Memory Usage

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.