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 11.2.0.4), 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
Oraculix (en) 