当前等待时间诊断一例

最新推荐文章于 2024-09-08 22:20:09 发布

转载最新推荐文章于 2024-09-08 22:20:09 发布 · 497 阅读

1、查看当前events，比如发现row lock contention 偏高，可能是想加锁，但是有别的session占着，所以等待。
SQL> select event,count(*) from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%' group by event order by count(*);
SQL> select event,sid,p1,p2,p3 from gv$session_wait where event not like 'SQL*%' and event not like 'rdbms%';
EVENT SID P1 P2 P3
---------------------------------- ---- ---------- ---------- ----------
enq: TX - row lock contention 101 1415053318 196638 55836
RMAN backup & recovery I/O 5 1 256 2147483647
enq: TX - row lock contention 993 1415053318 524293 51153
RMAN backup & recovery I/O 25 1 256 2147483647
db file sequential read 27 16 2876703 1

2、下面我们通过查看事件enq: TX - row lock contention来看看这些session都在等什么
SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
87549 19 3200776 13
87549 6 3385926 39

3、通过上面sql查找出来的对象编号找到对应的对象名称
SQL> select object_name from dba_objects where object_id in (87549);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
PMUSRINF

4、通过对象名称找出该对象的对应属性，对象属性为TABLE
SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='PMUSRINF';

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- -------------------
NEWSDUSER PMUSRINF 87549 87549 TABLE

5、通过正在等待的SID查看它们都在执行什么操作
SQL> select sid,sql_text from v$session a,v$sql b where sid in(101,993) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);
SID SQL_TEXT
---------- ----------------------------------------------------------------------------------------------------------------------------------
101 select opeip as last_ip, TO_CHAR (TO_DATE (operdate || opetime, 'yyyyMMddHH24miss'), 'yyyy-MM-dd HH24:mi:ss')
AS last_login_time from LIMOPEREC where opeid='U201502110739' and opetype='03' and operec=(
select max(operec) from LIMOPEREC where opeid='U201502110739' and opetype='03' )

101 update PMUSRINF set random = 'oucqhmncas' where user_id = 'U201502110739'
993 SELECT FJPATH FROM FJ WHERE instr(FJPATH,'/posa/dat/upload/201604/05/03055225/20160405095215.jpg') > 0

6、下面我们去找一下对应sid产生的锁
SQL> select SID,TY,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;

解决方法：
1：通过v$session找到BLOCK=1的用户，告知用户提交事务
2：通过sid找到pid，kill掉该进程
3：更改sql语句，SELECT ... FOR UPDATE no wait