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

  • From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 17 Jun 2021 12:17:05 +0100

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@xxxxxxxxx> 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

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

Other related posts: