异常锁处理记录 - 墨天轮文档

异常锁处理记录

xuerong

718

2页

6次

2020-07-13

5墨值下载

--查看等待事件

col event for a40

set pagesize 100

select inst_id,event,count(*) from gv$session_wait a WHERE a.WAIT_CLASS#<>6

group by inst_id,event order by count(*) desc;

INST_ID EVENT COUNT(*)

---------- ---------------------------------------- ----------

1 enq: TX - row lock contention 149

2 enq: TX - row lock contention 8

1 db file sequential read 1

select * from (select sql_id,count(*) from v$session where event ='&event_name'

group by sql_id order by 2 desc) where rownum<10 ;

---通过等待事件查看对应信息

col username for a10

select inst_id,sid,serial#, username,

SQL_ID,PREV_SQL_ID,event,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION from

gv$session where event='&event_name';

Enter value for event_name: enq: TX - row lock contention

INST_ID SID SERIAL# USERNAME SQL_ID PREV_SQL_ID EVENT

FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION

---------- ---------- ---------- ---------- ------------- -------------

---------------------------------------- -----------------------

----------------------

2 159 22801 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

2 162 17961 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

2 729 19281 WEIXIN ad0mu9tsnbdyj 1hjx6vdmgnx8v enq: TX -

row lock contention 1 1444

2 1261 8101 WEIXIN 1hjx6vdmgnx8v c7sm8tgc15m2t enq: TX -

row lock contention 1 1444

2 2151 2301 WEIXIN bh4m74vhq8n8r 1hjx6vdmgnx8v enq: TX -

row lock contention 1 1444

2 2715 43335 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

2 2969 50185 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

select inst_id,event,count(*) from gv$session_wait a WHERE a.WAIT_CLASS#<>6

group by inst_id,event order by count(*) desc;

----根据 FINAL_BLOCKING_SESSION 信息查看信息

set linesize 230

col sid format 9999;

col serial# format 999999;

col spid for a10;

col username format a10;

col program format a25;

col terminal format a12;

col client_info for a20;

col machine format a15;

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

select s.sid,s.serial#,p.spid,s.logon_time,s.username,s.status,s.sql_id,

s.prev_sql_id,s.server,s.machine,s.program,p.program,s.client_info,s.terminal

from v$session s,v$process p where s.paddr=p.addr and s.sid in(&sid);

Enter value for sid: 1444

SID SERIAL# SPID LOGON_TIME USERNAME STATUS SQL_ID

PREV_SQL_ID SERVER MACHINE PROGRAM PROGRAM

CLIENT_INFO TERMINAL

----- ------- ---------- ------------------- ---------- -------- -------------

------------- --------- --------------- -------------------------

------------------------- -------------------- ------------

1444 63257 32554 2020-03-21 20:47:00 WEIXIN ACTIVE 1khh2cagryw95

50cjz9j48hs60 DEDICATED testdb01 oracle@testdb01 (TNS V1-V oracle@wtdb01

---根据 sid 查看具体 sql 内容

---

select dbms_lob.substr(a.SQL_FULLTEXT) from v$sql a where sql_id='&sql_id';

Enter value for sql_id: 1khh2cagryw95

--sid

select 'alter system kill session '''||sid||','||serial#||''';' from v$session

where sid='&sid';

如果如此还没有终止，则需通过系统级别的命令杀死改 SQL

SQL> select b.username,b.SPID from v$session a,v$process b where a.PADDR=b.ADDR

and a.sid=1444;

USERNAME SPID

--------------- ------------

oracle 6066

其中 v$process 视图中 username 的含义为该进程所属的 OS user SPID 的含义为该进程的操作系统进程

号

然后通过系统级别命令 kill -9 6066 终止即可

--查看等待事件

col event for a40

set pagesize 100

select inst_id,event,count(*) from gv$session_wait a WHERE a.WAIT_CLASS#<>6

group by inst_id,event order by count(*) desc;

INST_ID EVENT COUNT(*)

---------- ---------------------------------------- ----------

1 enq: TX - row lock contention 149

2 enq: TX - row lock contention 8

1 db file sequential read 1

select * from (select sql_id,count(*) from v$session where event ='&event_name'

group by sql_id order by 2 desc) where rownum<10 ;

---通过等待事件查看对应信息

col username for a10

select inst_id,sid,serial#, username,

SQL_ID,PREV_SQL_ID,event,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION from

gv$session where event='&event_name';

Enter value for event_name: enq: TX - row lock contention

INST_ID SID SERIAL# USERNAME SQL_ID PREV_SQL_ID EVENT

FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION

---------- ---------- ---------- ---------- ------------- -------------

---------------------------------------- -----------------------

----------------------

2 159 22801 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

2 162 17961 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

2 729 19281 WEIXIN ad0mu9tsnbdyj 1hjx6vdmgnx8v enq: TX -

row lock contention 1 1444

2 1261 8101 WEIXIN 1hjx6vdmgnx8v c7sm8tgc15m2t enq: TX -

row lock contention 1 1444

2 2151 2301 WEIXIN bh4m74vhq8n8r 1hjx6vdmgnx8v enq: TX -

row lock contention 1 1444

2 2715 43335 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

2 2969 50185 WEIXIN 1khh2cagryw95 50cjz9j48hs60 enq: TX -

row lock contention 1 1444

select inst_id,event,count(*) from gv$session_wait a WHERE a.WAIT_CLASS#<>6

group by inst_id,event order by count(*) desc;

----根据 FINAL_BLOCKING_SESSION 信息查看信息

set linesize 230

col sid format 9999;

col serial# format 999999;

col spid for a10;

col username format a10;

col program format a25;

col terminal format a12;

col client_info for a20;

col machine format a15;

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

select s.sid,s.serial#,p.spid,s.logon_time,s.username,s.status,s.sql_id,

s.prev_sql_id,s.server,s.machine,s.program,p.program,s.client_info,s.terminal

from v$session s,v$process p where s.paddr=p.addr and s.sid in(&sid);

Enter value for sid: 1444

of 2

5墨值下载

oracle

关注

评论