查找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
# 再次查看使用CPU高的资源已经被杀死