---模拟例子：
1:会话1：执行select * from all_objects;
2:会话2：linux下执行top，查看pidtop:
Cpu(s): 5.3%us, 2.6%sy, 0.0%ni, 91.4%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 2035908k total, 1932092k used, 103816k free, 70824k buffers
Swap: 4192956k total, 0k used, 4192956k free, 1343568k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12991 oracle 20 0 72036 13m 9908 S 4.3 0.7 0:02.68 sqlplus
12921 root 20 0 90144 3400 2624 S 3.6 0.2 0:02.81 sshd
4584 oracle -2 0 1035m 16m 14m S 1.0 0.8 0:50.21 oracle
13507 oracle 20 0 1051m 144m 135m S 1.0 7.3 0:05.48 oracle
6 root 20 0 0 0 0 R 0.7 0.0 0:00.85 events/0
18 root 20 0 0 0 0 S 0.3 0.0 0:02.54 ata/0
4397 root 20 0 70368 2652 2044 S 0.3 0.1 0:02.22 escd
4580 oracle 20 0 1035m 16m 14m S 0.3 0.8 0:03.04 oracle
14882 root 20 0 12756 1160 820 R 0.3 0.1 0:00.07 top
1 root 20 0 10364 696 580 S 0.0 0.0 0:01.37 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:00.14 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
7 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cpuset
8 root 20 0 0 0 0 S 0.0 0.0 0:00.06 khelper
9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 netns
10 root 20 0 0 0 0 S 0.0 0.0 0:00.00 async/mgr
11 root 20 0 0 0 0 S 0.0 0.0 0:00.02 sync_supers
12 root 20 0 0 0 0 S 0.0 0.0 0:00.02 bdi-default
13 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/0
14 root 20 0 0 0 0 S 0.0 0.0 0:00.08 kblockd/0
---进一步模拟一个插入大量数据的测试操作：
--在Windows客户端执行插入数据程序块：
SQL> declare
2 dd date;
3 begin
4 for i in 1 ..30000000 loop
5 select sysdate into dd from dual;
6 end loop;
7 end;
8 /
--在服务端操作系统使用top命令查看：
[root@enmo ~]# top
top - 01:03:51 up 15:22, 6 users, load average: 1.09, 0.31, 0.10
Tasks: 198 total, 2 running, 196 sleeping, 0 stopped, 0 zombie
Cpu(s): 73.8%us, 26.2%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2035908k total, 1937920k used, 97988k free, 115320k buffers
Swap: 4192956k total, 0k used, 4192956k free, 1313036k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14381 oracle 20 0 1042m 39m 35m R 98.5 2.0 5:32.30 oracle
2753 root 20 0 126m 6988 4272 S 0.7 0.3 0:36.01 vmtoolsd
4596 oracle -2 0 1039m 16m 14m S 0.7 0.8 5:48.39 oracle
18 root 20 0 0 0 0 S 0.3 0.0 0:06.57 ata/0
2937 root 20 0 128m 4812 3972 S 0.3 0.2 0:12.24 ManagementAgent
14448 root 20 0 12756 1176 820 R 0.3 0.1 0:23.21 top
1 root 20 0 10364 692 580 S 0.0 0.0 0:01.78 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:01.18 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
6 root 20 0 0 0 0 S 0.0 0.0 0:01.70 events/0
7 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cpuset
8 root 20 0 0 0 0 S 0.0 0.0 0:00.04 khelper
9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 netns
10 root 20 0 0 0 0 S 0.0 0.0 0:00.00 async/mgr
11 root 20 0 0 0 0 S 0.0 0.0 0:00.06 sync_supers
12 root 20 0 0 0 0 S 0.0 0.0 0:00.07 bdi-default
13 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/0
14 root 20 0 0 0 0 S 0.0 0.0 0:00.24 kblockd/0
15 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpid
16 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpi_notify
17 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpi_hotplug
19 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata_aux
20 root 20 0 0 0 0 S 0.0 0.0 0:00.15 ksuspend_usbd
--通过pid使用客户端图形工具查看会话号与序列号：
SQL> SELECT t.sid,t.serial#,d.sql_text, a.spid, t.program, t.process
FROM v$sqlarea d, v$session t, v$process a
WHERE d.address = t.sql_address
AND d.hash_value = t.sql_hash_value
AND t.paddr = a.addr
AND a.spid IN (14381);
#使用命令行查询：
sys@PROD>> SELECT t.sid,t.serial#,d.sql_text, a.spid, t.program, t.process
2 FROM v$sqlarea d, v$session t, v$process a
3 WHERE d.address = t.sql_address
4 AND d.hash_value = t.sql_hash_value
5 AND t.paddr = a.addr
6 AND a.spid IN (&PID);
Enter value for pid: 14318
old 6: AND a.spid IN (&PID)
new 6: AND a.spid IN (14318)
SID SERIAL#
---------- ----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
SPID PROGRAM PROCESS
------------------------ ------------------------------------------------ ------------------------
44 27
SELECT SYSDATE FROM DUAL
14318 sqlplus.exe 5844:4872
---看到如此高CPU消耗率，查清楚原因后通过SID SERIAL#杀掉该进程：
--服务端：
sys@PROD>alter system kill session '44,27' immediate;
alter system kill session '44,27' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
sys@PROD>
--Windows客户端：
SQL> declare
2 dd date;
3 begin
4 for i in 1 ..30000000 loop
5 select sysdate into dd from dual;
6 end loop;
7 end;
8 /
declare
*
第 1 行出现错误:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
#这样就成功将CPU高耗进程停止掉。