TX row lock contention issues are a bit of a pain as they are normally intermittent and require a bit of co-ordination between DBAs and application developers at the exact time they are happening. To try and circumvent this I wanted to be able to automatically notify the application developers when this issue was occurring and what the affected order was.
To do this I generated a bit of SQL:
select distinct decode(sql_id, '716c1whwnhn77','select * from.apps.order ', '0jhvf4y5xmmhn','select * from apps.order_item ', '98sfcwp2zp6kx','select * from apps.order_relationship ','SQL NOT CURRENTLY KNOWN')||' where rowid = dbms_rowid.rowid_create (1,'||row_wait_obj#||','||row_wait_file#||','||row_wait_block#||','||row_wait_row#||')' from v$session where state='WAITING' and event='enq: TX - row lock contention' and seconds_in_wait > 640;
Using ASH I could determine the SQL_ID candidates which were the most likely to be waiting in a TX lock issue. As it seemed to be a couple of SQL IDs, I then embedded this in a decode statement.
The decode statement wouldtake the relevant SQL ID and then translate that into a select statement, this would then be concatenated with a translation of row_wait_obj#, row_wait_file#, and row_wait_row# from v$session to get a rowid. When you put these together you would then get the relevant select statement which would allow application support to pinpoint the actual orders which were being locked.
select * from apps_order where rowid = dbms_rowid.rowid_create (1,1256151,16,357335);
So the next to do is setup 12c to fire the SQL when an application row lock occurs.