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

There is no easy way I know of.
Doug Burns did a series of blog posts.
I listed his posts out in one of my blog post:

https://www.oraclerealworld.com/finding_the_blocking_sql_in_a_lock_wait/index.html

Kyle

On Wed, Jun 16, 2021 at 5:44 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> 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-1217https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
>

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