Reference: http://intermediatesql.com/index.php/aix/how-oracle-uses-memory-on-aix-part-1-processes/

In this post I am going to talk about how ORACLE allocates and uses memory when running on AIX, but I will also talk about the power of approximation and how it can sometimes be misused for ill purposes 

On the outset, ORACLE/AIX memory “deal” seems simple enough – obviously, ORACLE will use memory when it runs and many AIX commands (such as vmstat or ps) will show memory usage both system wide and specific to particular process. But, as always, the devil is in the details and the effect of those “details” may be far from subtle.

So, why don’t we go ahead and find that devil, shall we ?

Let’s say that we have an ORACLE instance running on one of our AIX servers. How do we find out how much memory it is using ?

First of all, let’s consider what ORACLE memory consists of. Here we actually have 2 separate parts:

1. Memory used by System Global Area or SGA, that is implemented in AIX as a shared memory segment.
2. Total memory used by ORACLE processes that represent the instance.

Next, let’s determine how much memory is used by each part.

Fortunately, SGA size is readily available -> just run ipcs -bm command and it’s there (The size of shared segment should match sga_max_size in 10g or memory_max_target in 11g).

Calculating “process” memory is a bit more involved as we need to sum memory used by ALL instance processes, but it is not very difficult either. ps -l command will show individual process memory size and a simple awk script can sum all of them up, such as:

The sum of SGA size and process size will obviously represent total memory usage by ORACLE instance and you can see the entire calculation in the slide below.

Well, let me tell you right away where the punch line is – this calculation is WRONG – in a typical case, it overestimates ORACLE memory usage by, at least, a factor of 5-10 or more.

But where exactly have we made a mistake ?

Have we identified ORACLE memory parts incorrectly ? No, ORACLE memory does indeed take 2 parts: SGA and combined per-process memory from instance processes.

Have we made a mistake in the summing formula somewhere ? Well, not really, the formula is trivial – not much room for errors here …

The reason our answer is wrong is more subtle and is related to the fact that modern operating systems (AIX included) employ a number of smart tricks to allocate and manage system memory.

For whatever reason, most AIX commands do not take these tricks into account and display memory “sizes” as if nothing is going on (read: the way it was done in the 70s).

Nevertheless, the tricks are there and their effects are real, so let’s see how we can “out trick the trickster” and find out the real memory allocation.

We will start with process memory.

To simplify a little, memory that is used by a typical AIX process usually is divided into 2 major parts:

1. User Data – Variables, dynamically allocated data, function parameters and return values etc
2. Program Code (program itself, shared libraries etc This is also known as: Text)

While user data is obviously unique to each process and will change in size slightly (or not so slightly) as the process runs, the code part is different – it is static and, moreover, it is exactly the same for all programs that run it.

Since all ORACLE processes that make ORACLE instance are “instantiated” from the same binary disk image – $ORACLE_HOME/bin/oracle (you did know that, didn’t you? ), there is no reason for operating system to duplicate ORACLE code segment – instead AIX loads it in memory once and then links to each process.

Let’s prove this:

The interesting observation here is that under normal circumstances and especially for idle (ORACLE) processes, code segment will be much larger than data (yes, ORACLE has a big code!), reaching up to 90-95% of the size reported by PS. That means that for ORACLE processes:

Alternatively, I guess, you could say that ps does report size properly but only if this was the only process in the system – you pick your poison …

A simple shortcut to see the real memory size of the process (excluding memory that is shared) is to use ps v command:

Here, the SIZE column shows the virtual size of process DATA segment (we will talk about what virtual means shortly) and as you can see it is much smaller (7088) than SZ (95924) that is reported by ps -l.

A more precise way is to use svmon command, that displays all process memory segments and can neatly group them into SHARED and EXCLUSIVE categories (well, there is also SYSTEM, but that is another story).

Ok, we can see now that ps -l process size is bloated because it does not take sharable segments into account. But is this the whole story ?

Not quite, there is still one other notable trick in AIX bag …

Let me ask you this – would it be possible for the process to allocate 2 Gb of RAM on the system that only has 1 Gb of physical memory ?

