How to find the locking "event"
From: Lyall Barbour <lyallbarbour_at_sanfranmail.com>
Date: Tue, 23 Oct 2018 17:48:12 +0200
Message-ID: <trinity-04ad358b-17a4-4c1b-a895-af187a5fa3b8-1540309692680_at_3c-app-mailcom-lxa07>
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 23 2018 - 17:48:12 CEST
Date: Tue, 23 Oct 2018 17:48:12 +0200
Message-ID: <trinity-04ad358b-17a4-4c1b-a895-af187a5fa3b8-1540309692680_at_3c-app-mailcom-lxa07>
Hello,
Debugging Off-the-shelf applications. Best part of my job...
Oracle 12.1.0.2 - Multitenant
SLES 11 SP4
I have found that an INSERT is having to wait a ton on TX: Row Lock Contention from an intense Document Management application bought, installed and configured about a year and a half ago - Open Text.
Here's my lock/block query that's been the most useful to this point:
SELECT o.object_name, t.start_time, sysdate, s.sid, s.status
, s.machine, s.sql_id
, to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
FROM v$transaction t, v$session s, v$locked_object l, dba_objects o, dba_blockers b
WHERE s.saddr = t.ses_addr
and s.sid = l.session_id
and l.object_id = o.object_id
and s.sid = b.holding_session
ORDER BY start_time;
, s.machine, s.sql_id
, to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
FROM v$transaction t, v$session s, v$locked_object l, dba_objects o, dba_blockers b
WHERE s.saddr = t.ses_addr
and s.sid = l.session_id
and l.object_id = o.object_id
and s.sid = b.holding_session
ORDER BY start_time;
the results of the query find that one blocking session has an open Transaction for about 15 to 30 seconds, with locks on 15 tables. Other sessions are waiting to insert into one of those tables and really bogging down the system.
How do i find the syntax/code that the blocking session ran to lock those 15 objects, in the first place?
OBJECT_NAME START_TIME SYSDATE SID STATUS MACHINE SQL_ID LOGON_TIME
------------------------------ -------------------- --------------------- ---------- -------- ---------------------------------------------------------------- ------------- ---------------------------
DTREEACL 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREECORE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
LLATTRDATA 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
LLATTRBLOBDATA 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREEMULTILINGUAL 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DSUGGESTWORDSPENDING 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
NOTIFYEVENTS 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
WEBNODESMETA_EN_US 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DBROWSEANCESTORSCORE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREEANCESTORS 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_OWNER 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_OBJECTTYPE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_MODIFYDATE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_ATTR_2V4D_R 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREENOTIFY 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
------------------------------ -------------------- --------------------- ---------- -------- ---------------------------------------------------------------- ------------- ---------------------------
DTREEACL 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREECORE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
LLATTRDATA 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
LLATTRBLOBDATA 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREEMULTILINGUAL 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DSUGGESTWORDSPENDING 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
NOTIFYEVENTS 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
WEBNODESMETA_EN_US 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DBROWSEANCESTORSCORE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREEANCESTORS 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_OWNER 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_OBJECTTYPE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_MODIFYDATE 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DFACET_ATTR_2V4D_R 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
DTREENOTIFY 10/23/18 11:34:56 23-OCT-18 11.35.23 AM 668 INACTIVE NGIC\xxxxxxxxxxx fppmu8hcw3y1d 18/OCT/18 22:41:25
TIA
Lyall Barbour