Re: no booking session info in ash on enq: TX - row lock contention 12.1.0.2.16.01.2016

From: Anton Bushmelev <djeday84_at_gmail.com>
Date: Mon, 18 Jul 2016 18:16:10 +0300
Message-Id: <D8317588-FE29-4040-8DE9-AA1770A0A5BB_at_gmail.com>



and no info about blocker from v$session:

 sql> _at_lock3

           |            |                 |                      |                                     |   BLOCKING |                   |                 |
   INST_ID |        SID | USERNAME        | CLIENT_IDENTIFIER    | EVENT                               |        SID | BLOCKING_INSTANCE | SECONDS_IN_WAIT |  WAIT_TIME
---------- | ---------- | --------------- | -------------------- | ----------------------------------- | ---------- | ----------------- | --------------- | ----------
         2 |       7907 | SMBD            | <NULL>               | enq: TX - row lock contention       | <NULL>     | <NULL>            |              30 |          0

1 row selected.

 sql> l
  1 select inst_id,sid, username,CLIENT_IDENTIFIER, event, blocking_session,blocking_instance,   2 seconds_in_wait, wait_time
  3 from gv$session
  4 where state = 'WAITING'
  5 and wait_class != 'Idle'
  6* order by blocking_session,username,1

> On 18 Jul 2016, at 18:04, Anton Bushmelev <djeday84_at_gmail.com> wrote:
>
> Hi all, faced with enq: TX - row lock contention
> but no booking session info in gv$session or ash =\
>
> select count (*),event,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,blocking_inst_id from gv$active_session_history where event like '%TX%' and sample_time> sysdate-1/24 group by bLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,blocking_inst_id,event;
>
> | | BLOCKING | |
> COUNT(*) | Event | SID | BLOCKING_SESSION_SERIAL# | BLOCKING_INST_ID
> ---------- | ---------------------------------------- | ---------- | ------------------------ | ----------------
> 3520 | enq: TX - row lock contention | | |
> 1 | enq: TX - index contention | 10908 | 33849 | 1
> 5 | enq: TX - row lock contention | 2259 | 3041 | 1
>
> what can it be ?
>
> ps: Bug 5010879 - V$SESSION slow and does not show any BLOCKING_SESSION column data (Doc ID 5010879.8)
> not my case, we use 12.1.0.2.16.01.2016

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 18 2016 - 17:16:10 CEST

Original text of this message