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

Depending on how long you're retaining AWR data, you might even be able to figure out when it started happening by running similar queries on DBA_HIST_ACTIVE_SESS_HISTORY? Years back, I used to do a presentation at conferences called "Database Forensics" about using the ASH views this way.

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