当前等待时间诊断一例

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