Re: 'enq: TX - row lock contention' and 10046 trace
Date: Thu, 29 Nov 2012 11:53:14 -0800
Message-ID: <CALgGkeBcr6A+hPAJnriJAVaRP1ZxKL4L5tsCVagwxpGgzWajyQ_at_mail.gmail.com>
Thanks for your response, Kyle!
Running the query you provided (adding a timestamp) with: col SAMPLE_TIME format a30
col LOCK_NAME format a21
col WAITER format 99999
col OTYPE format a6
col FILEN format 99999
col BLOCKN format 99999999
Doesn't look exactly the same as your output above...
Mine looks like (a single second):
SAMPLE_TIME LOCK_NAME WAITER LMODE P2 P3 OBJECT OTYPE FILEN BLOCKN WAITING_SQL BLOCKER ------------------------------ --------------------- ------ ---------- ---------- ---------- ------------------------------ ------ --------------- ------------- ----------
...
29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1403 4 5898263 1266032 IDX_BIZDOC_USERSTATUS INDEX 5 1245096 3ydys4ar7mauv 2412 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1412 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1572 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1601 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1664 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1713 4 327727 4802033 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2015 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1717 4 5898263 1266032 IDX_BIZDOC_USERSTATUS INDEX 5 1245096 3ydys4ar7mauv 2412 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1785 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1790 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1806 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1836 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1839 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 5 1245126 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1890 4 5898263 1266032 IDX_BIZDOC_USERSTATUS INDEX 5 1245096 3ydys4ar7mauv 2412 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 1992 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 5 7422 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2015 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2021 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 5 1245126 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2042 4 327727 4802033 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2015 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2083 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 5 7422 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2098 4 5898263 1266032 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2412 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2103 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2124 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 5 1245126 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2127 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2160 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2208 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2211 4 7733283 1214364 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 1992 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2261 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2284 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2289 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2317 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2335 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2339 4 5898263 1266032 IDX_BIZDOC_USERSTATUS INDEX 0 0 3d549m5yf6gyv 2412 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2349 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2369 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2378 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2390 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 4b7d6vbxx87cr 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2394 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2398 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2412 4 2883608 2257046 IDX_BIZDOC_ROUTINGSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2431 4 4128795 1953483 IDX_BIZDOC_USERSTATUS INDEX 5 1245126 3ydys4ar7mauv 2083 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2451 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 2445 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2623 4 7733283 1214364 IDX_BIZDOC_USERSTATUS INDEX 0 0 3ydys4ar7mauv 1992 29-NOV-12 09.03.18.824 AM enq: TX - row lock c 2646 4 2883608 2257046 IDX_BIZDOC_USERSTATUS INDEX 5 500862 3ydys4ar7mauv 2445
...
On Thu, Nov 29, 2012 at 10:51 AM, kyle Hailey <kylelf_at_gmail.com> wrote:
> select SAMPLE_TIME,
> substr(event,0,20) lock_name,
> ash.session_id waiter,
> mod(ash.p1,16) lmode,
> ash.p2 p2,
> ash.p3 p3,
> o.object_name object,
> o.object_type otype,
> CURRENT_FILE# filen,
> CURRENT_BLOCK# blockn,
> ash.SQL_ID waiting_sql,
> BLOCKING_SESSION blocker
> --,ash.xid
> from
> v$active_session_history ash,
> all_objects o
> where
> event like 'enq: %'
> and o.object_id (+)= ash.CURRENT_OBJ#
> /
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 29 2012 - 20:53:14 CET