模拟Oracle进程CPU占用率100%

最新推荐文章于 2024-08-21 16:23:04 发布

转载最新推荐文章于 2024-08-21 16:23:04 发布 · 339 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Tomason/p/3160939.html

文章标签：

#数据库 #运维

本文介绍了一种通过top命令定位高CPU占用的Oracle进程，并进一步找出引起资源占用高的具体SQL语句的方法。通过一系列SQL查询，可以精确找到并终止该SQL进程。

查找CPU资源占用较高的SQL

# SCOTT用户输入下面代码

SQL> show user
USER is "SCOTT"
SQL> 
SQL> declare
  2  num int:=0;
  3  begin
  4  loop
  5  num:=num+1;
  6  end loop;
  7  end;
  8  /

[root@ora10g ~]# top -u oracle
top - 15:03:13 up 15:14,  3 users,  load average: 0.90, 0.37, 0.13
Tasks:  90 total,   3 running,  86 sleeping,   0 stopped,   1 zombie
Cpu(s): 99.3%us,  0.7%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    774580k total,   561648k used,   212932k free,    73960k buffers
Swap:  1052248k total,        0k used,  1052248k free,   403420k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
12845 oracle    25   0  303m  28m  26m R 98.2  3.8   2:05.72 oracle      <---------- CPU负载为98.2% 
 8630 oracle    15   0 42388 6036 4352 S  0.0  0.8   0:00.36 tnslsnr
 8645 oracle    15   0  303m  13m  11m S  0.0  1.8   0:00.81 oracle 
 8650 oracle    15   0  303m  10m 9952 S  0.0  1.4   0:00.35 oracle 
 8652 oracle    15   0  303m  18m  17m S  0.0  2.5   0:00.39 oracle 
 8654 oracle    18   0  306m  26m  23m S  0.0  3.5   0:01.20 oracle 
 8656 oracle    15   0  318m  30m  28m S  0.0  4.0   0:01.46 oracle 
 8658 oracle    16   0  303m  17m  16m S  0.0  2.3   0:08.87 oracle 
 8660 oracle    18   0  304m  68m  66m S  0.0  9.0   0:06.93 oracle 
 8662 oracle    18   0  303m  17m  16m S  0.0  2.4   0:00.23 oracle 
 8664 oracle    15   0  304m  28m  26m S  0.0  3.8   0:03.50 oracle 
 8666 oracle    15   0  305m  53m  49m S  0.0  7.1   0:03.95 oracle 
 8668 oracle    18   0  303m  16m  15m S  0.0  2.2   0:02.01 oracle 
 8670 oracle    18   0  303m  11m 9604 S  0.0  1.5   0:00.07 oracle 
 8672 oracle    18   0  303m  10m 9532 S  0.0  1.4   0:00.14 oracle 
 8676 oracle    18   0  318m  26m  25m S  0.0  3.5   0:00.29 oracle 
 8678 oracle    18   0  318m  26m  25m S  0.0  3.5   0:00.38 oracle 
 8682 oracle    15   0  303m  12m  10m S  0.0  1.6   0:00.18 oracle 
 8686 oracle    17   0  303m  13m  12m S  0.0  1.8   0:00.09 oracle 
 8963 oracle    18   0  303m  11m  10m S  0.0  1.5   0:00.08 oracle 
 9874 oracle    15   0  4768 1508 1216 S  0.0  0.2   0:00.03 bash   
10914 oracle    15   0  4768 1508 1216 S  0.0  0.2   0:00.02 bash   
12842 oracle    15   0  4412 1092  896 S  0.0  0.1   0:00.01 rlwrap 
12843 oracle    15   0 21004 7416 5136 S  0.0  1.0   0:00.06 sqlplus
12857 oracle    15   0  4412 1096  896 S  0.0  0.1   0:00.01 rlwrap 
12858 oracle    15   0 20868 6760 4628 S  0.0  0.9   0:00.02 sqlplus
12859 oracle    19   0  303m  13m  12m S  0.0  1.8   0:00.04 oracle

SQL> select ses.sid,ses.serial#,ses.username
  2  from v$session ses,v$process pro
  3  where pro.spid=&spid and ses.paddr=pro.addr;
Enter value for spid: 12845   <---------------------------------------输入进程ID
old   3: where pro.spid=&spid and ses.paddr=pro.addr
new   3: where pro.spid=12845 and ses.paddr=pro.addr

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       159         15 SCOTT

