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 ?
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:
- Memory used by System Global Area or SGA, that is implemented in AIX as a shared memory segment.
- 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:
egrep " oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} " |
grep -v egrep | 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
To simplify a little, memory that is used by a typical AIX process usually is divided into 2 major parts:
- User Data – Variables, dynamically allocated data, function parameters and return values etc
- 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:
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
880802 - A 86:59 89065 7088 58020 xx 88839 52048 2.0 0.0 ora_s00
AIX> ps -elf | head -1;ps -elf | grep 880802 | grep -v grep
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
240001 A oracle 880802 1 8 64 20 4d345400 95924 Mar 01 - 86:59 ora_s000_qaten
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).
...............................................................................
EXCLUSIVE segments Inuse Pin Pgsp Virtual
1486 22 306 1765
Vsid Esid Type Description PSize Inuse Pin Pgsp Virtual
144370 11 work text data BSS heap s 1108 0 135 1211
7d4e3 ffffffff work application stack s 132 0 18 150
...
...............................................................................
SHARED segments Inuse Pin Pgsp Virtual
1528419 0 418040 1565766
Vsid Esid Type Description PSize Inuse Pin Pgsp Virtual
1e483c 10 clnt text data BSS heap, s 13012 0 - -
/dev/fslv04:911099
30a0 90000000 work shared library text s 4914 0 76 9685
...
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:
- Memory that is requested by system processes (we will call it Virtual)
- 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:
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
1282210 - A 1:05 14598 19224 63308 xx 88839 52048 0.0 0.0 ora_arc
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:
-------------------------------------------------------------------------------
Pid Command Inuse Pin Pgsp Virtual 64-bit Mthrd 16MB
1282210 oracle 2815 22 1963 4806 Y N N
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:
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:
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
...
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
# ps v: Shortcut for real memory usage
AIX> ps v pid
# ps v statistics for all processes that belong to $ORACLE_SID instance
AIX> ps gvw | head -1; ps gvw |
egrep " oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} " | grep -v egrep
# Detailed memory usage by process:
# All memory segments allocated to particular process
AIX> svmon -P pid
# Relevant memory segments allocated to particular process
# and categorized as SHARED/EXCLUSIVE
AIX> svmon -P pid -O filteprop=data -O segment=category
# The same information as before but summarized
AIX> svmon -P pid -O filteprop=data
# The same information as before but for all $ORACLE_SID instance processes
AIX> svmon -P $(ps -elf | egrep " oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} " |
grep -v egrep | awk '{print $4}') -O segment=category -O filterprop=data
# ORACLE memory utilization scripts - you can find these files in the TOOLS section
# Total memory allocation for ORACLE instance(processes and SGA)
AIX> omem.sh $ORACLE_SID
# Memory allocation by ORACLE processes (from ps v)
AIX> omem_proc.sh $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)
AIX> ora_mem.pl -i $ORACLE_SID -c db_connection
# 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)
AIX> ora_mem.pl -i $ORACLE_SID -r detailed
April 16th, 2010 - 07:21
Awesome articles, i am trying all your scripts and test case but this not seems to work on my version of AIX. So the report with svmon -p do not match ps v .. i am a bit lost.
ps v 1376498
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
1376498 – A 0:00 0 4232 44024 xx 88840 43376 0.0 1.0 oracleG
svmon -P 1376498
——————————————————————————-
Pid Command Inuse Pin Pgsp Virtual 64-bit Mthrd 16MB
1376498 oracle 96587 19552 24535 97134 Y N N
PageSize Inuse Pin Pgsp Virtual
s 4 KB 49163 0 24535 49710
m 64 KB 2964 1222 0 2964
Vsid Esid Type Description PSize Inuse Pin Pgsp Virtual
48030 90000000 work shared library text m 2375 717 0 2375
69d7e 70000004 work default shmat/mmap s 35049 0 20806 43441
5b45 10 clnt text data BSS heap, s 10844 0 – -
/dev/fslv03:96608
0 0 work kernel segment m 584 502 0 584
15d81 70000001 work default shmat/mmap s 1605 0 441 2030
71078 90020014 work shared library s 1470 0 796 1492
a026 f00000002 work process private m 5 3 0 5
2a5ae 11 work text data BSS heap s 54 0 445 590
8002 9ffffffd work shared library s 47 0 1717 1732
2800a 9ffffffe work shared library s 36 0 4 37
4e5d7 9001000a work shared library data s 21 0 47 103
15b41 – clnt /dev/fslv03:114354 s 18 0 – -
35029 9fffffff clnt USLA text,/dev/hd2:102461 s 12 0 – -
4e0f7 ffffffff work application stack s 6 0 13 19
3a44a 8fffffff work private load data s 1 0 11 11
2e38f fffffff2 work application stack s 0 0 0 0
49cd6 fffffffd work application stack s 0 0 0 0
3a48a 8001000a work private load data s 0 0 147 147
32548 fffffff1 work application stack s 0 0 0 0
76439 fffffff9 work application stack s 0 0 0 0
e5e7 fffffff4 work application stack s 0 0 0 0
16521 80020014 work USLA heap s 0 0 108 108
524f0 fffffff0 work application stack s 0 0 0 0
1e5c3 fffffff3 work application stack s 0 0 0 0
1e523 fffffff7 work application stack s 0 0 0 0
11d80 70000002 work default shmat/mmap s 0 0 0 0
6e49f fffffff8 work application stack s 0 0 0 0
4a5b6 fffffffa work application stack s 0 0 0 0
25c4 fffffff5 work application stack s 0 0 0 0
46455 fffffffb work application stack s 0 0 0 0
5e5b3 fffffff6 work application stack s 0 0 0 0
6dd7f 70000003 work default shmat/mmap s 0 0 0 0
625bc fffffffe work application stack s 0 0 0 0
a5c6 fffffffc work application stack s 0 0 0 0
How can i match both ?
Or how can i filter svmon to show the same figure as “PS”, thanks in advance
April 16th, 2010 - 07:23
Saying it is not working, actually this is the -O option for SVMON that is not present on my AIX version 6.1
oslevel -s
6100-00-02-0750
April 16th, 2010 - 10:20
JC, Thank you
It is actually quite easy to figure out once you know what to look for. The important thing to remember is that svmon shows memory segments that the process has access to and only some of them are actually exclusive to the process (that is – represent process “real” memory).
I.e. work default shmat/mmap, even though visible in “process” svmon output are really SGA segments that are shared among all instance processes (check them – you will see that shmat/mmap segments are exactly the same).
But back to exclusive process segments – essentially, you need to know what they are (i.e. look at $g_rhSvmonExclusiveSegs hash in ora_mem.pl).
From your example, here is the list of exclusive process segments (I skipped those that have 0 pages allocated):
a026 f00000002 work process private m 5 3 0 5
2a5ae 11 work text data BSS heap s 54 0 445 590
4e5d7 9001000a work shared library data s 21 0 47 103
4e0f7 ffffffff work application stack s 6 0 13 19
3a44a 8fffffff work private load data s 1 0 11 11
Let’s calculate the size. First of all, remember that svmon shows memory in pages (not Kbs). Notice that a026 f00000002 work process private m 5 3 0 5 is designated as M page type (that is: 64K) segment while the rest are S (default, 4K)
Total size is: 5 * 64K + (54+21+6+1)*4K = 648K
Let’s now look at ps v output:
RSS (44024) is the size of CODE (Shared) + DATA (Exclusive), while TRS (43376) is the size of CODE only. Hence, DATA = RSS - TRS = 44024 – 43376 = 648K, and we have a match.
As a side note, Program CODE segment in svmon output is: 5b45 10 clnt text data BSS heap, s 10844 0 and as you can see: 10844 * 4 = 43376, it exactly matches ps v TRS.
April 16th, 2010 - 12:06
Thanks for all the calculation, is the list of “types” of memory from svmon -p always the same ?
Would it be possible to arrange the Perl code of your “ora_mem.pl” to overcome the missing “-O” option from the “old” version of svmon.
Is it possible to dowload only the executable “svmon” from a new AIX 6.1 TL2 and put it in my version of AIX ?
I’ll have a look at your perl script to see all process segment taken into account in your calculation.
Have a nice weekned, you made my day.. understood finally all process memory management in AIX which is not easy at first sight !!
April 17th, 2010 - 14:03
JC, I’m glad I could help
In my experience, the list of exclusive segments has been roughly the same in AIX 5.3 – 6.1 and ORACLE 9.2 – 11.2. Of course, the list is mostly experimental (it would have been nice for IBM to come up with documentation somewhere, but so far I have been unlucky to find it).
In some cases segment names might be slightly different, i.e. program text may be recorded as “perm text data BSS heap” if you are on JFS and “clnt text data BSS heap” if on JFS2.
The rough guideline is to look at work segments and discard:
shmat/mmap
shared library
shared library text
kernel …
Adjusting ora_mem.pl to older version of svmon should be fairly easy and I’ll link the ‘backward compatible’ version in the TOOLS section when it is ready. Alternatively, you can use omem_proc.sh script that gets its data from ps v but is still fairly accurate.
As far as uploading the “newer” version of svmon – I think it is more trouble than it’s worth – I would rather install the latest AIX ML.
April 20th, 2010 - 13:17
I uploaded the new version of the ORACLE/AIX Memory Monitoring Tool set ora_mem1.2.tar.gz (you can also see it in the TOOLS section).
It now includes ora_mem_bc.pl that can work with older versions of svmon that do not have -O parameter. The tool is compatible with AIX 5.3 and 6.1
April 22nd, 2010 - 06:17
Thanks a lot, working like a charm, by the way, i was using glogin.sql from tkyte which display user@Global_Name prompt under sqlplus, i did a bit od debug to see that was causing a problem when execting SQL statement and parsing the resulting columns.
Will try deeper use of your awesome scripts.
Thanks a lot.
September 10th, 2010 - 16:19
Hello Maxym,
When I execute the script to check the memory detailed :
ora_mem.pl -i $ORACLE_SID -r detailed
Display Memory Statistics for ORACLE Processes on AIX
Author: Maxym Kharchenko, 2010
Database instance test12345 is NOT running at ora_mem.pl line 367.
However, my database name is TEST12345, in uppercase, could be the problem ?
September 10th, 2010 - 16:32
Hello Fabio,
This is a slight bug on my part. When I accept “-i” parameter I convert it to lowercase in this line:
The simple workaround is NOT to specify -i parameter – in this case, database SID will be taken directly from $ORACLE_SID environment variable. Alternatively, just open ora_mem.pl and remove lc() call.
October 23rd, 2010 - 18:37
Hello,
This is really an amazing post.
Just one more question please:
Is the vmstat (free column), really shows the free amount of memory and take care of the shared memory segment ?
when i add the “ps v” with the SGA it still much less than wht reported by the vmstat.
Please advice
October 23rd, 2010 - 19:38
Hello John,
Yes, vmstat “free” column reports the amount of free memory frames (“memory frames” = “physical” memory in the machine).
Not quite sure what you meant by the 2nd part of your question. Perhaps, you could elaborate with some examples …
Regards,
Maxym Kharchenko
October 24th, 2010 - 00:19
Thanks Maxym.
The vmstat reports 99% of memory used.
We have 32G memory in total.
The SGA is set to 12G, can be confirmed from the ipcs.
I run the following to get the private memory for all processes:
ps
ef |grep ORACLE_SID | awk ‘{print $2}’ |xargs -I{} ps v {} |awk ‘{print $7$10}’ >ps_summary.outAdd all the output in the ps_summary.out , I found it is almost 10G
So I assume that total memory used by Oracle should be 12G + 10G = 22G
So we should still have 10G free , why the OS report all memory are really used.
Is this is correct way to check amount of memory used by Oracle or am I missing something here ?
Although the memory used are almost 100% still I don’t see any paging ( know we are using ASM).
SO I wonder how to accurate calculate the amount of memory really used.
We only have Oracle running on this server.
Thank you so much
October 24th, 2010 - 12:11
Hello John,
There might be multiple reasons of course … but probably the most basic one is that part of the memory is used to host persistent pages (persistent = “text” of programs or shared libraries or “file system cache”).
To find out exactly how much space is used by persistent pages, run:
3.0 minperm percentage
90.0 maxperm percentage
21.7 numperm percentage
and look for numperm stat – it tells you how many “file” pages you have in memory (in this case, it is: 21.7%).
Or, you could use svmon which is, in general, a better tool to look at AIX memory, i.e.
size inuse free pin virtual mmode
memory 8126464 8121265 5197 2217986 5845264 Ded
pg space 8388608 85067
work pers clnt other
pin 1444739 0 0 236671
in use 5841136 0 1743553
PageSize PoolSize inuse pgsp pin virtual
s 4 KB - 3106321 21739 307698 1362768
m 64 KB - 279898 3958 85857 280156
L 16 MB 131 0 0 131 0
In this case, we can see that 1743553 “client” pages are currently in memory (“client” is a part of “persistent” and means that they are coming out (most likely) from JFS2 filesystems).
By the way, ipcs might not always properly report SGA size as AIX might under allocate SGA shared segment unless you “lock” it. The better way to see SGA memory allocation is to:
Regards,
Maxym Kharchenko
November 8th, 2010 - 08:52
Hi Maxym,
This is the best post I ever read on AIX and Oracle memory, thank you very much.
I would like to ask about 2 more things please:
-If we have high swapping on the machine, how can we know which processes consuming the most swapping ?
-Do you suest to use the memory_target in 11g or better to use sga_target and pga_agregate_target ?
November 10th, 2010 - 14:44
Hello Nancy,
Thank you for your kind words. As for your questions:
There are multiple ways to see what is consuming paging space. Probably the simplest one is to use a regular ps command and calculate “in paging space” allocation as: SIZE-(RSS-TRS), i.e.:
Or use svmon as in i.e.:
Or you can download memory mapping scripts from this website (they display memory specific to particular database instance), i.e.:
The second question is more philosophical, of course. You can either use memory_target (to set dynamic memory management) or set ORACLE memory parameters statically (and lock memory on the OS level). There are benefits in both, although in my experience, on large scale production systems, static “manual” method often wins (I guess, if the system is both a) large scale and b) production, you’d better know how to size it before it is deployed). Of course, (and let me apologize for using a cliche here) – it really depends on the system.
Regards,
Maxym Kharchenko
December 7th, 2010 - 10:25
Excellent article. I have been using the knowledge to monitor the paging in our AIX systems. There is a parameter in AIX LRU_REPAGE which helps memory management in AIX specially with databases. We have implemented the change (LRU_REPAGE) and after the change, we have no or little paging. Once again, Excellent article on virtual memory management.
Asif Jafri
December 8th, 2010 - 09:02
Hello Asif,
Thanks, I’m glad that you found my articles helpful.
I’m pretty sure you meant LRU_FILE_REPAGE AIX parameter which controls whether AIX should “steal” only file pages (=0) or file AND computational pages (=1) when it needs to place new pages in memory. This is related to the “other side” of memory usage on AIX - using part of memory as a file cache, keeping most recently used file pages in memory (the concept is very similar to ORACLE buffer cache). While I have not written an article about this (yet), there are a couple of outstanding ones there, i.e. :
or
Cheers,
Maxym Kharchenko
December 16th, 2010 - 16:26
Very good artical! Really useful for an AIX admin.
I can’t find and download attached scripts:
omem.sh
ora_mem.pl
omem_proc.sh
Can you post them on the comment?
Thanks,
Wei L
December 16th, 2010 - 16:45
Hello Wei,
Thanks. The scripts are available in the TOOLS section.
Here is a direct link: http://intermediatesql.com/wp-content/uploads/2010/04/ora_mem1.2.tar.gz
Maxym Kharchenko
April 9th, 2011 - 11:06
Hello Maxym:
Thank you for the articles. I have been trying to debug a performance problem. I was wondering whether you can add your insight.
AIX 5.3
There is %70+ usage of swap space – total 15GB:
I am assuming that this is swap space used. I have read that the value represents space reserved as well as used. Can you please add your thoughts.
numperm =3-4<%minperm set at 20, therefore this might be causing the paging. I understand the VMO parameters part, but my other question is towards the output generated by ps gvw. I was hoping that the sum of all the values of SIZE-(RSS-TRS) for each line would be close to or equal to the 70% of 15GB, ~10.5GB. However the value is around ~3GB.
I am unable to pull svmon for an individual process by Pid because of process does not exist error.
Can you please add your thoughts.
Thank you.
April 9th, 2011 - 14:35
Hello Spring2011,
With default AIX 5.3 paging space policy, paging space is not deallocated when date is re-paged for reads (it is deallocated, however when it it re-paged for writes)
This means that ps (and svmon) may double count memory pages.
Here is a simple example:
Your program = 12 Gb
Initially: MEMORY: 12 Gb, Paging Space: 0
Add memory pressure (your program is inactive): MEMORY: 2 Gb, Paging space: 10 Gb
End memory pressure and READ your memory pages: MEMORY: 10 Gb, Paging space: 10 Gb
I described this process in some detail here: http://intermediatesql.com/index.php/aix/how-aix-paging-space-works-part-1-why-your-program-memory-footprint-gets-bloated-sometimes/
Regards,
Maxym Kharchenko
April 10th, 2011 - 23:37
Hi Maxym, thank you for your response. So its best not to page at all.
Now, I am unable to understand another concept. A vmstat -v output shows a very low numperm value while there are a lot of free pages left. Even though the minperm value is not tuned, I would expect that the free memory be used by either the system, processes or filecache. NMON graph shows usage for system and processes but the file system cache is very low.
However, I do see the numperm jump back to 60-70% in an hour or so. But I am unable to explain this frequent behavior. Can you please direct me to documentation or add your thoughts. Thank you.
#vmstat -v
7340032 memory pages
7011862 lruable pages
4653391 free pages
5 memory pools
1676341 pinned pages
80.0 maxpin percentage
20.0 minperm percentage
80.0 maxperm percentage
2.1 numperm percentage
149284 file pages
0.0 compressed percentage
0 compressed pages
1.9 numclient percentage
80.0 maxclient percentage
133517 client pages
0 remote pageouts scheduled
467676 pending disk I/Os blocked with no pbuf
2828055 paging space I/Os blocked with no psbuf
75234 filesystem I/Os blocked with no fsbuf
0 client filesystem I/Os blocked with no fsbuf
1131917 external pager filesystem I/Os blocked with no fsbuf
0 Virtualized Partition Memory Page Faults
0.0 Time resolving virtualized partition memory page faults
April 11th, 2011 - 00:05
Is this by any chance a database system ?
In this case, most of the “file” operations are likely “direct reads/writes” and do not go through cache …
The “jumps” in numperm are probably related to regular file operations you can use filemon to investigate that further.
May 2nd, 2011 - 12:08
Hi Maxym,
thank you for your excellent explanation of memory usage on AIX. I’ve launched the script omem_proc.sh and I got the following output from one of production machine:
….
331948 26.80 0.00 26.80 oracleGOLDPROD
2011232 26.80 0.00 26.80 oracleGOLDPROD
516192 26.84 0.00 26.84 oracleGOLDPROD
2097226 26.89 0.00 26.89 oracleGOLDPROD
1405092 28.30 0.00 28.30 oracleGOLDPROD
1794168 29.36 0.00 29.36 oracleGOLDPROD
1564724 46.86 0.00 46.86 ora_dbw0_GOLDPROD
-1 130.47 0.00 0.00 TEXT SEGMENT
———- ———- ———- ———- ————————
PID InMem Paging Virtual COMMAND
———- ———- ———- ———- ————————
TOTAL: 1185.48 0.00 1055.01 Processes: 109
I wonder how can the Total Virtual Memory is less than the InMem size. Should the Virtual Memory be greater or equal to the InMem size?
Thanks for any of your help!
May 2nd, 2011 - 12:22
Hello Chen,
There is a slight issue with the script – as you can see, when I calculate virtual size, I exclude TEXT segment from the calculation. This is, of course, not entirely correct and the reason for it was to make script simple.
In most case, this error is insignificant, but since your database is fairly small and entirely in memory, you can see this discrepancy.
Regards,
Maxym Kharchenko
May 3rd, 2011 - 11:23
thank you for the clarification, I’m now aware of the issue.
Cheers!
July 8th, 2011 - 12:16
Does it make sense to measure top memory usage processes, knowing that the memory used by a process includes shared memory segment, shared code segment, and its own private segment. Given that the big SGA number we give to our databases, the shared memory segment value is high, the shared code piece ($ORACLE_HOME/bin/oracle) is also a big, it’s not a surprise we see oracle kernel processes taking top memory usage positions.
On AIX, due to file buffering, AIX memory in use always appears high as what we have observed. Directly measuring memory in use against total memory available do not seem make much sense on AIX. What is the proper measure/data to capture for AIX memory usage ? And also how to get the top memory consuming processes.
Thanks.
July 14th, 2011 - 12:00
Hello Lisa,
Sorry for the long delay …
Anyway, of course, it makes sense to measure memory utilization of top ORACLE processes if we want to make sure our ORACLE instance does not overrun available memory. But we also need to be smart about how to measure it correctly. This (and related) articles are all about smart measurements, how to ignore shared code segments etc … But you are right, top or topas might misrepresent memory usage.
Also, if you are running ORACLE on AIX, you actually do not want to use file buffering and should rather use direct disk operations for database files (i.e. mounting database filesystems with cio option or using ASM).
As for the memory measurement tools – check out the ones attached to this article (i.e. omem.sh or ora_mem_bc.pl) – in them I made every attempt to report real memory utilization, excluding all the stuff that is shared between the processes (and hence mostly irrelevant).
September 22nd, 2011 - 13:17
Excellent work.
February 22nd, 2012 - 12:07
Hi Maxym,
Nice article. Have a question of you please…. I’ve executed both omem.sh and ora_mem.pl against a given instance and, despite some minor differences between them, neither matches oracle sga_target setting.
Is this related to what you discussed in “How AIX Paging Space works” paper? Mean, since we are not locking SGA, then AIX will grant memory to instance upon request rather than a “sga_target” size chunk upon startup. Is that so? Also, as seen below, how come requested (virtual) sometimes shows smaller than RSS? Thanks
—
SQL> show parameter sga
NAME TYPE VALUE
———————————— ———–
——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 7008M
sga_target big integer 7008M
#######
======================================================================
=====
Memory used by ORACLE instance:
========================================================================
===
SGA:
In Memory (RSS): 393 Mb
Requested (Virtual): 393 Mb
Segments: 28
Processes:
In Memory (RSS): 693 Mb
Requested (Virtual): 425 Mb
Processes: 27
Total:
In Memory (RSS): 1086 Mb
Requested (Virtual): 818 Mb
########
Displaying memory statistics for ORACLE instance: Report mode: basic
Database session information has NOT been requested
…
…
———- ———- ———- ———- ———- —–
——————–
PID Total InMem Paging Virtual Pages
COMMAND
———- ———- ———- ———- ———- —–
——————–
TOTAL: 1377.68 1317.92 59.76 1358.47 s M Processes:
48
February 22nd, 2012 - 18:28
Hello John,
You got the gist of it: ORACLE allocates regular memory in AIX lazily (large pages are a different story but I do not believe you are using them since lock_sga=FALSE).
So, sga_target (or, more precisely sga_max_size) is a MAX memory allocation that can be achieved, not the actual allocation. This is precisely, why I came up with these tools to see how much memory is actually used
As for why virtual size < RSS - this is likely because my scripts are slightly simplifying things. When calculating process memory usage they do NOT take into account TEXT segment (ORACLE code). For large instances with lots of processes, this deficiency is minuscule, but in your case, since the instance is very small, the deficiency is significant. Just ignore it and use RSS size in your estimations.
Regards,
Maxym Kharchenko