AIX 4.2 and Oracle 7.2/7.3 SGA/PGA and virtual memory

From: Jens Wartenberg <wartenberg_id_at_ibm.net>
Date: 1997/02/25
Message-ID: <33136C9B.2170@ibm.net>#1/1

Hi.

Can you help me with a question regarding aix-behavior?

SHORT:

---

I understand that AIX uses almost all not actively used RAM for "disk-cache" for the jfs (not using raw devices!). I am hovever not sure which of the following is "true":

Examples:					  MB
---------					----
RAM:						 128
Paging space:					+256

Assumption 1: (I believe this is "true")

---

Base consumption of RAM (SGA, AIX etc.):	  50
ACTIVE usersessions (30sessions*3MB*50%):	  45
						  --
						  95
Available for "disk-cache" (128 - 95):	  	  33

Assumption 2: (I hope this is not "true")

---

Base consumption of RAM (SGA, AIX etc.):	  50
Usersessions (ALL) (30sessions*3MB*100%):	  90
						 -----
						 140
Available for "disk-cache" (128 - 140):	   	 (none) 

(I should be able to see more than very little paging activity when
using vmstat).

If assumption 1 is "true", I will allocate 33MB ram more the Oracle SGA/PGA (using the values of the above example). If assumption 2 is "true", I will probably recommend the customer to buy more RAM.

LONGER VERSION FOR UNDERSTANDING:

---

I am working on getting the most out of Oracle-based applications in AIX-environments. The applications are integrated (MPS, Economy etc.) to cover 90%-95% of the needs of manufacturing companies. The main tables contain 1-10 million rows, and the database has a size of 4-14GB.

Most of my customers has 2-4 AIX+Oracle sessions pr. user, so the most frequently used functions are "at the fingertips". This means, that most of the time 50%-70% of the sessions are "sleeping".

My problem is, how I should size the Oracle-SGA/PGA with this memory-use in mind.
MY AIM IS to use the RAM available for active Oracle in stead of for aix-disk-cache, as Oracle should manage the cache more efficient as a result of having different priorities for index-data and table-data. Aix is of course not able to prioritize disk-cache in this manner.

Most of my customers seem to have to little memory allocated to SGA/PGA
(13-25MB), resulting in a lot of I/O activity which of course is bad for
performance.

I HAVE tried to study some books on the subject, but I am not sure about the behavior.

Thank you in advance for any comment.

Regards

Jens Wartenberg
Wartenberg Industri Data
Denmark Received on Tue Feb 25 1997 - 00:00:00 CST