通信人家园
标题: ORACLE系统锁监控与管理 [查看完整版帖子] [打印本页]
时间: 2012-9-22 12:06
作者: slimebake
标题: ORACLE系统锁监控与管理
1.1 查看当前各用户正在使用的语句样例SQL语句：
COL “操作系统用户” FOR A16
COL “Oracle用户” FOR A16
COL “当前运行的SQL语句” FOR A80
SELECT osuser “操作系统用户”,
Username “Oracle用户”,
sql_text “当前运行的SQL语句”
from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
操作系统用户 Oracle用户 当前运行的SQL语句
---------------- ---------------- -----------------------------------------------------------------
SYSTEM foreign_id, state, status, heuristic_dflt, session
SYSTEM _vector, reco_vector, 3600*24*(sysdate-reco_time), 3600*2
SYSTEM 4*(sysdate-nvl(heuristic_time,fail_time)), global_commit#, type#
SYSTEM from pending_trans$ where session_vector != '00000000'
SYSTEM DBSNMP select name from v$database
1.2 查询被锁住的用户语句样例SQL语句：
下列查询会返回当前被锁住的用户列表：
SELECT a.username “用户名”, a.sid “SID号”, a.serial# “序列号”,
b.id1 “ID1”, c.sql_text “SQL语句”
FROM v$session a, v$lock b, v$sqltext c
WHERE a.lockwait is not null AND a.lockwait = b.kaddr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value;
SQL> l
1 SELECT a.username "用户名", a.sid "SID号", a.serial# "序列号",
2 b.id1 "ID1", c.sql_text "SQL语句"
3 FROM v$session a, v$lock b, v$sqltext c
4 WHERE a.lockwait is not null AND a.lockwait = b.kaddr
5 and a.sql_address = c.address
6* and a.sql_hash_value = c.hash_value
SQL> /
用户名 SID号 序列号 ID1 SQL语句
--------------------- -------- ---------- ---------- ------------------------------------
INMON 18 57968 196685 update tst2 set sal=98765 where name='zyj'
已用时间: 00: 00: 02.01
1.3 查询锁住别人的用户SQL语句样例SQL语句：
col "发出锁用户" for a10
col "等待锁用户" for a10
col "SID号" for 99999
col "Serial#号" for 99999
col "Sql hash值" for 9999999999
col "发出锁机器" for a14
col "等待锁机器" for a14
col “锁对象1号” for 999999
col “锁对象2号” for 999999
SELECT
bs.username "发出锁的用户",
ws.username "等待锁的用户",
bs.sid "SID号",
bs.serial# "Serial#号",
bs.sql_hash_value "Sql hash值",
bs.machine "发出锁机器",
ws.machine "等待锁机器",
hk.id1 “锁对象1号”,
hk.id2 “锁对象2号”
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE (+) = hk.TYPE
AND wk.id1 (+) = hk.id1
AND wk.id2 (+) = hk.id2
AND hk.sid = bs.sid(+)
AND wk.sid = ws.sid(+)
and (bs.username is not null) and (bs.username<>'SYSTEM')
and (bs.username<>'SYS')
ORDER BY 1 ;
SQL> SELECT
2 bs.username "发出锁的用户",
3 ws.username "等待锁的用户",
4 bs.sid "SID号",
5 bs.serial# "Serial#号",
6 bs.sql_hash_value "Sql hash值",
7 bs.machine "发出锁机器",
8 ws.machine "等待锁机器",
9 hk.id1 "锁对象1号",
10 hk.id2 "锁对象2号"
11 FROM v$lock hk, v$session bs, v$lock wk, v$session ws
12 WHERE hk.lmode != 0
13 AND hk.lmode != 1
14 AND wk.request != 0
15 AND wk.TYPE (+) = hk.TYPE
16 AND wk.id1 (+) = hk.id1
17 AND wk.id2 (+) = hk.id2
18 AND hk.sid = bs.sid(+)
19 AND wk.sid = ws.sid(+)
20 and (bs.username is not null) and (bs.username<>'SYSTEM')
21 and (bs.username<>'SYS')
22 ORDER BY 1 ;
发出锁的用户 等待锁的用户 SID号 Serial#号 Sql hash值 发出锁机器 等待锁机器 锁对象1号 锁对?
------------ ------------ ------ --------- ----------- -------------- -------------- --------- -----
INMON INMON 17 40599 4213403015 WORKGROUP\ZYJ WORKGROUP\ZYJ 589840 623
已用时间: 00: 00: 00.00
1.4 查询控制、等待锁的会话SQL语句样例：SQL语句：
SELECT a.SID "会话标识",
DECODE(a.lmode, 2, '控制','请求' ) "等待锁?", a.ID1 "控制或等待对象#" ,
a.ctime "等待总时间", b.NAME "控制或等待对象名"
FROM V$LOCK a, sys.obj$ b
WHERE b.OBJ# = a.ID1 AND a.REQUEST = 0 AND( a.lmode=2 OR a.lmode =3 )
ORDER BY a.ID1;
会话标识 等待锁? 控制或等待对象# 控制或等待对象名
-------- ---------- --------------- -----------------
5 请求 3 I_OBJ#
5 请求 59 VIEW$
14 请求 5760 TST2
15 请求 5760 TST2
17 控制 5760 TST2
SQL> select sid,serial#,terminal from v$session where sid in(14,15,17);
SID SERIAL# TERMINAL
------- ---------- ----------------
14 627 ZYJ
15 2260 ZYJ
17 204 ZYJ
SQL> select sid,serial#,terminal from v$session where sid=17;
SID SERIAL# TERMINAL
------- ---------- ----------------
17 204 ZYJ
1.5 查找前十条性能差的sqlSELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
sql_text FROM v$sqlarea ORDER BY disk_reads DESC)
WHERE ROWNUM<10 ;
1.6 捕捉运行很久的SQL:column username format a12
column opname format a16
column progress format a8
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops , v$sql where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value;
1.7 耗资源的进程(top session):col SCHEMA_NAME for a18
col OS_USER_NAME for a18
col ACTION for a30
col USER_NAME for a18
SELECT s.schemaname schema_name,
decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action,
status session_status,s.osuser os_user_name,
s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[Oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_value
FROM v$sesstat st,v$session s,v$process p
WHERE st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr order by st.value desc,p.spid asc,s.username asc,s.osuser asc;
1.8 监控当前数据库谁在运行什么SQL语句:SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
| 通信人家园 (https://www.txrjy.com/) |
Powered by C114 |