SQL> 
SQL> SELECT /*+ ORDERED*/
  2  REPLACE(SQL_TEXT, CHR(13), CHR(10)||CHR(13))
  3  FROM v$sqltext a
  4  WHERE (a.HASH_VALUE,a.ADDRESS) IN (
  5           SELECT decode(sql_hash_value,
  6                         0,prev_hash_value,
  7                         sql_hash_value
  8                         ),
  9                  decode(sql_hash_value,0,prev_sql_addr,sql_address)
 10           FROM v$session b
 11           where b.sid=&sid and b.serial#=&serial
 12  )
 13  /
Enter value for sid: 159
Enter value for serial: 15
old  11:          where b.sid=&sid and b.serial#=&serial
new  11:          where b.sid=159 and b.serial#=15

REPLACE(SQL_TEXT,CHR(13),CHR(10)||CHR(13))
--------------------------------------------------------------------------------
declare num int:=0; begin loop num:=num+1; end loop; end;  <---------得到执行的SQL

SQL> alter system kill session '&sid,&serial';
Enter value for sid: 159
Enter value for serial: 15
old   1: alter system kill session '&sid,&serial'
new   1: alter system kill session '159,15'

System altered.

[root@ora10g Server]# top -u oracle
top - 15:18:17 up 15:29,  3 users,  load average: 0.69, 0.89, 0.62
Tasks:  89 total,   1 running,  87 sleeping,   0 stopped,   1 zombie
Cpu(s):  2.9%us,  0.3%sy,  0.0%ni, 96.2%id,  0.5%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:    774580k total,   561648k used,   212932k free,    75000k buffers
Swap:  1052248k total,        0k used,  1052248k free,   403912k cached
                                                                      
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND  
 8630 oracle    15   0 42388 6036 4352 S  0.0  0.8   0:00.36 tnslsnr  
 8645 oracle    15   0  303m  13m  12m S  0.0  1.8   0:00.82 oracle   
 8650 oracle    15   0  303m  10m 9952 S  0.0  1.4   0:00.35 oracle   
 8652 oracle    15   0  303m  18m  17m S  0.0  2.5   0:00.39 oracle   
 8654 oracle    18   0  306m  26m  23m S  0.0  3.5   0:01.21 oracle   
 8656 oracle    15   0  318m  30m  28m S  0.0  4.0   0:01.49 oracle   
 8658 oracle    16   0  303m  17m  16m S  0.0  2.3   0:08.98 oracle   
 8660 oracle    18   0  304m  68m  66m S  0.0  9.0   0:07.01 oracle   
 8662 oracle    18   0  303m  17m  16m S  0.0  2.4   0:00.23 oracle   
 8664 oracle    15   0  304m  28m  26m S  0.0  3.8   0:03.53 oracle   
 8666 oracle    15   0  305m  53m  49m S  0.0  7.1   0:04.02 oracle   
 8668 oracle    18   0  303m  17m  15m S  0.0  2.3   0:02.02 oracle   
 8670 oracle    18   0  303m  11m 9604 S  0.0  1.5   0:00.07 oracle   
 8672 oracle    18   0  303m  10m 9532 S  0.0  1.4   0:00.14 oracle   
 8676 oracle    18   0  318m  26m  25m S  0.0  3.5   0:00.29 oracle   
 8678 oracle    18   0  318m  26m  25m S  0.0  3.5   0:00.40 oracle   
 8682 oracle    15   0  303m  12m  10m S  0.0  1.6   0:00.19 oracle   
 8686 oracle    17   0  303m  13m  12m S  0.0  1.8   0:00.09 oracle   
 8963 oracle    18   0  303m  11m  10m S  0.0  1.5   0:00.11 oracle   
 9874 oracle    15   0  4768 1508 1216 S  0.0  0.2   0:00.03 bash     
10914 oracle    15   0  4768 1508 1216 S  0.0  0.2   0:00.02 bash     
12842 oracle    25   0  4412 1092  896 S  0.0  0.1   0:00.01 rlwrap   
12843 oracle    15   0 21004 7428 5148 S  0.0  1.0   0:00.06 sqlplus  
12857 oracle    15   0  4412 1100  896 S  0.0  0.1   0:00.02 rlwrap   
12858 oracle    16   0 20868 7088 4956 S  0.0  0.9   0:00.03 sqlplus  
12859 oracle    18   0  304m  28m  25m S  0.0  3.7   0:00.12 oracle