Database – Oracle 9i Instance Memory Usage

databasememoryoracleprocessunix

My question deals specifically with a way to tell how much memory is being used by an Oracle instance on a UNIX (Solaris) server.

For example (I'm gonna really oversimplify this…), if I see that there's 8GB of RAM in use, is there a "simple" method (either through TOAD or some UNIX tool) to identify what portion of that memory is being used by a particular Oracle instance?

Through contact with an Oracle instructor and Sun support site searches, I was able to find "pmap" command usage to obtain the "anon" memory for Oracle processes, but hopefully someone will have an alternate solution.

I do have the Sun Management Center at my disposal (if that helps), and I'm open to any solution, even bribing the server with burgers and beer. Thanks everyone!

Best Answer

There's two types of memory for an Oracle instance. The SGA is shared memory, and PGA is memory only accessible to the individual processes. If you connect to an instance with appropriate privileges (eg DBA or with SELECT_CATALOG_ROLE) you can

SELECT SUM(VALUE) FROM V$SGA;

SELECT SUM(PGA_ALLOC_MEM) FROM V$PROCESS;

to see the totals for each of those.

Related Solutions

How to measure solaris process memory usage

prstat -s rss

'-s' sorts prstat output by rss column (see man page for other columns). Also try '-a' option for a per user accumulation.
ps -eo pid,pmem,vsz,rss,comm | sort -rnk2 | head

Top 10 RAM consumers. '-o pmem' displays percentage of resident memory i.e. RAM used by process.
ls -lh /proc/{pid}/as

Easy way to show total address space (vsz) of a process. Useful in combination with pgrep to accumulate by user, pattern, ... e.g.:
```
for pid in `pgrep -U webserver`; do ls -lh /proc/$pid/as; done
```

linux – How to Dump a Process’s Memory to File

I'm not sure how you dump all the memory to a file without doing this repeatedly (if anyone knows an automated way to get gdb to do this please let me know), but the following works for any one batch of memory assuming you know the pid:

$ cat /proc/[pid]/maps

This will be in the format (example):

00400000-00421000 r-xp 00000000 08:01 592398                             /usr/libexec/dovecot/pop3-login
00621000-00622000 rw-p 00021000 08:01 592398                             /usr/libexec/dovecot/pop3-login
00622000-0066a000 rw-p 00622000 00:00 0                                  [heap]
3e73200000-3e7321c000 r-xp 00000000 08:01 229378                         /lib64/ld-2.5.so
3e7341b000-3e7341c000 r--p 0001b000 08:01 229378                         /lib64/ld-2.5.so

Pick one batch of memory (so for example 00621000-00622000) then use gdb as root to attach to the process and dump that memory:

$ gdb --pid [pid]
(gdb) dump memory /root/output 0x00621000 0x00622000

Then analyse /root/output with the strings command, less you want the PuTTY all over your screen.

Best Answer

Related Solutions

How to measure solaris process memory usage

linux – How to Dump a Process’s Memory to File

Related Topic