The answer is: of course, and it happens every day on many systems (albeit, the ratio in this example is somewhat extreme). That is: most modern operating systems (AIX is no exception) are designed to handle workloads that require more memory than the system has.

So, right of the bat, when we are talking about memory, we may actually mean 2 quite different things:

1. Memory that is requested by system processes (we will call it Virtual)
2. Physical memory that the system has (we will cal it Real or Physical)

It should be obvious that if Virtual > Real, something must happen to the portion of Virtual memory that does not fit into Real memory for the system to continue working properly. What usually happens is that the excess of Virtual memory (normally the oldest or least used pages) is saved to a special area on disk called SWAP (or Paging Space in AIX).

I guess you see where I’m going with this – how do we know whether the memory allocated by ORACLE processes is really in memory or has it been swapped to disk?

Here, svmon and ps v will tell us the details again.

Let’s look at ps v shortcut first:

Remember that SIZE represents process Virtual size (or memory that is requested by the process). RSS represents In Memory size of CODE+DATA, while TRS represents In Memory size of CODE. So, In Memory size of DATA (what we really want to know) is RSS-TRS=11260 (Kb), which means that 19224-11260=7964 (Kb) is probably in paging space.

Once again, svmon will show a more precise information:

Notice that the Virtual size is 4806 (4K pages) and 4806*4=19224, so we have a match here. InUse (In Memory) size is 2815 (4K pages) and 2815*4=11260, so we have a match again.

Yet, look at Pgsp statistics. 1963*4=7852 which does not match our estimation from ps. Here we can see that the formula:

is not precise all the time – my guess is: some of paging space may be reserved even though the page is really in memory, conversely, there might be some resident memory leftovers after pages have been moved to paging space. In either case, svmon will generally provide a more accurate estimation (and you can see why ps v is only a shortcut).

To summarize, to see the true memory usage by ORACLE processes in AIX, you should adjust your estimation keeping these 2 facts in mind:

• Do not over count segments that are shared (that is – code)
• Recognize that some process memory may be in the paging space.

If you do NOT want to do these calculations by hand, you can download omem_proc.sh and ora_mem.pl tools from this site.

Finally, is there any way to control how much memory ORACLE instance processes are using ?

There are several process memory controls that can be implemented.

The simplest way is to use AIX ulimits – you can set maximum memory allocation limits, separately for process data (User Data), stack and rss (virtual memory) components. You can set these (per user) settings in /etc/security/limits (or through smit) and you can view them with ulimit -a command:

But limiting process memory usage in this way is like having a firing squad enforce parking rules – one small mistake and you are dead! Plus, ORACLE processes do not really know that they are NOT supposed to exceed AIX ulimits and some of them oftentimes (you know ORACLE …) might need to have a lot more additional memory.

So, we need another mechanism, one that is gentler and aware what ORACLE processes are doing, but at the same time, sane enough to not let ORACLE kill the system with unreasonable memory demands.

This mechanism is provided by ORACLE and there are actually two of them:

The older one – manual process memory management sets memory usage limits individually per process. MAX per-process memory allocation is controlled by parameter sort_area_size (with sort_area_retained_size acting as the required minimum). This is the only mechanism available up to ORACLE 8i and it will still be used in later versions if parameter workarea_size_policy is set to MANUAL.

The newer one – automatic process memory management sets memory usage limit collectively for ALL ORACLE server processes. It is controlled by pga_aggregate_target parameter and works when workarea_size_policy=AUTO.

A couple of things to remember about pga_aggregate_target:

• It is an advisory upper target that ORACLE will try to enforce, but might not be able to under extraordinary circumstances (i.e. you if have 5000 concurrent active sessions that need to sort data but only allocated 200M of pga_aggregate_target – there is no way 200M target will be met)
• It might not cover all the process memory. Rather what it covers is – various work areas: sort, hash, bitmap merge etc but if you decide to allocate another 1,000,000 item PL/SQL array in your session – ORACLE has no choice but to let you use memory for that (however, subsequent sessions will have to use less memory for sorting,hashing etc).

Ok, so I think we have a better idea now how to see the real memory usage by ORACLE processes and how to control that usage.

But what about the other (and arguably bigger) chunk of memory that ORACLE uses – SGA ? Stay tuned as we will talk about that in Part 2.