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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Starting at about 21:06, ten minutes into the test, we had huge row locks on the following SQL statement\u2026<\/p>\n
\r\nUPDATE dcs_inventory SET inventory_lock = :1 WHERE catalog_ref_id = :2\r\n<\/pre>\nWe 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\u2026<\/p>\n
\r\nSQL> select inventory_lock from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109549, 44);\r\n\r\nINVENTORY_LOCK\r\n--------------------\r\ninventoryManager\r\n\r\nSQL> select inventory_lock from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109512, 56);\r\n\r\nINVENTORY_LOCK\r\n--------------------\r\ninventoryManager\r\n\r\nSQL> select catalog_ref_id from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109512, 56);\r\n\r\nCATALOG_REF_ID\r\n----------------------------------------\r\n3413362\r\n\r\nSQL> select display_name from atg_cata_prod.dcs_sku where sku_id = '3413362';\r\n\r\nDISPLAY_NAME\r\n--------------------------------------------------------------------------------\r\nCAMOUFLAGE FIVE PANEL HAT\r\n\r\nSQL>\r\n<\/pre>\nYou can also find the rowid for a given row, and search for it in DBA_HIST_ACTIVE_SESS_HISTORY with something like below<\/p>\n
\r\ndeclare\r\n l_rowid_type number;\r\n l_object_number number;\r\n l_relative_fno number;\r\n l_block_number number;\r\n l_row_number number;\r\n l_ts_type_in number;\r\nbegin\r\n for r in (select order_id,rowid from atg_core_prod.dcspp_order where order_id in ('EXP536424935','EXP536461512','EXP536439773')) loop\r\n dbms_rowid.rowid_info(r.rowid,l_rowid_type,l_object_number,l_relative_fno,l_block_number,l_ts_type_in);\r\n dbms_output.put_line(r.order_id);\r\n dbms_output.put_line(l_rowid_type);\r\n dbms_output.put_line(l_object_number);\r\n dbms_output.put_line(l_relative_fno);\r\n dbms_output.put_line(l_block_number);\r\n dbms_output.put_line(l_ts_type_in);\r\n end loop;\r\nend;\r\n\/\r\n<\/pre>\nKeep 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n