Re: How to figure out which statements are locking row in one table?

From: Tim Gorman <tim.evdbt_at_gmail.com>
Date: Wed, 16 Jun 2021 21:25:37 -0700
Message-ID: <9cbf28da-76e8-c35b-b4e7-fcae9e0a575e_at_gmail.com>

And of course I answered without thinking; those queries will display the blocked SQL, not the blocking SQL.  Sorry about that...

Neither ASH nor AWR captures data from V$LOCK, so I don't think the blocking SQL can be figured out using those histories, so it can only be captured in real-time with something like...

    select   s.sql_id, count(*) cnt
    from     v$session s, v$lock l
    where    s.sid = l.sid

    and      l.type = 'TX'
    and      l.block > 0
    group by s.sql_id
    order by cnt desc;

This doesn't pin it down to an object, but if the list of SQL_IDs returned isn't too long, perhaps it can be inferred?

Hope that helps?

> On 6/16/2021 5:41 PM, Tim Gorman wrote:

>> Perhaps...
>>
>> select SQL_ID, count(*) from V$ACTIVE_SESSION_HISTORY where EVENT =
>> 'enq: TX - row lock wait' group by SQL_ID order by 2 desc;
>>
>> ...or...
>>
>> select SQL_ID, count(*) from V$ACTIVE_SESSION_HISTORY where
>> CURRENT_OBJ# = 93432 group by SQL_ID order by 2 desc;
>>
>> ...to find what's locking on the MESSAGE table PART_3 partition, for
>> example?
>>
>>
>>
>> On 6/16/2021 5:29 PM, Mladen Gogala wrote:
>>>
>>> I have a problem: my application is doing a lot of locking. I got
>>> the segments that are being locked from an AWR report:
>>>
>>>
>>> Segments by Row Lock Waits
>>>
>>> * % of Capture shows % of row lock waits for each top segment
>>> compared
>>> * with total row lock waits for all segments captured by the Snapshot
>>> * When ** MISSING ** occurs, some of the object attributes may not
>>> be available
>>>
>>> Owner Tablespace Name Object Name Subobject Name Obj. Type Obj#
>>> Dataobj# Row Lock Waits % of Capture PDB Name
>>> APP_USER USERS MESSAGE PART_3 TABLE PARTITION 93432 93432
>>> 852,300 97.56 USACH
>>> APP_USER USERS CLEARINGRECNCLNTOTAL TABLE 100137 100137
>>> 17,403 1.99 USACH
>>> APP_USER USERS PAYMENTSFEEDCHUNK TABLE 100186 100186 2,813
>>> 0.32 USACH
>>> APP_USER USERS FEDACCOUNTBALANCES TABLE 99552 99552 971 0.11
>>> USACH
>>> APP_USER USERS CLEARINGSYSTEMACCOUNT TABLE 99718 99718 111
>>> 0.01 USACH
>>>
>>> ------------------------------------------------------------------------
>>>
>>> How do I determine the SQL statements that are doing all the
>>> locking?  I even considered locking the table in the exclusive mode
>>> and then finding the sessions waiting for the lock.. Obviously
>>> almost all the row lock waits are concentrated in a single
>>> partition. A part of the application needs to be rewritten but I
>>> need to figure out which part.  Any ideas how to get the SQL
>>> statement(s) doing the locking?
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217
>>> https://dbwhisperer.wordpress.com
>>> -- http://www.freelists.org/webpage/oracle-l
>>
>

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

Received on Thu Jun 17 2021 - 06:25:37 CEST