一、构建一条比较耗费性能的SQL语句：
-
SQL>
set linesize
200
-
SQL>
select count(*) from all_objects a,all_objects b,all_objects c group by a.object_id;
二、登陆Linux，使用top工具查看进程信息
-
top - 14:39:46 up 9 min, 3 users,
load average:
0.41,
0.51,
0.32
-
Tasks:
381 total,
1 running,
380 sleeping,
0 stopped,
0 zombie
-
Cpu(s):
3.5%us,
2.2%sy,
0.0%ni,
64.5%
id,
28.8%wa,
0.1%hi,
0.9%si,
0.0%st
-
Mem:
2941544k total,
2799796k used,
141748k free,
47072k buffers
-
Swap:
3145724k total,
0k used,
3145724k free,
1024096k cached
-
-
PID
USER PR NI VIRT RES SHR S %CPU %MEM
TIME+ COMMAND
-
5241
oracle
20
0
1502m
132m
49m S
8.6
4.6
0:
04.28
oracle
-
2023 root
0
-20
0
0
0 S
4.6
0.0
0:
05.85 iscsi_q_33
-
4816
oracle
-2
0
1403m
16m
14m S
3.0
0.6
0:
08.61
oracle
-
3865 root RT
-5
693m
154m
63m S
2.0
5.4
0:
10.37 ologgerd
-
4129 grid
-2
0
1306m
15m
12m S
1.7
0.5
0:
09.79
oracle
-
3774 root
20
0
1567m
38m
16m S
1.0
1.3
0:
02.31 orarootagent.bi
-
4191 root
20
0
2205m
66m
26m S
1.0
2.3
0:
07.08 crsd.bin
-
3393 root
20
0
1850m
55m
22m S
0.7
1.9
0:
04.81 ohasd.bin
-
3740 grid
20
0
1836m
41m
16m S
0.7
1.5
0:
02.40 oraagent.bin
-
3776 grid
20
0
559m
39m
12m S
0.7
1.4
0:
02.96 gipcd.bin
-
3828 grid RT
0
1612m
123m
54m S
0.7
4.3
0:
05.42 ocssd.bin
-
4317 root
20
0
962m
28m
14m S
0.7
1.0
0:
03.49 orarootagent.bi
-
4 root
20
0
0
0
0 S
0.3
0.0
0:
00.04 ksoftirqd/
0
-
3928 root
20
0
751m
25m
10m S
0.3
0.9
0:
01.99 octssd.bin
-
3959 grid
20
0
1093m
28m
13m S
0.3
1.0
0:
02.04 evmd.bin
-
4141 grid
20
0
1314m
26m
17m S
0.3
0.9
0:
01.03
oracle
-
4143 grid
20
0
1322m
30m
16m S
0.3
1.1
0:
00.99
oracle
-
4145 grid
-2
0
1321m
31m
17m S
0.3
1.1
0:
01.87
oracle
-
4305 grid
20
0
565m
19m
10m S
0.3
0.7
0:
00.31 scriptagent.bin
-
4479 grid
20
0
2755m
162m
16m S
0.3
5.6
0:
11.50
java
-
4744
oracle
20
0
1103m
33m
16m S
0.3
1.2
0:
02.07 oraagent.bin
-
4832
oracle
20
0
1418m
31m
21m S
0.3
1.1
0:
00.83
oracle
-
4836
oracle
-2
0
1418m
49m
36m S
0.3
1.7
0:
02.26
oracle
-
[root@or11g1 ~]#
从中可以看出，进程号为5241的oracle进程占用CPU资源是最多的
三、查看5241这个oracle进程对应的会话信息
-
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 =
'5241'))
ORDER
BY piece
ASC;
或者：
-
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;
查询结果如下：
-
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 =
'5241')) ORDER BY piece ASC; 2 3
-
-
SQL_TEXT
-
----------------------------------------------------------------
-
select count(*)
from all_objects a,all_objects b,all_objects c g
-
roup
by a.object_id
-
-
SQL>
结果显示SQL语句就是我们之前构建的SQL。
在执行如下SQL语句，找出相应的会话信息：
-
select
sid,
serial# ,username,osuser,machine,program,process,to_char(logon_time,
'yyyy-mm-dd hh24:mi:ss')
logon
from
-
v$
session
where paddr
in (
select addr
from v$process
where spid
in(
'5241'));
或者：
-
select
id,
serial# ,username,osuser,machine,program,process,to_char(logon_time,
'yyyy-mm-dd hh24:mi:ss')
logon
-
from v$
session
where paddr
in (
select addr
from v$process
where spid
in(
'&pid'));
执行的结果如下：
到这里，就可以根据SID和SERIAL#来杀掉用户会话了。
alter system kill session '76,21' immediate;
-
SQL> alter
system
kill session
'76,21' immediate;
-
-
System altered.
-
-
SQL>
但是，这要是通过PMON进程来定期清除会话的，因此会有一定的时间间隔，如果情况很紧急，需要立即释放资源，那么就要杀操作系统进程了。
四、找出操作系统进程
select PADDR from v$session where SID=76 and SERIAL#=21;
找出拥有会话的进程地址
然后通过PAAD号找出系统进程号
然后在操作系统上kill -9 5241即可。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
