In the Oracle database, if you have purchased the tuning pack, you have full access to the DBA_HIST_% views. AWR, or Automated Workload Repository, is normally accessed via grid control or another tool.
However, the raw data in the DBA_HIST views is absolutely invaluable. This post will show how you can find which rows were locked and blocking another user.
To do this, we use the DBA_HIST_ACTIVE_SESS_HISTORY view. This view reflects historical samples taken every ten seconds of the live data in the GV$ACTIVE_SESSION_HISTORY view, which itself reflects samples taken every second.
For us to find the rows, we must query for sessions waiting on the “enq: TX – row lock contention” event during our window of interest. We also want to group by SQL_ID, so we can see which statements were being executed that were most impacted.
Starting at about 21:06, ten minutes into the test, we had huge row locks on the following SQL statement…
UPDATE dcs_inventory SET inventory_lock = :1 WHERE catalog_ref_id = :2
We reverse engineered the row(s) on which it was waiting (this is where the Oracle database really shines with this kind instrumentation), and found the following…
SQL> select inventory_lock from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109549, 44);
INVENTORY_LOCK
--------------------
inventoryManager
SQL> select inventory_lock from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109512, 56);
INVENTORY_LOCK
--------------------
inventoryManager
SQL> select catalog_ref_id from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109512, 56);
CATALOG_REF_ID
----------------------------------------
3413362
SQL> select display_name from atg_cata_prod.dcs_sku where sku_id = '3413362';
DISPLAY_NAME
--------------------------------------------------------------------------------
CAMOUFLAGE FIVE PANEL HAT
SQL>
You can also find the rowid for a given row, and search for it in DBA_HIST_ACTIVE_SESS_HISTORY with something like below
declare
l_rowid_type number;
l_object_number number;
l_relative_fno number;
l_block_number number;
l_row_number number;
l_ts_type_in number;
begin
for r in (select order_id,rowid from atg_core_prod.dcspp_order where order_id in ('EXP536424935','EXP536461512','EXP536439773')) loop
dbms_rowid.rowid_info(r.rowid,l_rowid_type,l_object_number,l_relative_fno,l_block_number,l_ts_type_in);
dbms_output.put_line(r.order_id);
dbms_output.put_line(l_rowid_type);
dbms_output.put_line(l_object_number);
dbms_output.put_line(l_relative_fno);
dbms_output.put_line(l_block_number);
dbms_output.put_line(l_ts_type_in);
end loop;
end;
/
Keep in mind this is mostly effective with UPDATE’s and assumes that the rows aren’t migrated as they are in a table shrink. However, it can be invaluable.