Oracle九大性能视图 v$process

1、表结构ITPUB个人空间S+_Tp&|$m(LYg
SQL> desc v$processITPUB个人空间2~R]j,G4Iwn
 名称                                      是否为空? 类型ITPUB个人空间7tG9LF0}3D"PD(F4wb
 ----------------------------------------- -------- ----------------------------
IaR*A0~5U!i~'?*|0 ADDR                                               RAW(8)ITPUB个人空间-n{Oxjm5Q:I
 PID                                                NUMBERITPUB个人空间(x%Qx.r$g^pc.^
 SPID                                               VARCHAR2(12)
^0C,g2~Zs_&B0 USERNAME                                           VARCHAR2(15)ITPUB个人空间8s 1n�r;T4z[7|P
 SERIAL#                                            NUMBER
p-a5d _N,fx3I y0 TERMINAL                                           VARCHAR2(16)
.k
`7@3n"K j,L0 PROGRAM                                            VARCHAR2(64)
1Bpk5h&D^8Bd0 TRACEID                                            VARCHAR2(255)ITPUB个人空间�j+REL#Nk8O.L:W
 BACKGROUND                                         VARCHAR2(1)
6m*O1 L]5K0N Pe0 LATCHWAIT                                          VARCHAR2(16)
3o/QJ.['t p s0 LATCHSPIN                                          VARCHAR2(16)ITPUB个人空间X9|j@C8u)H:y J!f
 PGA_USED_MEM                                       NUMBERITPUB个人空间2o"E RAs] M$_"E
 PGA_ALLOC_MEM                                      NUMBER
(x6xD2kd'sjdW4v0 PGA_FREEABLE_MEM                                   NUMBER
!{e"p3t"x0 PGA_MAX_MEM                                        NUMBER

 2、根据session id可以查到操作系统进程的信息
$wk L4V!e7k'pU4}0 select * from v$process where addr in (select paddr from v$session where sid=&sid );ITPUB个人空间c],`;f#tsp![
 查得结果如下：
dN]%aY3f�R%O-l I0     ADDR PID SPID USERNAME SERIAL# TERMINAL PROGRAM TRACEID BACKGROUND LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
n'qU�m^P01 000007FF93A62948 15 1676 SYSTEM 14 ZHOULINLING ORACLE.EXE (J000)     153384 1876773 983040 3318565

spid字段是操作系统进程号，可以用来进行 kill -9 spid 这么一个操作。

ITPUB个人空间z%i[.z2T[
3、找到 unix命令 top 出来的最耗cpu资源的pid，根据pid最耗资源的sql：

SELECT /*+ ORDERED */ITPUB个人空间7W!Y,N/[O
 sql_textITPUB个人空间$EPT)]*[
  FROM v$sqltext aITPUB个人空间j+AH:a"_i2J(d
 WHERE (a.hash_value, a.address) INITPUB个人空间 t;f4e9Rt4[I4[
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
*X�m)y5` E%f0               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
9neS,xu6U"p_0          FROM v$session bITPUB个人空间V;QSY+Ub%J9{-^3V1L
         WHERE b.paddr =ITPUB个人空间D]+N|Q6S/D6q6dg}F
               (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
y-raU;I1EXd"blB0 ORDER BY piece ASC