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

There is no stored link between locked rows and the SQL that locked them, so finding the right SQL comes down to luck and psychology: https://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

If that's an AWR report for one hour, and if you have access the the ASH data for that one hour (possibly through an export/import of dba_hist_etc. if you're not allowed direct access, CTAS/export/import of v$active_session_history) then you're likely to have a good sampling of the detail that might give you good guesses: a) look at the row_wait_xxx values for the tx enq waits. do you see lots of waits for the same rowid (obj, file, block, row): is it almost always the same row or is there a slow progression through the table. b) look at the blocking session is it always the same session (or same small subset of sessions) - and is the sql_i c) look at the sessions waiting - are they almost all waiting in the same SQL_ID
d) (psychology) - the key table is MESSAGE - is this someone trying to recreate AQ and doing it badly. I'd look in v$sql for lower(sql_text) like '%select%message%update%' (and a couple of others like "%delete%message%" and "%update%message%';

Regards
Jonathan Lewis

On Thu, 17 Jun 2021 at 01:30, Mladen Gogala <gogala.mladen_at_gmail.com> 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-1217https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l

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