以下是模擬消耗CPU較高的程式，並透過查詢程式（透過作業系統top命令查詢）進一步停止程式。

---模擬例子：
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高耗程式停止掉。

效能查詢並殺CPU高耗程式

相關文章