Home -> Mailing Lists -> Oracle-L -> How to figure out which statements are locking row in one table?

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

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 16 Jun 2021 20:29:05 -0400
Message-ID: <5e587cb7-e59a-6a0a-6089-c7913c259f01_at_gmail.com> 


  

    
  
  
    
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
Received on Thu Jun 17 2021 - 02:29:05 CEST












Original text of this message