Oracle八大性能视图之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