How ORACLE Uses Memory on AIX. Part 1: Processes « Intermediate SQL

7Mar/1066##  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 ?

The straightforward (but naive) approach

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:

ps-elf

egrep" oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} "

grep-vegrep|awk'{sum += $10} END {print sum/1024}'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.

Estimating Process Memory Use in AIX

Trick #1 – Some memory may be shared

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:

ps -l usually significantly over reports memory size, and the real size is MUCH less 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:

ps880802

%%

88080286598906570885802088839520482.00.0

ps-elf|head-1ps-elf|grep880802|grep-vgrep

240001880802186420959248659Here, 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).

-P880802-Osegment-Ofilterprop

1486223061765

14437011110801351211

132018150

152841904180401565766

10130120

//911099

9000000049140769685

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 …

Trick #2: Some memory may be swapped away

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: ps1282210

%%

1282210114598192246330888839520480.00.0Remember 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:

-P1282210-Ofiltercat-Ofilterprop

64

128221028152219634806Notice 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:

Virtual Size = Size(In Memory) + Size(In Paging Space) 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 ?

How to control ORACLE process memory usage

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:

ulimit-a

()

()4194304

()

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.

Useful Commands

Regular AIX commands that should be available on any system …

ps v: Shortcut for real memory usage

ps

ps v statistics for all processes that belong to $ORACLE_SID instance

ps|head-1ps

egrep" oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} "|grep-vegrep

Detailed memory usage by process:

All memory segments allocated to particular process

-P

Relevant memory segments allocated to particular process

and categorized as SHARED/EXCLUSIVE

-P-Ofilteprop-Osegment

The same information as before but summarized

-P-Ofilteprop

The same information as before but for all $ORACLE_SID instance processes

-P(ps-elf|egrep" oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} "

grep-vegrep|awk'{print $4}')-Osegment-Ofilterprop

ORACLE memory utilization scripts – you can find these files in the TOOLS section

Total memory allocation for ORACLE instance(processes and SGA)

$ORACLE_SID

Memory allocation by ORACLE processes (from ps v)

$ORACLE_SID

Memory allocation by ORACLE processes (from svmon)

-c part is optional but will display useful session information

(you should connect to user with SELECT ANY DICTIONARY privilege)

-i$ORACLE_SID-c

Detailed memory report for ORACLE instance processes

See where memory is spent: user data, stack, shared library vars etc …

(you need to have a BIG screen here)

-i$ORACLE_SID-rTagged as: AIX, Memory, ORACLE, ProcessLeave a comment