Discussion:
How to find the locking "event"
Lyall Barbour
2018-10-23 15:48:12 UTC
Permalink
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hello,</div>

<div>&nbsp; Debugging Off-the-shelf applications.&nbsp; Best part of my job...</div>

<div>&nbsp;</div>

<div>Oracle 12.1.0.2 - Multitenant&nbsp;</div>

<div>SLES 11 SP4</div>

<div>&nbsp;</div>

<div>&nbsp; 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.</div>

<div>&nbsp; Here&#39;s my lock/block query that&#39;s been the most useful to this point:</div>

<div>&nbsp;</div>

<div>SELECT o.object_name, t.start_time, sysdate, s.sid, s.status<br/>
&nbsp; &nbsp;, s.machine, s.sql_id<br/>
&nbsp; &nbsp;, to_char(s.logon_time,&#39;DD/MON/YY HH24:MI:SS&#39;) logon_time<br/>
FROM v&#36;transaction t, v&#36;session s, v&#36;locked_object l, dba_objects o, dba_blockers b<br/>
WHERE s.saddr = t.ses_addr<br/>
and s.sid = l.session_id<br/>
and l.object_id = o.object_id<br/>
and s.sid = b.holding_session<br/>
ORDER BY start_time;</div>

<div>&nbsp;</div>

<div>the results of the query find that one blocking session has&nbsp;an open Transaction for about 15 to 30 seconds, with locks on 15 tables.&nbsp; &nbsp;Other sessions are&nbsp;waiting to insert into one of those tables and really bogging down the system.</div>

<div>&nbsp;</div>

<div>How do i find the syntax/code that the blocking session ran to lock those 15 objects, in the first place?</div>

<div>&nbsp;</div>

<div>OBJECT_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;START_TIME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SYSDATE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SID STATUS &nbsp; MACHINE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SQL_ID &nbsp; &nbsp; &nbsp; &nbsp;LOGON_TIME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
------------------------------ -------------------- --------------------- ---------- -------- ---------------------------------------------------------------- ------------- ---------------------------<br/>
DTREEACL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DTREECORE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
LLATTRDATA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
LLATTRBLOBDATA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DTREEMULTILINGUAL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DSUGGESTWORDSPENDING &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
NOTIFYEVENTS &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
WEBNODESMETA_EN_US &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DBROWSEANCESTORSCORE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DTREEANCESTORS &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DFACET_OWNER &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DFACET_OBJECTTYPE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DFACET_MODIFYDATE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DFACET_ATTR_2V4D_R &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br/>
DTREENOTIFY &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/23/18 11:34:56 &nbsp; &nbsp;23-OCT-18 11.35.23 AM &nbsp; &nbsp; &nbsp; &nbsp;668 INACTIVE NGIC&#92;xxxxxxxxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fppmu8hcw3y1d 18/OCT/18 22:41:25</div>

<div>&nbsp;</div>

<div>TIA</div>

<div>&nbsp;</div>

<div>Lyall Barbour</div></div></body></html>
--
http://www.freelists.org/webpage/oracle-l
p***@gmail.com
2018-10-23 16:14:30 UTC
Permalink
Threw a few views and a few SQLs out there that have always worked for me in giving me just about everything I needed to know. Have not tested in a couple years but worked on Oracle 12 at one point. Most of them probably found here directly or indirectly over the years.



https://gist.github.com/ethanpost/8004370dcfa7a6a6516f5bed0c18db74



Some digging in gv$session_event may also give you what you need if your licensed.



Ethan Post

https://www.linkedin.com/in/ethanraypost/

https://arclogicsoftware.com

https://twitter.com/poststop









From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> On Behalf Of Lyall Barbour
Sent: Tuesday, October 23, 2018 10:48 AM
To: oracle-l <oracle-***@freelists.org>
Subject: How to find the locking "event"



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;



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



TIA



Lyall Barbour

-- http://www.freelists.org/webpage/oracle-l
Dominic Brooks
2018-10-23 18:47:32 UTC
Permalink
There is no fool proof way unless you happen to have been tracing the session from before it too the lock but you can get lucky. Think this is still an accurate statement.

https://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

Sent from my iPhone

On 23 Oct 2018, at 17:38, Lyall Barbour <***@sanfranmail.com<mailto:***@sanfranmail.com>> wrote:

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;

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

TIA

Lyall Barbour
-- http://www.freelists.org/webpage/oracle-l
solutions.axone
2018-10-24 10:12:14 UTC
Permalink
Sent from my Samsung Galaxy smartphone.
-------- Original message --------From: Dominic Brooks <***@hotmail.com> Date: 2018-10-23 2:47 PM (GMT-05:00) To: ***@sanfranmail.com Cc: oracle-l <oracle-***@freelists.org> Subject: Re: How to find the locking "event"

There is no fool proof way unless you happen to have been tracing the session from before it too the lock but you can get lucky. Think this is still an accurate statement.


https://jonathanlewis.wordpress.com/2009/04/19/locking-sql/


Sent from my iPhone


On 23 Oct 2018, at 17:38, Lyall Barbour <***@sanfranmail.com> wrote:







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;
 
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
 
TIA
 
Lyall Barbour

-- http://www.freelists.org/webpage/oracle-l

Loading...