ORACLE系统锁监控与管理

slimebake

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        查找前十条性能差的sql

SELECT * 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;