learning v$ view-> v$process

 

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:
  1. Find the resource intensive processes.
  2. Find their sessions. You must relate the processes to sessions.
  3. Find out why the session is using so many resources.
• 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 rdbmsipc 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

• PID: Oracle process ID of the process
• SPID: Operating system process ID of the 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)
ADDR	V$SESSION	PADDR

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