Strange Memory consumption by Oracle on AIX

[former_member185954]

Hello Experts,

I have a strange problem, which some of us must have observed on Oracle running on AIX.

We have two Oracle Databases running on a single UNIX host with 11GB of memory and 6 CPUs, the SGAs defined for both databases are not more than 1GB each.

I know that apart from SGA other oracle processes would also occupy considerable amount of memory, however what I see in the OS commands is a bit strange.

vmstat
System Configuration: lcpu=6 mem=11008MB
kthr     memory             page              faults        cpu     
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa 
 9  5 1870504  9622   0   0   0 236  579   0 3358 53653 16356 46  8 30 16

In the above output the fre value is to be multiplied by 4K to get the actual free memory available in the system, which is calculated to be 37MB ! In fact data taken at regular intervals (by Tivoli) shows that on an average 42MB is the amount of free memory left in the system.

I was not convinced with this, so I wrote a simple script to total the memory used by each process in the system as shown below:

cat chkfree.sh

sum=0

for i in `ps -Alo vsz`
do

        test=`echo "$i" | egrep "^[0-9]+$"`

        if [ "$test" ];  then
                sum=`expr $sum + $i`
        fi

done

# Multiply by 4K

sum=`expr $sum \* 4096`

sum=`expr $sum / 1024`

sum=`expr $sum / 1024`

avail=`vmstat | grep -i System | cut -f 3 -d '=' | cut -f 1 -d 'M'`

echo "Physical Memory Configured: $avail MB"
echo "Memory Used: $sum MB"

The above script shows the following results:

./chkfree.sh
Physical Memory Configured: 11008 MB
Memory Used: 7358 MB

Now according to this result about 3+GB is free, however standard AIX tools show that only 42MB is free.

This clearly shows that there is something not right or I am grossly missing something.

Has anyone seen this before and does anyone know what we can do about this?

Regards,

Siddhesh

[stefan_koehler · Accepted solution]

Hello Siddhesh,

to be honest i am not using your commands.. i always use nmon or topas.

http://www.ibm.com/developerworks/aix/library/au-analyze_aix/

I am not sure about the calculated values with your commands, but mostly the "free" memory is used by the filesystem cache (numperm). Please use nmon or topas and post the memory informations here.

Regards

Stefan

[former_member185954 · Accepted solution]

Hello Stefan,

thank you for your feedback.

We do not have nmon installed, the output of topas is as follows:

Topas Monitor for host:    xyz         EVENTS/QUEUES    FILE/TTY 
Wed Mar 25 15:56:00 2009   Interval:  2         Cswitch   40858  Readch  205.6M3 
                                                Syscall   60030  Writech5005.3K1 
Kernel   14.6 ;  |#####                       |  Reads     42637  Rawin         0 
User     47.8 ;  |##############              |  Writes     1582  Ttyout      874 
Wait     26.8 ;  |########                    |  Forks        16  Igets         0 
Idle     10.8 ;  |####                        |  Execs         9  Namei       164 
                                                Runqueue    6.0 ; Dirblk        0 
Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out  Waitqueue   9.0 
en1     968.5 ;  1194.0 ; 1040.5 ;  254.7 ;  713.8 
en0       1.5 ;     2.0 ;    4.0 ;    0.2 ;    1.3 ; PAGING           MEMORY 
en2       0.0 ;     0.0 ;    0.0 ;    0.0 ;    0.0 ; Faults     4692  Real,MB   11007 
lo0       0.0 ;     0.0 ;    0.0 ;    0.0 ;    0.0 ; Steals     1681  % Comp     68.9 
en3       0.0 ;     0.0 ;    0.0 ;    0.0 ;    0.0 ; PgspIn        0  % Noncomp  31.7 
                                                PgspOut       0  % Client   33.9 
