Proposing a SQL-only Diagnosis for Blocking Locks in Single Instance and RAC

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.

Locked?

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

References

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s