【Oracle九大性能视图】之6.v$process视图

1、表结构
SQL> desc v$process
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
PID NUMBER
SPID VARCHAR2(12)
USERNAME VARCHAR2(15)
SERIAL# NUMBER
TERMINAL VARCHAR2(16)
PROGRAM VARCHAR2(64)
TRACEID VARCHAR2(255)
BACKGROUND VARCHAR2(1)
LATCHWAIT VARCHAR2(16)
LATCHSPIN VARCHAR2(16)
PGA_USED_MEM NUMBER
PGA_ALLOC_MEM NUMBER
PGA_FREEABLE_MEM NUMBER
PGA_MAX_MEM NUMBER

2、根据session id可以查到操作系统进程的信息
select * from v$process where addr in (select paddr from v$session where sid=&sid );

查得结果如下：
ADDR PID SPID USERNAME SERIAL# TERMINAL PROGRAM TRACEID BACKGROUND LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
1 000007FF93A62948 15 1676 SYSTEM 14 ZHOULINLING ORACLE.EXE (J000) 153384 1876773 983040 3318565
spid字段是操作系统进程号，可以用来进行 kill -9 spid 这么一个操作。

3、找到 unix命令 top 出来的最耗cpu资源的pid，根据pid最耗资源的sql：
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