There’s a way to know the REAL memory usage by Oracle Instance, including all connecting processes and using the shell rather than a connection to oracle?
The short answer is “I think so” 🙂
Summing up RSS column from ps output, is not reliable because Linux uses a copy-on-write on process forks and also doesn’t take into account correctly the shared memory and other shared allocations.
I’ve come across this post on Pythian’s Blog from Marc Billette.
While it seems good I’ve had discording results depending on platform and release.
Instead, I’ve tried to create a shell snippet that always uses pmap but works differently and SEEMS to work correctly on Linux ans Solaris.
Basically, using the pmap script I get a lot of information about the different memory areas allocated to the process:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
21010: ora_d000_db1p 0000000000400000 208908K r-x-- /ccv/app/oracle/product/11.2.0.3/bin/oracle 000000000D012000 1536K rw--- /ccv/app/oracle/product/11.2.0.3/bin/oracle 000000000D192000 1040K rw--- [ heap ] 0000000060000000 12288K rwxs- [ dism shmid=0x4300000e ] 0000000080000000 1036288K rwxs- [ dism shmid=0x7600000f ] 00000000C0000000 12K rwxs- [ dism shmid=0x4f000011 ] FFFFFD7FFC7A0000 64K rwx-- [ anon ] FFFFFD7FFC7BD000 704K rw--- [ anon ] FFFFFD7FFC86E000 200K rw--- [ anon ] FFFFFD7FFC8A0000 312K rw--- [ anon ] FFFFFD7FFC8EF000 1280K rw--- [ anon ] FFFFFD7FFCA30000 64K rwx-- [ anon ] FFFFFD7FFCA4F000 256K rw--- [ anon ] FFFFFD7FFCA90000 64K rwx-- [ anon ] FFFFFD7FFCAB0000 36K r-x-- /lib/amd64/libuutil.so.1 ... |
Initially I’ve tried to decode correctly the different kinds of memory the same way other scripts I’ve found online do:
|
1 2 3 4 5 6 7 8 9 |
rwxs- = shared memory rw--- = private heap rwx-- = private code stack r-x-- = shared code stack (?) etc... |
but finally the ADDRESS is the same from different processes when the memory area is shared, so my script now just get a unique line for each address and sums up the memory size (not the rss one!):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
username=`whoami` sids=`ps -eaf | grep "^$username" | grep pmon | grep -v " grep " | awk '{print substr($NF,10)}'` total=0 for sid in $sids ; do pids=`ps -eaf | grep "^$username" | grep -- "$sid" | grep -v " grep " | awk '{print $2}'` mem=`pmap $pids 2>&1 | grep "K " | sort | awk '{print $1 " " substr($2,1,length($2)-1)}' | uniq | awk ' BEGIN { sum=0 } { sum+=$2} END {print sum}' ` echo "$sid : $mem" total=`expr $total + $mem` done echo "total : $total" |
This should give the total virtual memory allocated by the different Oracle instances.
The results I get are plausible both on Linux and Solaris.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ ./test_mem.ksh db1p: 3334852 db2p: 2052048 db3p: 6765280 db4p: 2687928 db5p: 4385616 total : 19225724 |
If you find any error let me know and I’ll fix the script!
—
Ludovico
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Hi Ludovico
I have issue running on exadata X5-2L
[oracle@pp-xd01db01@(SID unset) ~]$ sh mem.sh
tdwcprod1 : 0
dsprod1 : 0
api2prod1 : 0
wc2prod1 : 0
apx2prd1 : 0
dehyprod1 : 0
wcprod1 : 0
wcxprod1 : 0
crn6prod1 : 0
wb6prod1 : 0
sesprod1 : 0
coprod1 : 0
ebs2prd1 : 0
hrprod1 : 0
secprod1 : 0
pphrprod1 : 0
ppplay1 : 0
cos5prod1 : 0
brecprd1 : 0
bconprd1 : 0
apx5prd1 : 0
iamprod1 : 0
cog10prd1 : 0
new19c1 : 0
mstrprod1 : 0
apxiprd1 : 0
discprod1 : 0
toscprod1 : 0
epiprod1 : 0
nauxprod1 : 0
twsprod1 : 0
auxprod1 : 0
swprod1 : 0
total : 0
Hi Alex,
the commands in the script are quite simple… you might try to execute them one by one before piping the output to the next one, and see where it does not work as expected.
Good script . After running the script I got out put as well as compared with actual oracle using . Login to each database and executed my own script how much is using
Hi, Mir,
Maybe it lacks privileges to run pman. Try “sudo pmap” or log in as root.
Regards,
Flávio.
oracle@w1676apypk008:~/Mir> ./test_mem.sh
TESPRISM : 0
TESARCH : 0
TESADHOC : 0
DEVPRISM : 0
DEVARCH : 0
DEVADHOC : 0
TRAARCH : 0
TRAADHOC : 0
TRAPRISM : 0
total : 0
Hi Mir,
your Oracle instances are very lightweight 🙂
Jokes apart, you should try to reproduce each step in the script to see if it fits with your OS/version.
On Linux 6 and Solaris 10 works pretty well, but maybe in your environment there is something that prevents it from working.
Particularly, I expect something not working here:
mem=
pmap $pids 2>&1 | grep "K " | sort | awk '{print $1 " " substr($2,1,length($2)-1)}' | uniq | awk ' BEGIN { sum=0 } { sum+=$2} END {print sum}'—
Ludovico
Hi Maris, thank you for sharing your thoughts!
I was aware of being counting more than once the size of shared libraries… I should calculate separately the total rather than summing up all the memory consumption from the different instances.
I think I’ll spend a little more time on different memory areas (based on the address) to see if I can find any correlation between the VM Size and the multiple different RSS sizes I get from each process.
However I’ve big problems with pmap -x on solaris, it takes a huge amount of time and sys cpu consumption on heavy loaded systems so I can’t run it on customer environments. Using pmap without the -x was “quick & dirty” and the results are almost plausible.
Let me know if you discover more! 😉
—
Ludovico
Hi Ludovico,
The more I read about memory usage the more complicated it becomes. I’m not sure, but I don’t think this is entirely accurate.
I think “pmap ” shows the virtual memory mappings for each process, which is not the real used memory. see here:
[root@s1 ~]# pmap 11203 | grep libc-2.5.so
000000331e000000 1340K r-x– /lib64/libc-2.5.so
000000331e14f000 2044K —– /lib64/libc-2.5.so
000000331e34e000 16K r-x– /lib64/libc-2.5.so
000000331e352000 4K rwx– /lib64/libc-2.5.so
[root@s1 ~]# pmap -x 11203 | grep libc-2.5.so
Address Kbytes RSS Dirty Mode Mapping
000000331e000000 1340 480 0 r-x– libc-2.5.so
000000331e14f000 2044 0 0 —– libc-2.5.so
000000331e34e000 16 16 8 r-x– libc-2.5.so
000000331e352000 4 4 4 rwx– libc-2.5.so
The outputs from “pmap ” match the Kbytes from “pmap -x ” which is the virtual memory. I also observe RSS (which should be the real used memory” is a lot smaller then virtual memory in some cases – like the one above. Could it be oracle process hasn’t loaded the whole library into the memory, but just a portion?
Another thing. Have a look at a process from another DB instance:
[root@s1 ~]# pmap -x 32103 | grep libc-2.5.so
000000331e000000 1340 480 0 r-x– libc-2.5.so
000000331e14f000 2044 0 0 —– libc-2.5.so
000000331e34e000 16 16 8 r-x– libc-2.5.so
000000331e352000 4 4 4 rwx– libc-2.5.so
[root@s1 ~]# ps -ef | egrep “11203|32103”
oracle 11203 1 0 Nov15 ? 00:01:38 ora_qmnc_PROD1
oracle 32103 1 0 Nov11 ? 00:02:31 ora_rsmn_CAT1
The memory addresses are the same, so the libraries are shared even among instances.
And the last one – you also mentioned “copy-on-write”, there’s one one specific thing about it with “anonymous memory”, if I understand the concept correctly, the memory is physically used only by the dirty buffers, otherwise it’s all linked to a single page of /dev/zero. Read more about it here http://landley.net/writing/memory-faq.txt
So:
– the idea is very nice to sum up memory based on unique addresses across all platforms
– need to be careful with shared libraries as they can be used by multiple instances (not a big deal as the size is not big though)
– I think RSS should be used instead of virtual memory for the calculations.
– careful with “[ anon ]” segments, need to count dirty pages only.
– I read quite a lot about the memory management recently, but I have a feeling I understand around 10%, so I might be wrong too.
Maris