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

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