{"id":1491,"date":"2009-04-23T12:00:00","date_gmt":"2009-04-23T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1491"},"modified":"2009-04-23T12:00:00","modified_gmt":"2009-04-23T12:00:00","slug":"diagnosing-locking-problems-using-ashlogminer-part-8","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2009\/04\/23\/diagnosing-locking-problems-using-ashlogminer-part-8\/","title":{"rendered":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 Part 8"},"content":{"rendered":"<p>So what about those SELECT FOR UPDATEs?<\/p>\n<p>I know that they\u2019ll generate redo entries and so something should appear in both log file dumps and the LogMiner output, but what exactly will appear? (This is all on Oracle 10.2.0.4)<\/p>\n<p>For this post I\u2019ll go back to the example from <a href=\"http:\/\/18.133.199.212\/?p=1481\">Part 4<\/a>, where Session 1 performs three different SELECT FOR UPDATE statements against the same table, TEST_TAB1, and rolls the first two back before leaving the third as the statement that\u2019s blocking Session 2. i.e. Three possible guilty parties in very quick succession, which makes the exact source harder to find. This time, I granted select privileges on V$TRANSACTION to TESTUSER, so that we could take a quick peek at the contents after each SELECT FOR UPDATE. I&#8217;ve also set up LogMiner access in the SYS session, as in the last couple of posts.<\/p>\n<p><strong>Session 1 \u2013 Connected as TESTUSER<\/strong><\/p>\n<pre>SQL&gt; select pk_id, object_name from test_tab1 order by pk_id desc for update;\n\nTrimmed ...\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 319 STMT_AUDIT_OPTION_MAP\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 317 STMT_AUDIT_OPTION_MAP\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 316 TABLE_PRIVILEGE_MAP\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 314 TABLE_PRIVILEGE_MAP\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 313 SYSTEM_PRIVILEGE_MAP\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 311 SYSTEM_PRIVILEGE_MAP\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 259 DUAL\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 258 DUAL\n\n4477 rows selected.\n\nSQL&gt; select start_time, xid, xidusn, xidslot,\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 xidsqn, start_scn, to_char(start_scn, 'XXXXXXXXXX')\n\u00a0 3\u00a0 from v$transaction\n\u00a0 4\u00a0 order by start_time;\n\nSTART_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XIDUSN XIDSLOT\u00a0 XIDSQN\u00a0 START_SCN TO_CHAR(STA\n-------------------- ---------------- ------ ------- ------- ---------- -----------\n04\/23\/09 09:40:16\u00a0\u00a0\u00a0 0003001100008615\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0 17\u00a0\u00a0 34325\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\n\nSQL&gt; rollback\n\nRollback complete.\n\nSQL&gt; select pk_id from test_tab1 where object_name='SYSTEM_PRIVILEGE_MAP' for update;\n\n\u00a0\u00a0\u00a0\u00a0 PK_ID\n----------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 311\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 313\n\nSQL&gt; select start_time, xid, xidusn, xidslot,\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 xidsqn, start_scn, to_char(start_scn, 'XXXXXXXXXX')\n\u00a0 3\u00a0 from v$transaction\n\u00a0 4\u00a0 order by start_time;\n\nSTART_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XIDUSN XIDSLOT\u00a0 XIDSQN\u00a0 START_SCN TO_CHAR(STA\n-------------------- ---------------- ------ ------- ------- ---------- -----------\n04\/23\/09 09:40:20\u00a0\u00a0\u00a0 0002000100008DAA\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 36266\u00a0\u00a0 84788253\u00a0\u00a0\u00a0\u00a0 50DC41D\n\nSQL&gt; rollback;\n\nRollback complete.\n\nSQL&gt; select pk_id from test_tab1 where pk_id=313 for update;\n\n\u00a0\u00a0\u00a0\u00a0 PK_ID\n----------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 313\n\nSQL&gt; select start_time, xid, xidusn, xidslot,\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 xidsqn, start_scn, to_char(start_scn, 'XXXXXXXXXX')\n\u00a0 3\u00a0 from v$transaction\n\u00a0 4\u00a0 order by start_time;\n\nSTART_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XIDUSN XIDSLOT\u00a0 XIDSQN\u00a0 START_SCN TO_CHAR(STA\n-------------------- ---------------- ------ ------- ------- ---------- -----------\n04\/23\/09 09:40:20\u00a0\u00a0\u00a0 000900110000891B\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0 17\u00a0\u00a0 35099\u00a0\u00a0 84788256\u00a0\u00a0\u00a0\u00a0 50DC420<\/pre>\n<p>So Session 1 has executed three different queries, all of which lock one or more rows including the row with PK_ID=313, has rolled back the first two (releasing the locks) and has just PK_ID=313 locked now.<\/p>\n<p><strong>Session 2 \u2013 Connected as TESTUSER<\/strong><\/p>\n<pre>SQL&gt; select pk_id from test_tab1 where pk_id=313 for update;<\/pre>\n<p>Session 2 hangs, waiting for the lock<\/p>\n<p><strong>Session 1 &#8211; Connected as TESTUSER<\/strong><\/p>\n<pre>SQL&gt; rollback;\n\nRollback complete.<\/pre>\n<p>The lock is released and Session 2 acquires the lock and then releases it.<\/p>\n<p><strong>Session 2 &#8211; Connected as TESTUSER<\/strong><\/p>\n<pre>\u00a0\u00a0\u00a0\u00a0 PK_ID\n----------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 313\n\nSQL&gt; select start_time, xid, xidusn, xidslot,\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 xidsqn, start_scn, to_char(start_scn, 'XXXXXXXXXX')\n\u00a0 3\u00a0 from v$transaction\n\u00a0 4\u00a0 order by start_time;\n\nSTART_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XIDUSN XIDSLOT\u00a0 XIDSQN\u00a0 START_SCN TO_CHAR(STA\n-------------------- ---------------- ------ ------- ------- ---------- -----------\n04\/23\/09 09:40:22\u00a0\u00a0\u00a0 00080005000081D3\u00a0\u00a0\u00a0\u00a0\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0 33235\u00a0\u00a0 84788259\u00a0\u00a0\u00a0\u00a0 50DC423\n\nSQL&gt; rollback;\n\nRollback complete.<\/pre>\n<p>Taken as a whole, the time-line looks like this<\/p>\n<p><strong>Transaction ID\u00a0\u00a0\u00a0 Session 1 Activity\u00a0 Transaction ID\u00a0\u00a0 Session 2 Activity<\/strong><\/p>\n<p>0003001100008615\u00a0 Whole Table Locked<br \/>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Locks Released<br \/>0002000100008DAA\u00a0 Two Rows Locked<br \/>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Locks Released<br \/>000900110000891B\u00a0 PK_ID=313 Locked\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Waiting to lock PK_ID=313<br \/>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lock Released\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00080005000081D3 PK_ID=313 Locked\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <br \/>OK, let\u2019s see what LogMiner makes of this. First, let\u2019s look for any entries associated with the specific transaction that was the blocker.<\/p>\n<pre>SQL&gt; select username,session# sid,serial#,sql_redo from v$logmnr_contents \n     where\u00a0XID = '&amp;&amp;blocking_xid';\nEnter value for blocking_xid: 000900110000891B\nold\u00a0\u00a0 1: select username,session# sid,serial#,sql_redo from v$logmnr_contents \n         where\u00a0XID = '&amp;&amp;blocking_xid'\nnew\u00a0\u00a0 1: select username,session# sid,serial#,sql_redo from v$logmnr_contents \n         where\u00a0XID = '000900110000891B'\n\nUSERNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SID\u00a0\u00a0\u00a0 SERIAL#\n------------------------------ ---------- ----------\nSQL_REDO\n-----------------------------------------------------------------------------------------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nrollback;<\/pre>\n<p>Mmmm \u2026 \u2018rollback\u2019. Not too helpful, is it? Maybe if I look at the undo segment and slot from another of Kyle&#8217;s queries?<\/p>\n<pre>SQL&gt; select distinct xid , xidusn, xidslt, xidsqn, \n     username, session# sid, serial# , sql_redo\n\u00a0 2\u00a0 from v$logmnr_contents\n\u00a0 3\u00a0 where timestamp &gt; sysdate- &amp;minutes\/(60*24)\n\u00a0 4\u00a0 and xidusn=&amp;my_usn\n\u00a0 5\u00a0 and xidslt=&amp;my_slot;\nEnter value for minutes: 5\nold\u00a0\u00a0 3: where timestamp &gt; sysdate- &amp;minutes\/(60*24)\nnew\u00a0\u00a0 3: where timestamp &gt; sysdate- 5\/(60*24)\nEnter value for my_usn: 9\nold\u00a0\u00a0 4: and xidusn=&amp;my_usn\nnew\u00a0\u00a0 4: and xidusn=9\nEnter value for my_slot: 17\nold\u00a0\u00a0 5: and xidslt=&amp;my_slot\nnew\u00a0\u00a0 5: and xidslt=17<p><\/p><p>XID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XIDUSN\u00a0\u00a0\u00a0\u00a0 XIDSLT\u00a0\u00a0\u00a0\u00a0 XIDSQN USERNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SID\u00a0\u00a0\u00a0 SERIAL#\n---------------- ---------- ---------- ---------- ------------------- ---------- ----------\nSQL_REDO\n---------------------------------------------------------------------------------\n000900110000891B\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 17\u00a0\u00a0\u00a0\u00a0\u00a0 35099\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nrollback;<\/p><p>00090011FFFFFFFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 17 4294967295\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nUnsupported<\/p><\/pre>\n<p>Now that&#8217;s a bit interesting because I think we&#8217;ll find that &#8216;Unsupported&#8217; operation is the third SELECT FOR UPDATE (which blocks Session 2) but the Transaction ID looks wrong. Oh, and why is the SQL_REDO &#8216;Unsupported&#8217;? Well, would you really want to redo a SELECT FOR UPDATE that merely locks rows?<\/p>\n<p>Next I\u2019ll try displaying all operations against TEST_TAB1 in the\u00a0past 5 minutes. I\u2019ll group the results so we only see the discrete actions and how many entries there are for each.<\/p>\n<p>\u00a0<\/p>\n<pre>SQL&gt; select xid, xidusn, xidslt, xidsqn, session#, serial#, sql_redo, count(*)\n\u00a0 2\u00a0 from v$logmnr_contents\n\u00a0 3\u00a0 where timestamp &gt; sysdate- &amp;minutes\/(60*24)\n\u00a0 4\u00a0 and table_name='TEST_TAB1'\n\u00a0 5* group by xid, xidusn, xidslt, xidsqn, session#, serial#, sql_redo\nEnter value for minutes: 60\nold\u00a0\u00a0 3: where timestamp &gt; sysdate- &amp;minutes\/(60*24)\nnew\u00a0\u00a0 3: where timestamp &gt; sysdate- 60\/(60*24)\n\nXID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0XIDUSN\u00a0\u00a0\u00a0XIDSLT\u00a0\u00a0\u00a0\u00a0 XIDSQN\u00a0\u00a0 SESSION#\u00a0\u00a0\u00a0 SERIAL# SQL_REDO\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*)\n---------------- -------- -------- ---------- ---------- ---------- ----------- ----------\n00080005FFFFFFFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 4294967295\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Unsupported\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\n0003001100008615\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 17\u00a0\u00a0\u00a0\u00a0\u00a0 34325\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Unsupported\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08858\n00020001FFFFFFFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 4294967295\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Unsupported\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\n00090011FFFFFFFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 17 4294967295\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Unsupported\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/pre>\n<p>Good &#8211; that&#8217;s starting to look more like it. I can see all 4 discrete SELECT FOR UPDATE transactions from the test (the rollback operation returned by the previous query isn&#8217;t necessarily specific to TEST_TAB1 so I&#8217;m not surprised it doesn&#8217;t appear). The\u00a0XIDs still look a little screwy and you&#8217;re trusting me at this stage that these are SELECT FOR UPDATEs. Notice the number of entries for the different transactions &#8211; two single row updates, one 2 row update and a multiple row update. <\/p>\n<p>However, the transaction IDs for three of the transactions have the wrong sequence number of FFFFFFFF and even this output is the best I&#8217;ve seen. I&#8217;ve run this several times and sometimes it&#8217;s captured\u00a0by LogMiner, sometime it isn&#8217;t. I appreciate that&#8217;s a little vague, but I have very little confidence in some of the results I&#8217;ve seen on different tests.<\/p>\n<p>I dumped the log file for this example, so I&#8217;ll look at that in the next part. <\/p>\n<p>Believe me when I say I&#8217;m aware of how far I&#8217;ve strayed from identifying the blocking SQL statement (you won&#8217;t be getting that from redo entries) but I suppose I might as well carry on for one more post, maybe two.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>So what about those SELECT FOR UPDATEs? I know that they\u2019ll generate redo entries and so something should appear in both log file dumps and the LogMiner output, but what exactly will appear? (This is all on Oracle 10.2.0.4) For this post I\u2019ll go back to the example from Part 4, where Session 1 performs&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2009\/04\/23\/diagnosing-locking-problems-using-ashlogminer-part-8\/\">Continue reading <span class=\"screen-reader-text\">Diagnosing Locking Problems using ASH\/LogMiner \u2013 Part 8<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1491","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1487,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/20\/diagnosing-locking-problems-using-ash-part-6\/","url_meta":{"origin":1491,"position":0},"title":"Diagnosing Locking Problems using ASH \u2013 Part 6","date":"April 20, 2009","format":false,"excerpt":"\"Toto, I've a feeling we're not in Kansas any more.\"What started as a simple write-up of a course demo gone wrong (or right, depending the way you look at these things) has grown arms and legs and staggered away from the original intention to talk about ASH. so I thought\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1484,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/17\/diagnosing-locking-problems-using-ash-part-5\/","url_meta":{"origin":1491,"position":1},"title":"Diagnosing Locking Problems using ASH \u2013 Part 5","date":"April 17, 2009","format":false,"excerpt":"... and so it continues.Coincidentally, the subject of tracking down locking problems after they occurred cropped up on the Oak Table mailing list just after the last post. Several suggestions were offered but I think the nearest and most detailed suggestion was that proposed by Kyle Hailey, ex-Oracle and now\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1495,"url":"http:\/\/orcldoug.com\/blog\/2009\/05\/01\/diagnosing-locking-problems-using-ashlogminer-the-end\/","url_meta":{"origin":1491,"position":2},"title":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 The End","date":"May 1, 2009","format":false,"excerpt":"Except\u00a0it's not the\u00a0end, of course. What I mean is\u00a0that I usually agree with\u00a0what Miladin Modrakovic said in one of his comments on his first deadlock blog post.\"There is always way around.\"As I keep saying, there are many different ways of diagnosing locking problems. Which one works best\u00a0depends on the situation\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1488,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/22\/diagnosing-locking-problems-using-ashlogminer-part-7\/","url_meta":{"origin":1491,"position":3},"title":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 Part 7","date":"April 22, 2009","format":false,"excerpt":"Picking up from the end of the last example, I immediately generated a log file dump as follows. (You'll need to look back at Part 5 to see that the ROWID, log file name etc. match up or just trust me that these steps came from a continuation of the\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1492,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/30\/diagnosing-locking-problems-using-ashlogminer-part-9\/","url_meta":{"origin":1491,"position":4},"title":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 Part 9","date":"April 30, 2009","format":false,"excerpt":"This time, instead of dumping the contents of the log file for a specific Data Block Address (DBA), as in the last part, I\u2019m going to dump it for a specific operation type \u2013 Lock Rows (SELECT FOR UPDATE in this case) \u2013 which is part of the Row Operations\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1480,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/31\/diagnosing-locking-problems-using-ash-part-3\/","url_meta":{"origin":1491,"position":5},"title":"Diagnosing Locking Problems using ASH &#8211; Part 3","date":"March 31, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.In the last part I described a locking scenario where the blocking session had only executed one SQL statement that was quick enough to avoid being sampled by ASH and is now inactive. As a result, there is no ASH data\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1491","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1491"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1491\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1491"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}