学习动态视图(三) v$process

动态视图学习计划列表

This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.
V$PROCESS中的常用列
ADDR：进程对象地址
PID：oracle进程ID
SPID：操作系统进程ID

常用的查询
select s.sid,s.SERIAL#, s.username,p.spid
from v$session s, v$process p
where s.osuser = 'junsansi'
and s.PADDR = p.ADDR;

不包含于v$session中的进程
select * from v$process WHERE addr NOT IN (select paddr from v$session);
ADDR        PID        SPID        USERNAME        SERIAL#        TERMINAL        PROGRAM      
00000000F333CA38        1                        0                PSEUDO                                      
00000000F33450A0        18        21022        oracle        1        UNKNOWN        oracle@dbtwo (D000)
00000000F3345888        19        21024        oracle        1        UNKNOWN        oracle@dbtwo (S000)

D001和S001是Shared Server模式下的"d000" dispatcher 和"s000" shared_server, 在没有client用共享服务器连接时 "d000" dispatcher 和"s000" shared_server 就空闲下来，不对应任何session了.

PSEUDO进程是在Session被kill后留下的。
原理：When an inactive session has been terminated, STATUS in the view V$SESSION is
"KILLED." ,but its row in the v$session view is not removed until you try to
use that session again and "ORA-00028 your  session has been killed" is reported,
then it is removed.  Until that point, it is marked as killed in the status column
 and pseudo in the server column.

NOTE:  Killing the pseudo process (seen at the O/S) can lead to an orphan process
process in Oracle as well.   We recommned killing the Oracle process (found in
V$PROCESS) first and then kill at the O/S if necessary.

杀掉session
You can shutdown and restart the database or use the ORAKILL utility to kill
threads.

PMON进程
what happens to a session while it is in the KILLED PSEUDO state?
What's happening is that PMON periodically checks to see if any sessions have been killed. If it finds one, it attempts to rollback the transaction for that session
(that was in progress when it was killed).  The reason this can take a long time is
because PMON may have more than one transaction to rollback at a time
(if other sessions have been killed, or if processes have died etc).
Thus, it may take a while to finally cleanup the killed session and have it
disappear from the session monitor.  The system i/o monitor correctly shows
the reads and writes being performed by PMON in order to rollback the
session's transaction.
PMON will not delete the session object itself until the client connected to
that session notices that it has been killed.  Therefore, the sequence of
events is:
1) alter system kill session is issued - the STATUS of the session object in
   V$SESSION becomes KILLED, its server becomes PSEUDO.
2) PMON cleans up the *resources* allocated to the session
   (i.e., rolls back its transaction, releases its locks, etc).
3) the entry in V$SESSION remains there until the client of that session (the
   client is the process associated with the OSUSER,MACHINE,PROCESS columns in
   the V$SESSION view) tries to do another request.
4) the client attempts another SQL statement and gets back ORA-28.
5) PMON can now remove the entry from V$SESSION.
   This behavior. is necessary because the client still has pointers to the
   session object even though the session has been killed.  Therefore, the
   object cannot be deleted until the client is no longer pointing at it.

以下部分来自[三思笔记]:

在linux环境可以通过ps查看进程信息包括pid,windows中任务管理器的PID与v$process中pid不能一一对应，这块在oracleDocument中也没有找到介绍，后来google了一下，有资料介绍说是由于windows是多线程服务器,每个进程包含一系列线程。这点于unix等不同，Unix每个Oralce进程独立存在，在Nt上所有线程由Oralce进程衍生。
要在windows中显示oracle相关进程pid，我们可以通过一个简单的sql语句来实现。
SELECT s.SID, p.pid, p.spid signaled, s.osuser, s.program
FROM v$process p, v$session s
WHERE p.addr = s.paddr;

SID PID SIGNALED OSUSER PROGRAM
1 2 2452 SYSTEM ORACLE.EXE
2 3 2460 SYSTEM ORACLE.EXE
3 4 2472 SYSTEM ORACLE.EXE
4 5 2492 SYSTEM ORACLE.EXE
5 6 2496 SYSTEM ORACLE.EXE
6 7 2508 SYSTEM ORACLE.EXE
7 8 2520 SYSTEM ORACLE.EXE
8 9 2524 SYSTEM ORACLE.EXE
10 12 1316 JSSjunsansi PlSqlDev.exe
9 13 3420 JSSjunsansi PlSqlDev.exe
13 14 660 JSSjunsansi PlSqlDev.exe

还可以通过和 v$bgprocess 连接查询到后台进程的名字：
SELECT s.SID SID, p.spid threadid, p.program processname, bg.NAME NAME
FROM v$process p, v$session s, v$bgprocess bg
WHERE p.addr = s.paddr
AND p.addr = bg.paddr
AND bg.paddr <> '00';

SID THREADID PROCESSNAME NAME
1 2452 ORACLE.EXE PMON
2 2460 ORACLE.EXE DBW0
3 2472 ORACLE.EXE LGWR
4 2492 ORACLE.EXE CKPT
5 2496 ORACLE.EXE SMON
6 2508 ORACLE.EXE RECO
7 2520 ORACLE.EXE CJQ0
8 2524 ORACLE.EXE QMN0

Eygle大师写了一段sql脚本getsql.sql，用来获取指定pid正在执行的sql语句，在此也附注上来。
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 = TO_NUMBER ('&pid', 'xxxx')))
ORDER BY piece ASC
/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/55472/viewspace-374931/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-374931/