Lock Waits: Finding the blocking SQL on Oracle

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:

Diagnosing Locking Problems using ASH - Part 1 - try OEM 10g on Lock Problem
Diagnosing Locking Problems using ASH - Part 2 - look at raw ASH data, missing blocker SQL
Diagnosing Locking Problems using ASH - Part 3 - look at raw ASH data, find the blocker SQL
Diagnosing Locking Problems using ASH – Part 4 - misleading data from ASH
Diagnosing Locking Problems using ASH – Part 5 - logminer undependable
Diagnosing Locking Problems using ASH – Part 6 - overview of all the parts (table of contents)
Diagnosing Locking Problems using ASH/LogMiner – Part 7 - Redo log dump
Diagnosing Locking Problems using ASH/LogMiner – Part 8 - Redo log dump lacks "select for update"
Diagnosing Locking Problems using ASH/LogMiner – Part 9 - Redlo log dump - search for traces of "select for update"

Kyle Hailey

DB Optimizer PGM

http://sites.google.com/site/embtdbo/