Disk    Busy%     KBPS     TPS KB-Read KB-Writ  PageIn     2643 
hdisk292 12.0 ;   427.8 ;   65.5 ;  358.0 ;   69.8 ; PageOut    1183  PAGING SPACE 
hdisk107 11.0 ;   121.8 ;   24.0 ;   88.0 ;   33.8 ; Sios       3822  Size,MB    8192 
hdisk20  10.0 ;   130.5 ;   19.5 ;  118.0 ;   12.5 ;                  % Used      1.0 
hdisk116 10.0 ;   174.8 ;   23.0 ;  142.0 ;   32.8 ; NFS (calls/sec)  % Free     98.9 
hdisk155 10.0 ;    84.8 ;   12.0 ;   80.0 ;    4.8 ; ServerV2       0 
hdisk111 10.0 ;   225.0 ;   30.0 ;  152.0 ;   73.0 ; ClientV2       0   Press: 
hdisk140  9.5 ;    92.8 ;   12.0 ;   60.0 ;   32.8 ; ServerV3       0   "h" for help 
hdisk13   9.5 ;   196.5 ;   29.0 ;  180.0 ;   16.5 ; ClientV3       0   "q" to quit 
hdisk100  9.5 ;    93.0 ;   13.5 ;   72.0 ;   21.0 
hdisk106  9.5 ;   149.8 ;   22.5 ;  128.0 ;   21.8 
hdisk101  9.0 ;   175.2 ;   25.5 ;  130.0 ;   45.2 
hdisk145  9.0 ;    79.0 ;   11.5 ;   78.0 ;    1.0 
hdisk320  9.0 ;   373.0 ;   60.5 ;  308.0 ;   65.0 
hdisk147  8.5 ;   127.2 ;   22.5 ;   98.0 ;   29.2 
hdisk146  8.5 ;   219.8 ;   30.5 ;  142.0 ;   77.8 
hdisk306  8.5 ;   292.5 ;   54.0 ;  250.0 ;   42.5 
hdisk26   8.5 ;   132.5 ;   19.0 ;  124.0 ;    8.5 
hdisk7    8.5 ;   139.2 ;   22.0 ;  110.0 ;   29.2 
hdisk222  8.5 ;   404.0 ;   57.5 ;  348.0 ;   56.0 

Name            PID  CPU%  PgSp Owner 
oracle      3133544   5.5 ;  6.6 orapcm 
oracle      2105410   5.0 ;  5.9 orapcm 
oracle      1528044   5.0 ;  6.7 orapcm 
oracle      2801766   4.9 ;  5.9 orapcm 
oracle      3149896   4.5 ;  6.1 orapcm 
oracle      1564736   3.3 ;  6.2 orapcm 
oracle      3158068   3.2 ;  6.4 orapcm 
oracle       729112   2.8 ;  6.2 orapcm 
oracle      2957458   2.1 ;  5.9 orapcm 
oracle      3010686   2.1 ;  5.8 orapcm 
oracle      2977934   2.0 ;  6.8 orapcm 
oracle      2805972   1.7 ;  6.0 orapcm 
oracle      2900160   1.5 ;  6.6 orapcm 
oracle      1798182   1.1 ;  6.1 orapcm 
oracle      3051768   0.9 ;  5.9 orapcm                                                                                
oracle      3367064   0.8 ;  5.7 orapcm 
oracle      1704018   0.8 ;  6.2 orapcm                                                                                
lrud         274566   0.7 ;  0.1 root   
init              1   0.7 ;  0.7 root   
oracle      1380480   0.7 ;  5.8 orapcm

This being production instance database, we wouldn't be allowed to install it without a Change Management process.

Also, let me tell you that there is hardly any paging activity going on the server, which tells me that server is doing fine, however I am baffled by the fact that average memory free on the system is 40MB only!

The reason I am posting this question is, the data buffer quality in ST04 transaction is below 85% , it has been above 90% in the past, however recently it has seen some drastic decrease.

I have looked at what has changed and which queries are causing disk read/writes , the only thing that seems to have changed is the number of users in the system(the users have increased).

So I am also looking for a change in the buffer size (increase), however looking at the free memory, I am somewhat baffled.

Regards,

Siddhesh

[stefan_koehler · Accepted solution]

Hello Siddhesh,

no problem - the topas output is more than enough. So let's start.

At first here is the documentation about the values of topas:

http://publib.boulder.ibm.com/infocenter/systems/index.jsp?topic=/com.ibm.aix.cmds/doc/aixcmds5/topa...

> Also, let me tell you that there is hardly any paging activity going on the server, which tells me that server is doing fine

The values of PageIn / PageOut has something todo with I/O requests.. nothing with the memory usage.

That is the important part

MEMORY
Real,MB   11007
% Comp     68.9
% Noncomp  31.7

So in your case you have round about 11 GB memory. Nearly 69% of the memory is used by processes (like SAP work processes, Oracle shadow processes, SGA, etc.) - round about 32% is used for filesystem cache and so on.. so if you calculate you have round about 3.5 GB free memory that can be used by processes.

The usage of the filesystem cache is controlled by some parameters -> for more information check here:

http://publib.boulder.ibm.com/infocenter/systems/index.jsp?topic=/com.ibm.aix.prftungd/doc/prftungd/...

But keep in mind if you don't use DIO or CIO with the oracle database, maybe some content of the data files can be cached by the filesystem cache and can increase your performance.

Btw. i would also recommend to use nmon .. it is a great tool (also integrated in the newer AIX levels) with a better overview.

Regards

Stefan

[former_member185954 · Accepted solution]

Hello Stefan,

Thanks for that.

I'll read the links you provided and get back.

Regards,

Siddhesh

[former_member185954 · Accepted solution]

Hello,

Thanks Stefan, the links you provided gave me some good insights about the VMM that AIX will use to manage its Virtual Memory.

This information will help me fix my problem, hence I am closing the question.

Regards,

Siddhesh