oracle 查询占用消耗CPU的进程sql

1. top查看占用CPU比较高的进程ID，并记录下来

例如：

 PID  USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND                                                               
31189 nxuser    15   0 12436  11m  256 S  55.4  0.1  35626:21 oracle                                                             
32902 oracle    16   0 1304m 1.0g 1.0g S     1 12.5  13:07.28 oracle                                                                
 5784 nxuser    16   0  2368 1104  780 S     0  0.0   0:00.02 oracle                                                                   
28779 nxuser    16   0  2364 1140  780 R     0  0.0   0:25.28 top   

Pid 31189占用CPU显然是比较高的

Sqlpusl 连接登陆ORACLE

2. 执行下面的语句，查得相对应的系统进程对应的session id

SQL> select sid from v$session where paddr in (select addr from v$process where spid=&spid);            Enter value for spid: 31189 old   1: select sid from v$session where paddr in (select addr from v$process where spid=&spid) new   1: select sid from v$session where paddr in (select addr from v$process where spid=31189)          SID ----------         206

3. 根据所得的会话ID查得sql地址和hash值

SQL> select sql_address,sql_hash_value from v$session where sid=206;   SQL_ADDR SQL_HASH_VALUE -------- -------------- 6EC554F4     3141392848

 

4. 根据sql hash值查得sql语句

SQL> select sql_text from v$sqltext where hash_value=3141392848;   SQL_TEXT ---------------------------------------------------------------- INSERT INTO TEST SELECT * FROM SYS.DBA_OBJECTS

 

5．若没查得相应的sql地址和hash值,请查询job

 

 

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

转载于:http://blog.itpub.net/24015283/viewspace-709152/