Linux下Oracle进程CPU使用率过高处理方式

一、构建一条比较耗费性能的SQL语句：

SQL> set linesize 200
SQL> select count(*) from all_objects a,all_objects b,all_objects c group by a.object_id;

二、登陆Linux，使用top工具查看进程信息

top - 14:39:46 up 9 min,  3 users,  load average: 0.41, 0.51, 0.32
Tasks: 381 total,   1 running, 380 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.5%us,  2.2%sy,  0.0%ni, 64.5%id, 28.8%wa,  0.1%hi,  0.9%si,  0.0%st
Mem:   2941544k total,  2799796k used,   141748k free,    47072k buffers
Swap:  3145724k total,        0k used,  3145724k free,  1024096k cached

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                 
  5241 oracle    20   0 1502m 132m  49m S  8.6  4.6   0:04.28 oracle                                                                   
  2023 root       0 -20     0    0    0 S  4.6  0.0   0:05.85 iscsi_q_33                                                               
  4816 oracle    -2   0 1403m  16m  14m S  3.0  0.6   0:08.61 oracle                                                                   
  3865 root      RT  -5  693m 154m  63m S  2.0  5.4   0:10.37 ologgerd                                                                 
  4129 grid      -2   0 1306m  15m  12m S  1.7  0.5   0:09.79 oracle                                                                   
  3774 root      20   0 1567m  38m  16m S  1.0  1.3   0:02.31 orarootagent.bi                                                          
  4191 root      20   0 2205m  66m  26m S  1.0  2.3   0:07.08 crsd.bin                                                                 
  3393 root      20   0 1850m  55m  22m S  0.7  1.9   0:04.81 ohasd.bin                                                                
  3740 grid      20   0 1836m  41m  16m S  0.7  1.5   0:02.40 oraagent.bin                                                             
  3776 grid      20   0  559m  39m  12m S  0.7  1.4   0:02.96 gipcd.bin                                                                
  3828 grid      RT   0 1612m 123m  54m S  0.7  4.3   0:05.42 ocssd.bin                                                                
  4317 root      20   0  962m  28m  14m S  0.7  1.0   0:03.49 orarootagent.bi                                                          
     4 root      20   0     0    0    0 S  0.3  0.0   0:00.04 ksoftirqd/0                                                              
  3928 root      20   0  751m  25m  10m S  0.3  0.9   0:01.99 octssd.bin                                                               
  3959 grid      20   0 1093m  28m  13m S  0.3  1.0   0:02.04 evmd.bin                                                                 
  4141 grid      20   0 1314m  26m  17m S  0.3  0.9   0:01.03 oracle                                                                   
  4143 grid      20   0 1322m  30m  16m S  0.3  1.1   0:00.99 oracle                                                                   
  4145 grid      -2   0 1321m  31m  17m S  0.3  1.1   0:01.87 oracle                                                                   
  4305 grid      20   0  565m  19m  10m S  0.3  0.7   0:00.31 scriptagent.bin                                                          
  4479 grid      20   0 2755m 162m  16m S  0.3  5.6   0:11.50 java                                                                     
  4744 oracle    20   0 1103m  33m  16m S  0.3  1.2   0:02.07 oraagent.bin                                                             
  4832 oracle    20   0 1418m  31m  21m S  0.3  1.1   0:00.83 oracle                                                                   
  4836 oracle    -2   0 1418m  49m  36m S  0.3  1.7   0:02.26 oracle                                                                   
[root@or11g1 ~]# 

从中可以看出，进程号为5241的oracle进程占用CPU资源是最多的

三、查看5241这个oracle进程对应的会话信息

SELECT 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 = '5241')) ORDER BY piece ASC;

或者：

SELECT 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;

查询结果如下：

SQL> SELECT 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 = '5241')) ORDER BY piece ASC;  2    3  

SQL_TEXT
----------------------------------------------------------------
select count(*) from all_objects a,all_objects b,all_objects c g
roup by a.object_id

SQL>

结果显示SQL语句就是我们之前构建的SQL。

在执行如下SQL语句，找出相应的会话信息：

select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') logon from 
	v$session where paddr in ( select addr from v$process where spid in('5241'));

或者：

select id,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') logon 
	from v$session where paddr in ( select addr from v$process where spid in('&pid'));

执行的结果如下：

到这里，就可以根据SID和SERIAL#来杀掉用户会话了。

alter system kill session '76,21' immediate;

SQL> alter system kill session '76,21' immediate;

System altered.

SQL>

但是，这要是通过PMON进程来定期清除会话的，因此会有一定的时间间隔，如果情况很紧急，需要立即释放资源，那么就要杀操作系统进程了。

四、找出操作系统进程

select PADDR from v$session where SID=76 and SERIAL#=21;

找出拥有会话的进程地址

然后通过PAAD号找出系统进程号

然后在操作系统上kill -9 5241即可。