where do I find memory for oracle

[Ronelle van Niekerk]

Our dba's need an extra 200Mb of memory to assign to oracle.

How do I tell how much memory the machine has free?
I understand that top is not a good indication of this.

[Con O'Kelly]

Hi

Use glance ('m' option) if you have it installed. Otherwise look at the 'free' column in vmstat output. You need to multiple this value by 4 to give the amt of free memory in KB.

Cheers
Con

[Ronelle van Niekerk]

Thanks, I'll pass on the information.
I'm not the administrator of that box so can't run the commands myself.

-Ronelle

[Steven E. Protter]

ipcs show shared memory area. You might need to increase shmmax or shmseg to make Oracle run right.

Just becuase the DBA says he needs memory does not make it so.

swapinfo -tam

This shows total memory which is defined as physical memory plus swap.

In general, with heavy oracle applications my total swap is between 1.5 and 2.0 times physical memory.

I've recently become disposed to creating multiple swap areas, the first equal to half physical memory, the second at lower priority the rest of needed swap. This provides for very good Oracle performance during light loads and decent performance when the box is being pushed.

Here is a US only link to a good performance doc written by one of HP's oracle tuning experts.

http://www1.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-682735245+1058744592442+28353475&searchCrit=allwords

Lastly I'm attaching a data collection script to let you get to the bottom of any performance issues.

Ask those dba's why they think they need 200 mb and why they can't just modify the init.ora file and grab what they need then.

SEP

[Ronelle van Niekerk]

Thanks guys, they've got it sorted out.

Steven, as I said, it's not my box so I don't really care what the DBA's do on it.


-Ronelle

[Michael Steele_2]

Ask the DBA's if they have the ability to enlarge their SGA within each instance. This is the oracle cache parameter within oracle itself.

The HP-UX kernel parameter directly affecting the ORACLE SGA is SHMMAX which is limited in 32bit HP-UX systems to ~ 1gb. 64bit HP-UX versions can go significantly higher.

Run this command to find your O/S:

getconf KERNEL_BITS

Run this command to see the current value of SHMMAX:

kmtune -q shmmax

-to change shmmax-

kmtune -s shmmax=###
kmupdate -or- kmtune -u

See the DBA for the SGA procedure.

[Bill Hassell]

HP-UX is a virtual memory design so if you provide several gigabytes of swap space, you'll have plenty of memory. HOWEVER, if your DBAs insist on using 32bit instances of Oracle, they will be severely constrained with SGA due to a number of restrictions.

SGA is shared memory and by default, ALL 32bit programs must share the same 32bit map, and this includes shared memory, shared libraries and memory mapped files. All of this gets scattered inside a single memory map which leads to fragmentation. So if a DBA needs an SGA that is 700 megs in size, while there may be 900 megs available, due to fragmentation there may be several 300 meg segments but no 700 meg segment. This is an architectural limitation of 32bit proceses.

There are two solutions: change to a 64bit version of Oracle and all shared memory issues disappear. SGA can be thousands of megs in size if needed. Then the only issue is physical memory--swapping large memory areas will severely impact performance.

The second solution is to use memory windows (available on 11.0 and higher, 11.0 needs current patches). This allows each instance of Oracle to have a private shared memory map to avoid fragmentation.

Have your DBAs read the memory management and process management white papers in /usr/share/doc (for version 11i, the papers disappeared--look for them at docs.hp.com).

[twang]

Use the following code to find out how many ram was claimed by "oracle":
#!/bin/ksh

m=0
for j in `UNIX95= ps -e -o vsz=Kbytes -o ruser -o pid,args=Command-Line | sort -
rnk1 | grep -v Kbytes | grep ora | awk '{print $1}'`
do
m=`expr $m + $j`
done
echo "\n Oracle user claimed RAM is $m Kbytes.\n"