Memory usage by 3 oracle instances

[Mel_12]

Hello,

Can somebody show me how I can view memory consumption for three Oracle Instances running on a unix box. vmstat or top commands don't tell me much. Could it be that there is no delineation in memory usage for Oracle. The sho sga command from Oracle tells me a bit but I am curious to know what the OS command would spit out too.

Thanks

Mel

[twang]

A script to show all memory claimed by oracle user:
#!/bin/ksh
t=0

for i 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
t=`expr $t + $i`
done
echo "\n Total memory using by oracle is $t Kbytes \n"

[Anand_31]

Hi,

Hate to say it, but it takes a while to find the *actual* memory used by each process.

You could also find the amount of memory used by each instance to prepare the SGA. Note that this is only the shared memory segment attached when each instance starts.

login as root or as the owner of oracle software and at the UNIX prompt type,

$ ipcs -ma

This should show you all the segments attached and the size too. So, in your case, there will be 3 lines, one for each instance or SGA.

Each process that attaches to the SGA will show you the total SGA size + process memory usage. So, if you use ps command to look at the Virtual Size for each process, you will find that it is huge. The Resident Segment Size is also a bit misleading, depending on what is executing at that moment, input values, etc.

GlancePlus gives you a good view of each process and what it is doing. Drill down into the process and look at the process state and look at the open files. This gives you a very good idea.

Generally, i use

ps -flp

Put this in a small shell script to execute every second or so because the process could be doing a lot inside the SGA.

Hope this helps!

[Anand_31]

Oops!

Forgot to add that,

you need to run a couple of SQL Scripts to find what oracle is doing with all your memory. With that info, you can compare the figures from the OS commands and get a better picture.

let me know if you need all those long boring scripts :)

cheers
anand

[Bill Hassell]

Memory usage is a complicated task, especially if Oracle is running in 32bit mode rather than 64bit. The ps command can show local memory usage (which is fairly small compared to SGA) and ipcs -bmop can show the SGA (shared memory) areas. However, performance is almost always improved by allocating much larger amounts of shared memory for each instance. As several questions here in the forums have answered, Oracle needs many gigabytes of RAM and special handling is needed for 32bit versions to use memory effectively.

[Mel_12]

Thanks guys. I decided to stick with ipcs -m since other forums favor it too. However, the result for these shared memory range from:
401
402
403 for each of the instances. Sort of confusing to make good sense out of it. Here is the exact print out:

$ ipcs -m
IPC status from as of Thu Aug 28 12:06:11 2003
T ID KEY MODE OWNER GROUP
Shared Memory:
m 0 0x50000a6c --rw-r--r-- root root
m 401 0x0e99692a --rw-r----- oracle dba
m 402 0x0f73606d --rw-r----- oracle dba
m 403 0x0d4785be --rw-r----- oracle dba

[Anand_31]

Hi,

Each line is for 1 Oracle instance. Now, if you had used ipcs -ma, you would have got an output with more details. Look for the column SEGSZ. This is the size of the shared memory segment that EACH oracle (for that matter any process) process gets when it starts. In this case, Oracle would ask for a size equivalent to the SGA size (inclusive of Fixed and Variable components).

You need to know which instance started at what time (look into V$INSTANCE). Then you can actually say that 'this segment belongs to this instance'. You need to know how much you have configured in the init.ora for each instance. That way you can map the size of each segment in the ipcs -ma output to the actual instance.

Remember that this is the SEGSZ is only equal to your SGA size and does not include the processes attached to and using the SGA.

Regards
anand

[Bill Hassell]

400 megs for SGA is pretty small for good performance. Most DBAs would love to have 600 to 900 megs to use for each instance in 32bitr mode. In 64bit mode several thousand megs is fairly normal. Note that actual memory usage isn't as useful as actual performance. If all 3 are running OK from the end user's perspective, then all is well. If one instance seems slow, it is quite likely due to missing indexes or poorly designed SQL statements.