To which user does a Oracle process belong ?

[Leif Halvarsson_2]

Hi,
From HP-UX point of view, all Oracle processes belongs to the oracle user, but to which Oracle user does a specific process belong ?

I know that it can be found using the Oracle Enterprise Administrator tool but, is ther any other way (e.g. a SQL query).

[A. Clay Stephenson]

I suspect that by "process" you acxtually mean "session". Try 'select * from v$session' although you will probably want to apply some conditions to the select.

[TwoProc]

Do you mean the shadow process on the db server? I'm presuming this is what you're talking about, otherwise, if it was the direct process, you'd just look at it from the Unix level. The Shadow process containing the user's PGA or at least, the link the user's PGA (using aggregated PGA) is a bit harder.

So, let's say you're talking about the connections from Unix which say "LOCAL=NO" in them (from the "ps" command).

Let's say you're interested in process 28737

> ps -ef | grep 28737

dbau1 28737 1 0 Aug 26 ? 0:00 oracleDEVELA (LOCAL=NO)

Total processes found = 1

look inside of oracle for anything with an spid of 28737 in the v$process table.

SELECT s.sid, s.serial#, p.spid, p.pid, s.process, s.type, s.username, s.osuser, s.server,s.machine, s.module,s.terminal, s.program, p.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr(+)
AND p.spid = 28737;


Look in the result - your OS user in the column s.osuser, and your database user login is contained in the s.username column.

[Hein van den Heuvel]

You need to ask Oracle about its connections. This is done by joining information from two internal tables : v$session and v$process. Combined they have the data you need.

Below you'll find sample code which I made a while back and used a lot to analyze some sap performance issues. I'm sure thre are a ton more examples along those lines.

Ask Oracle to describe the table to see if there is more info you might need.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting



column id format 999;
column Local format 99999999;
column Remote format 99999999;

column Node format a9;
column Local_Program format a20;
column Remote_Program format a24;
set pages 9999;
set lines 132;

select pid "Id", spid "Local", process "Remote",
s.machine "Node", SUBSTR(p.program,1,20) "Local Program",
SUBSTR(s.program,1,24) "Remote Program"
from v$process p, v$session s
where addr = paddr
order by machine, spid
/

[TwoProc]

Well, on second thought, whether or not "LOCAL=NO" is part of this discussion is wrong. Leave that part out of the discussion, doesn't matter so much one way or the other, and is pretty much a different discussion topic (bequeath protocol, remote user vs local, etc.) which I don't think is necessary to discuss as part of this question.

[Steven E. Protter]

Shalom,

The only way to reliably get that information is to write a sql query to the database. You need to know the instance name, but can usually find that out by looking at the process list.

v$sessions needs to be queried.

I believe the table structure of that table includes an OS process id.

SEP

[whiteknight]

Hi Leif,

Here is the steps.
On a Unix system, the PID of a user's cshell login will match up with a SPID retrieved from V$process. .

Issuing the following commands will give the dba a list of of Unix PIDS:

ps -efa | pg

or for a specific login

ps -efa | grep cberlin

these queries will return a list of PIDs

wgwillia-sun% ps -efa | grep cberlin

cberlin 18379 18377 0 09:35:18 pts/7 0:00 grep cberlin
cberlin 17491 17489 0 08:29:02 pts/3 0:00 -csh
cberlin 18377 18376 0 09:35:05 pts/7 0:00 /usr/bin/csh
cberlin 17780 17628 0 08:36:04 pts/7 0:00 svrmgrl
cberlin 17628 17626 0 08:30:23 pts/7 0:00 -csh
cberlin 18376 17780 0 09:35:05 pts/7 0:00 sh -c /usr/bin/csh
cberlin 17621 17491 0 08:29:33 pts/3 0:00 sqlplus

If this information does not match up exactly with a SPID found in
V$process, don't be concerned.

Issue the following command from Server Manager:

SVRMGR> select paddr,osuser from V$session
2> intersect
3> select addr, username from V$process;

This will return output where paddr.V$session = addr.V$process
and osuser.V$session = username.V$process

PADDR OSUSER
-------- ---------------
800205C0 oracle
80020828 oracle
80020A90 oracle
80020CF8 oracle
80020F60 oracle
800211C8 cberlin
80021430 cberlin


NEXT ISSUE - a query similar to:

select * from V$process where addr = '800211C8';


wgwillia-sun% SVRMGR> select * from V$process where addr = '800211C8';
ADDR PID SPID USERNAME SERIAL# TERMINAL PROGRAM
B LATCHWAI LATCHSPI
-------- ---------- --------- --------------- ---------- ---------- ------------
------------------------------------ - -------- --------
800211C8 7 17625 cberlin 5 pts/3 oracle@wgwil
lia-sun (TNS V1-V2)
1 row selected.

To verify issue, the following command at the unix prompt

wgwillia-sun% ps -efa | grep cberlin

cberlin 18736 17780 0 09:51:03 pts/7 0:00 sh -c /usr/bin/csh
cberlin 18737 18736 0 09:51:03 pts/7 0:00 /usr/bin/csh
cberlin 17491 17489 0 08:29:02 pts/3 0:00 -csh
cberlin 17780 17628 0 08:36:04 pts/7 0:00 svrmgrl
cberlin 17628 17626 0 08:30:23 pts/7 0:00 -csh
cberlin 18745 18737 0 09:54:13 pts/7 0:00 grep cberlin
cberlin 17621 17491 0 08:29:33 pts/3 0:00 sqlplus

In this example the spid 17625 from V$process is very close to
the PID and PPID associated with the -csh session for cberlin

cberlin 17628 17626 0 08:30:23 pts/7 0:00 -csh

Once the correct session has been linked from Oracle to the
operating system, the DBA can issue the following query
to obtain the sid and Oracle username of the problematic client:

SVRMGR> select sid, username from V$session
2> where paddr = '800211C8';


SID USERNAME
---------- ------------------------------
7 SYS


With the SID identified, the DBA can then retrieve the serial #,
kill the session, or retrieve other information, such as
what locks the user session is holding.


WK
please assign points

[TwoProc]

Here is the steps.
On a Unix system, the PID of a user's cshell login will match up with a SPID retrieved from V$process. .

I disagree - the spid is not your login shell, its the process spawned by oracle to handle your Oracle conversation with the database on your behalf, otherwise known as your shadow process.

That's why, in your example, you couldn't actually match the process id to the spid, but you were "close".

[Leif Halvarsson_2]

Hi,

Thanks to all of you.

I just found out that the problem was more complex then what I belived.

My problem was that we have noticed some strange load on one of our servers but, has no idea about which user that caused the load.

But, as this server is running Documentum, no useful information can be found in Oracle ( all Oracle processes i owned by "dmadmin").

The user must be fetched from Documentum (if possible at all) and this is a different problem.

[Volker Borowski]

Hi Leif,

this look like the same problem I am facing with SAP from time to time. Since the real solution is not to knock the User with a Club on his head, you should hunt the statement that causes the load and try to improve this by supplying a helpfull index.

To do this, identify the UNIX process ID from your application client process (in SAP this is a disp+work usually) that causes the high load ("top" is a good way to get this).

Then call SQLPLUS to do this:

set LONG 20000 LONGC 20000 LINESIZE 20000 HEADING OFF

select SQL_FULLTEXT from v$sqlarea where SQL_ID=(select sql_id from v$session where status='ACTIVE' and process=&PROC);


You'll receive a prompt to feed &PROC and enter the UNIX PID you collected before.

Voila, there is the bad SQL-Statement.
Repeat this to check if it stays really long on this statement and then you can try to analyze it for speedup possibilites.

Volker