如何定位占用oracle数据库cpu过高的sql



1）查看一下cpu进程占用情况：

看到oracle进程为6331,6517等这几个进程占用cpu过高，

2）查看相关进程信息：

[html] view plain copy print ?

1. [oracle@oracle-one ~]$ ps -ef | grep 6331  
2. oracle    6331     1 23 20:24 ?        00:02:05 ora_vktm_RHYS  
3. oracle    6555  6385  0 20:32 pts/2    00:00:00 grep 6331  
4. [oracle@oracle-one ~]$ ps -ef | grep 6517  
5. <span style="color: rgb(255, 0, 0);">oracle    6517  6513  9 20:28 ?        00:00:24 oracleRHYS (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))  
6. </span>oracle    6557  6385  0 20:33 pts/2    00:00:00 grep 6517  
7. [oracle@oracle-one ~]$   

<span style="font-size:14px;">[oracle@oracle-one ~]$ ps -ef | grep 6331
oracle    6331     1 23 20:24 ?        00:02:05 ora_vktm_RHYS
oracle    6555  6385  0 20:32 pts/2    00:00:00 grep 6331
[oracle@oracle-one ~]$ ps -ef | grep 6517
<span style="color: rgb(255, 0, 0);">oracle    6517  6513  9 20:28 ?        00:00:24 oracleRHYS (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
</span>oracle    6557  6385  0 20:33 pts/2    00:00:00 grep 6517
[oracle@oracle-one ~]$ </span>

3）查看该会话信息：

[html] view plain copy print ?

1. SQL> select sid,serial#,username,machine,osuser,process from v$session s  
2.   2     where s.paddr=(select addr from v$process p where p.spid='&pid');  
3. Enter value for pid: 6517  
4. old   2:    where s.paddr=(select addr from v$process p where p.spid='&pid')  
5. new   2:    where s.paddr=(select addr from v$process p where p.spid='6517')  
6.   
7.        SID    SERIAL# USERNAME                       MACHINE                                                          OSUSER                         PROCESS  
8. ---------- ---------- ------------------------------ ---------------------------------------------------------------- ------------------------------ ------------------------  
9.          1         21 RHYS                           oracle-one                                                       oracle                         6513  

<span style="font-size:14px;">SQL> select sid,serial#,username,machine,osuser,process from v$session s
  2     where s.paddr=(select addr from v$process p where p.spid='&pid');
Enter value for pid: 6517
old   2:    where s.paddr=(select addr from v$process p where p.spid='&pid')
new   2:    where s.paddr=(select addr from v$process p where p.spid='6517')

       SID    SERIAL# USERNAME                       MACHINE                                                          OSUSER                         PROCESS
---------- ---------- ------------------------------ ---------------------------------------------------------------- ------------------------------ ------------------------
         1         21 RHYS                           oracle-one                                                       oracle                         6513
</span>

可知：session 的信息为sid：1  serial#：21 数据库用户为：RHYS，客户端机器为：oracle-one，操作系统用户为：oracle 进程号：6513

4）查看该会话正在运行的sql：

[html] view plain copy print ?

1. SQL> select sql_text from v$sqltext  
2.   2   where (address,hash_value) in (  
3.   select sql_address,sql_hash_value from v$session s   
4.   3    4        where s.paddr=  
5.   5       (select addr from v$process p where p.spid='&pid'));  
6. Enter value for pid: 6517  
7. old   5:      (select addr from v$process p where p.spid='&pid'))  
8. new   5:      (select addr from v$process p where p.spid='6517'))  
9.   
10. <span style="color: rgb(255, 0, 0);">SQL_TEXT  
11. ----------------------------------------------------------------  
12. delete from amy_emp</span>  

<span style="font-size:14px;">SQL> select sql_text from v$sqltext
  2   where (address,hash_value) in (
  select sql_address,sql_hash_value from v$session s 
  3    4        where s.paddr=
  5       (select addr from v$process p where p.spid='&pid'));
Enter value for pid: 6517
old   5:      (select addr from v$process p where p.spid='&pid'))
new   5:      (select addr from v$process p where p.spid='6517'))

<span style="color: rgb(255, 0, 0);">SQL_TEXT
----------------------------------------------------------------
delete from amy_emp</span></span>

可知，当前用户正在进行删除表的操作，本次就是通过v$process 的spid找到进程号，然后找到v$session 的addr地址，然后找到v$sqltext的sql_address以及sql_hash_value，通过这两个字段就可以定位出唯一的sql_text，本次通过v$process，v$session，v$sql_text三个视图结合找到相应的sql；

5）如果该session为非法的，可以使用如下命令杀掉该会话：

alter  sytem kill  session ‘1,21’；

既然找到sql了，那么 我们就可以通知应用相关人员，确认是否在进行相关数据操作。

另外我们也可以使用dbms_system包对该session进行更加详细的跟踪。