Oracle Performance – What Happens When SGA and PGA Reach Limits?

linuxoracleperformance

I have the following doubt, I have a server with Oracle 9i and another with Oracle 12c, in both cases I have the doubt that happens when Oracle reaches the limit imposed by the defined memory parameters (SGA and PGA)? … Example hypothetical:
I have a server with 32GB of RAM in which I only have running an instance of oracle 12c which has assigned the following parameters: 

sga_max_size = 19327352832
sga_target = 19327352832
pga_aggregate_target = 10737418240 
pga_aggregate_limit = 12884901888

If it were to reach the limit of those parameters that would happen ?, the operating system (RedHat) would begin to swap?

Best Answer

SGA: nothing serious, it is a fix sized memory area, once it gets full, the database reuses memory that is rarely accessed using LRU (least recently used) algorithm

Memory Architecture

PGA: Once PGA_AGGREGATE_LIMIT is reached, the database aborts calls of sessions having the most untunable (like PL/SQL collections) PGA memory, and those sessions receive ORA-04036 error. If that does not help and PGA usage is still above the limit, the database may even kill those sessions:

PGA_AGGREGATE_LIMIT

Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.

SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

(19327352832 + 12884901888)/1024/1024/1024 = 30

You have 32 GB memory in your server, you have allocated 30 GB memory to the database, and there is nothing else running on it, as you said. With proper configuration and sizing, you should not see swapping under normal circumstances:

  • hugepages for decreasing page tables
  • filesystemio_options=setall for direct I/O and avoiding filesystem caching
  • vm.swappiness set to 10 or even lower
  • sizing: reasonable amount of database sessions/processes

This does not mean the system will never swap. For example:

  • SYS sessions and background processes are not limited by PGA_AGGREGATE_LIMIT. You can still have rogue procesesses consuming PGA memory above the limit causing swapping.
  • If you use and unreasonably sized connection pool, like 100s or even 1000s of database processes, those processes have some private memory that is not part of PGA, so they add up on top of the SGA + PGA limit. This is especially significant on AIX, fortunately not so much on Linux.
Related Question