And of course I answered without thinking; those queries will display
the blocked SQL, not the blocking SQL. Sorry about that...
Neither ASH nor AWR captures data from V$LOCK, so I don't think the
blocking SQL can be figured out using those histories, so it can only be
captured in real-time with something like...
select s.sql_id, count(*) cnt
from v$session s, v$lock l
where s.sid = l.sid
and l.type = 'TX'
and l.block > 0
group by s.sql_id
order by cnt desc;
This doesn't pin it down to an object, but if the list of SQL_IDs
returned isn't too long, perhaps it can be inferred?
Hope that helps?
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-1217
https://dbwhisperer.wordpress.com
-- //www.freelists.org/webpage/oracle-l
