定位CPU高消耗的问题

以下例子并未出现CPU过高消耗现象，只是介绍如何定位步骤

1、top命令先查找到CPU消耗过高进程的pid

oracle@linux:~> top
top - 10:18:07 up 33 days, 24 min,  2 users,  load average: 0.38, 0.55, 0.79
Tasks: 212 total,   1 running, 211 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.0%us,  0.3%sy,  0.0%ni, 97.3%id,  0.0%wa,  0.0%hi,  0.4%si,  0.0%st
Mem:  16379632k total, 16289356k used,    90276k free,   154388k buffers
Swap: 16779852k total,  2369936k used, 14409916k free, 12269600k cached
 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                      
20270 oracle    15   0  5652 1296  876 R    2  0.0   0:00.19 top                                                                                           
28282 oracle    16   0 8198m  28m  25m S    2  0.2  12:45.94 oracle                                                                                        
 7053 oracle    16   0 65864  12m 9452 S    0  0.1   0:33.38 tnslsnr                                                                                       
 7311 oracle    16   0 8199m  19m  16m S    0  0.1   0:06.32 oracle                                                                                        
 7315 oracle    15   0 8196m  14m  12m S    0  0.1   0:00.15 oracle                                                                                        

 

2、ps查找到对应的进程信息

oracle@linux:~> ps -ef| grep 28282
oracle   20632  9851  0 10:21 pts/2    00:00:00 grep 28282
oracle   28282     1  0 Jan19 ?        00:12:46 oracleora11g (LOCAL=NO)

 

3、查找相应有问题的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='&pid')
 )
 order by piece ASC