When you have to troubleshoot blocking locks in your Oracle DB, you might be lucky enough to have a GUI tool handy that helps you with that (e.g. Enterprise Manager or TOAD). Otherwise you’ll likely have to resort to Oracle’s pre-installed script “utllockt.sql”. Unfortunately, this script only shows locks in the DB instance it’s running on. However, locks chains may span multiple instances in a clustered environment.
In this brief post I’d like to propose a RAC-aware alternative to “utllockt.sql” using only SQL so it can be run in any Oracle client.
Picture: Mike Gabelmann / flickr / CC-BY-NC-SA
utllockt for RAC (and Single-Instance)
The original SQL script hasn’t been updated in quite a while, aside from minor bug fixes. It has never been updated to use cluster-wide data dictionary (gv$) views and creates tables to temporarily store intermediate results.
Thanks to subquery factoring (aka “WITH-Clause”) those tables can be modeled as table expressions. This has the added benefit that the account to run this statement doesn’t need the “CREATE TABLE”privilege. Furthermore, this is not a SQL*Plus-sepcific script anymore, so it can be run in any client.
with BLOCKERS as( select inst_id waiting_instance , sid waiting_session , blocking_instance , blocking_session from gv$session where blocking_instance is not null and blocking_session is not null ) , LOCK_TREE as( select * from BLOCKERS union all select blocking_instance, blocking_session, null, null from BLOCKERS minus select waiting_instance, waiting_session, null, null from BLOCKERS ) select lpad(' ', 2*(level-1)) || l.waiting_session wsid , l.waiting_instance winst , s.status , substr( q.sql_text, 1, 30 ) sql_text , coalesce( s.sql_id, s.prev_sql_id ) sql_id , s.osuser , s.username , substr(s.program,1,20) program , substr(s.module,1,20) module , s.action from LOCK_TREE l , gv$session s , gv$sql q where s.sid = l.waiting_session and s.inst_id = l.waiting_instance and q.sql_id (+) = coalesce( s.sql_id, s.prev_sql_id ) and q.inst_id (+) = s.inst_id connect by prior l.waiting_session = l.blocking_session and prior l.waiting_instance = l.blocking_instance start with l.blocking_session is null;
Voilá! Have fun troubleshooting! :-)
If you’d like a nicely formatted version for SQL*Plus or sqlcl, download it from GitHub.
In case you wonder why I mixed ANSI and Oracle syntax: I intentionally preferred “connect by” over a recursive WITH-clause. In my tests (Oracle 220.127.116.11), this produced much better execution plans, and I like my administrative queries to have a small footprint.
Bonus: “Kill Switch”
A little extra for those of you using GUI tools rather than the command line: here is an additional column that creates an “ALTER SYSTEM KILL …” statement to cut and paste:
, 'ALTER SYSTEM KILL SESSION ' || '''' || s.SID || ', ' || s.serial# || ', @' || s.inst_id ||'''' || ' IMMEDIATE;' kill_session_stmt