V$PROCESS
This view contains information about all Oracle processes running on the system. It is used to relate the Oracle or operating system process ID of the server process to the database session. This is needed in several situations:
- If the bottleneck on the database server is related to an operating system resource (for example, CPU, memory), and if the top resource users are localized within a small set of server processes, then perform the following steps:
- The SQL*Trace file names are based on the operating system process ID of the server process. To locate the trace file for a session, you must relate the session to the server process.
- Some events, like
rdbms
ipc
reply
, identify the Oracle process ID of the process a session is waiting on. To find out what those processes are doing, you must find their sessions. - The background processes you see on the server (DBWR, LGWR, PMON, and so on) are all server processes. To see what they are doing in the database, you must find their session.
Useful Columns for V$PROCESS
Join Columns for V$PROCESS
Table 24-11 lists the join columns forV$PROCESS
.
Table 24-11 Join Columns for V$PROCESS
Column | View | Joined Column(s) |
---|---|---|
|
|
|
Example 24-17 Finding the Session for Server Process 20143
SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '|| s.audsid||chr(10)|| ' DB User / OS User : '||s.username|| ' / '||s.osuser||chr(10)|| ' Machine - Terminal : '|| s.machine||' - '|| s.terminal||chr(10)|| ' OS Process Ids : '|| s.process||' (Client) '|| p.spid||' - '||p.pid||' (Server)'|| chr(10)|| ' Client Program Name : '||s.program "Session Info" FROM V$PROCESS P,V$SESSION s WHERE p.addr = s.paddr AND p.spid = '20143'; Session Info ------------------------------------------------------------------- Sid, Serial#, Aud sid : 2204 , 5552 , 14478782 DB User / OS User : APPS / sifapmgr Machine - Terminal : finprod3 - OS Process Ids : 9095 (Client) 20143 - 1404 (Server) Client Program Name : RGRARG@finprod3 (TNS V1-V3)
Example 24-18 Finding the Session for PMON
SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '|| s.audsid||chr(10)|| ' DB User / OS User : '||s.username|| ' / '||s.osuser||chr(10)|| ' Machine - Terminal : '|| s.machine||' - '|| s.terminal||chr(10)|| ' OS Process Ids : '|| s.process||' (Client) '|| p.spid||' - '||p.pid||' (Server)'|| chr(10)|| ' Client Program Name : '||s.program "Session Info" FROM V$PROCESS p, V$SESSION s WHERE p.addr = s.paddr AND s.program LIKE '%PMON%' Session Info --------------------------------------------------------------- Sid, Serial#, Aud sid : 1 , 1 , 0 DB User / OS User : / oracle Machine - Terminal : finprod7 - UNKNOWN OS Process Ids : 20178 (Client) 20178 - 2 (Server) Client Program Name : oracle@finprod7 (PMON)
You can see that the client and server processes are the same for the background process, which is why we could specify the client program name.
related useful Query SQL:
Lock& Wait:
SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid
FROM v$locked_object l, dba_objects o, v$session s, v$process p
WHERE l.object_id = o.object_id
AND l.session_id = s.sid and s.paddr = p.addr
ORDER BY o.object_id, xidusn DESC;
GETSQL from eygle:
REM getsql.sql
REM author eygle
REM 在windows上,已知进程ID,得到当前正在执行的语句
REM 在windows上,进程ID为16进制,需要转换,在UNIX直接为10进制
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC