Finding the blocking SQL in a lock wait

One of my pet peeves on Oracle is the inability to find out what SQL took out a lock that another user is waiting. It’s easy to find the waiting user and their SQL with v$session by looking at v$session.event where the event is an “enqueue” (v8 and v9) or “enq: TX – row lock contention” and then looking up their SQL via the v$session.sql_hash_value which joins to v$sql.hash_value for the v$sql.sql_text.

So far so good and easy.

Second step of finding the blocker is really easy starting in 10g because Oracle has a new field v$session.blocking_session which can be joined back to v$session.sid to find information on that user.

The rub is that there is no way to find the SQL text that the blocking session ran that took out the original blocking lock.

For the 2 day course I teach on Active Session History (ASH) and Oracle wait events, I wanted to show students how to actually get the blocking SQL text if they really had to.
I went as far as looking at log miner to try and get the blocking SQL text and this works sometimes and sometimes it doesn’t. At that point I gave up, knowing the next step was dumping the redo logs which was more research than I felt like doing at the time.Luckily someone has picked up the torch – Doug Burns!
On the Oaktable email list I shared my research with Doug and Doug took it even farther and posted it on his blog:

Long story short, the best way to try and see what changed (when there was a change and not a “select for update”) to cause the lock is to use flashback information. For example if or contention table was TEST_TAB and our field that we knew was modified “VAL1″ then we could try to find what it was changed from:

Now that’s not the blocking SQL but at least you can see what the value of the field was that the blocker changed it to, so you can guess to some degree what the actual SQL was. Not great, but better than nothing.

I have no problem with your approach – and if your initial response to Kyle’s post had been more like: “I don’t bother looking for the specific SQL I just kill the session so all I need is dba_waiters” then I wouldn’t have felt the need to comment.

Actually, I do get the sql via OEM. The “Blocking sessions” screen in my case lets me track it. Mostly because in the particular case of the application in question, it was designed and written in java by someone who doesn’t understand set-at-a-time processing. With the result that 999 times out of 1000 the SQL that causes the lock is the same as the one waiting andwhich is shown by OEM: same jdbc program, different connections.
Had this been our DW where most of the stuff is written in-house in a real language (not java), it’d be slightly more difficult as you point out.
Good thing though that the guys writing our DW PL/SQL know what they are doing! And if they don’t, I can just stand-up and look at them with a stern face!

Thanks for the follow-up – it emphasises, but makes more concrete, my comment from earlier on in the thread:
“It’s only easy if you’re lucky and the application makes it obvious.”

(Possibly the “and” should be an “or”, or even an “and/or”; and I suspect that many people get lucky enough most of the time – possibly without realising it – because it is often sufficient to be just a little bit lucky.)

Absolutely. However, what I wish should happen is Oracle should pull their heads off the “aaS” marketing nonsense – which achieves noting for current customers – and actually deliver features to stop users having to rely on luck so many times.

Using this example (I’m likely using the wrong terminology, but this springs to mind): an inbuilt, reliable mechanism to display which SQL is taking part in a lock, both locking and locked side?

Seems to me this would be eminently more useful and sensible than expecting folks to pour through 3rd party blogs to find scripts to do what should be inbuilt into the product in the first place. And less “bad dba tut-tuting” when they find that the vast majority of said “bad dbas” are actually VERY busy and are not paid to browse blogs, forums, or “communities”.

The list of small enhancements like this that would truly be useful and which Oracle has totally ignored over many, many years is incredibly long. I mentioned some of them in my blog many years ago and to my surprise a few have been answered in recent releases. But they are a very small portion of the sum total I know of.

Unfortunately instead of truly useful improvements we keep getting even more of the “aaS” nonsense. And now there is even religion thrown in…

But that’s got nothing to do with this!
Thanks heaps for the (as usual) immensely useful script pointers.

@Noons, @Jonathan : thanks for all the lively discussion. It’s frustrating all around that technology advances that make sense at Oracle don’t get implemented. On the other hand Oracle is far ahead of every other database in the realm of diagnostics. Oracle’s wait events started way before other databases and are still much richer than any other database. I don’t know of any other database that has active session history (ASH) despite the implementation of ASH being quite feasible on say SQL Server, Sybase and DB2. We actually implemented it ourselves in DB Optimizer for all 4 databases.
The market is ultimately revenue driven and thus improving the analysis of small performance issues for a DBAs is pretty far down the totem pole. It’s much more effective for all to push for changes that have huge impact on the results for a company and despite my interest in tactical tuning issues, that’s why I’ve instead concentrated these past few years on how to improve IT performance at the enterprise level.

I agree with the general principle, but in practice a closer look at the problem often shows that it is much harder that it first seems.

In this case you would need to store the sql_id and rowid of every row modified – do you do that in memory, or write the SQL_ID to the row in the data block ? (You can’t write it to the ITL because a single transaction may use different statements to modify different rows in the same block.) Then what do you do if a transaction uses 3 different statements to update the same row – bet Noons has seen ETL programs that do that – do you keep the first, the last, or all three SQL_IDs ?

I agree with the general principle, but in practice a closer look at the problem often shows that it is much harder that it first seems.

In this case you would need to store the sql_id and rowid of every row modified – do you do that in memory, or write the SQL_ID to the row in the data block ? (You can’t write it to the ITL because a single transaction may use different statements to modify different rows in the same block.) Then what do you do if a transaction uses 3 different statements to update the same row – bet Noons has seen ETL programs that do that – do you keep the first, the last, or all three SQL_IDs ?