Solaris

Notice that Oracle relies heavily on shared memory: in addition to listing all the processes for the database you need to issue a ipcs -m th display the shared memory segments and analyse what you get there.

I am no Oracle expert but i recall vaguely that any Oracle instance will allocate only a certain amount of memory for all its processes and this value is configurable. I am not sure, anyhow, if this value is the SGA, the PGA or whatever - check this with the DBAs, they should know. It might be easier to do this than to list and add and analyse some OS-output.

I hope this helps.

bakunin

Running ipcs -m gave me

Code:

oraent@DBSRV1 $
oraent@DBSRV1 $ ipcs -m
IPC status from <running system> as of Friday, August 11, 2017 03:07:22 PM IST
T         ID      KEY        MODE        OWNER    GROUP
Shared Memory:
m         15   0x3f81fc70 --rw-r-----   oraent oinstall
m         14   0x0        --rw-r-----   oraent oinstall
m         13   0x0        --rw-r-----   oraent oinstall
m          7   0xeb0a8b74 --rw-r-----   oraent oinstall
m          6   0x0        --rw-r-----   oraent oinstall
m          5   0x0        --rw-r-----   oraent oinstall
m          3   0xf6b11ac0 --rw-r-----   oraent oinstall
m          2   0x0        --rw-r-----   oraent oinstall
m          1   0x0        --rw-r-----   oraent oinstall
You have new mail in /var/mail/oraent
oraent@DBSRV1 $
oraent@DBSRV1 $

The oracle database allocates memory equivalent to the SGA parameter.
Thsi is needed to only start the database and to keep it running.
But as new user connect to the database and perform theis tasks. Each user has its own process on the server. This process consumes memory.

So, such connections per database differs.
In my case i have three database opn the server.
Out of the three, one database consumes apporx 80% of the CPU and memory. This is just an estimate depending on the number of connections see at the database level.
Thats why i need to find the memory usage per database.

What Bakunin is politely telling you -

Depending on the oracle version and what the db is configured to do, there are oracle configuration files and tables. You as root cannot easily get into each instance and see what is going on. You pretty much have to know how the db was set up beforehand.

Go ask your DBA's what they have done for each database. Unless you want to spend two days wading knee deep through a wall of oracle documentation. And your interpretation of the "80%" thing probably is wrong, IMO (see last line)

Why? Because it all depends on whether sqlplus is running on the Solaris box itself for each user process, which it may look like to you, but whether the user's desktop or an application server is involved.

FWIW your pmon output ( I did not count) shows six(?) databases - anyway way more than three instances. You've got problems beyond what you are aware of, IMO.

The pmon was from another server, hence the 6 databases.

The connections are not from the server. They arw coming from various sources. Some from Windows machine some from inhouse application server and some web based application. Few from the developer also.
Only the jobs which are scheduled as crontab make connection to the database. And these jobs are scheduled at night..

Look, you can dispute what you want. You seem to want to diagnose this on a per process basis. Okay, then the use ps command and netstat to show who comes from what source.

The base point: if you do not have the full picture you will very likely incorrectly diagnose problems. For example are there lots of TIMED_WAIT in netstat? - I had that cause the problem you see. The windows boxes mostly and other servers had issues, but the oracle system showed the symptoms - lots of cpu, poor performance.

Do what you want. You have your mind set on per process analysis, so go for it.