According to wikipedia,
“The rack is a medieval torture . . . which induces excruciating pain as the victim’s joints slowly dislocate.”
Per the Oracle website,
“Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for all your business applications.”
Which is more painful, you might ask? I cannot say for certain, as I have never been subjected to the torture of a medieval rack, but I have experienced some pain at the hands of the Oracle RAC. My first encounter was about five months ago when I first became an “official” DBA. Being eager to jump into solving problems in my new job (as that’s what most DBAs do, solve problems), I relished the chance to get my hands dirty and work on a “real” DBA task — a database lock.
Even though I had never been officially titled a DBA before, I was somewhat familiar with the concepts as I have been working around them for years (and still chose to join their ranks, if that tells you anything). Theoretically, I knew exactly what a database lock was, but I had no clue how to practically diagnose or kill one off.
Checking with a few knowledgeable co-workers, I was directed to a set of common database diagnostic scripts affectionately known as the “Pythian Kit”. I unzipped the contents and voila! — there was a script for checking for locks. I plopped the SQL into Golden SQL, clicked the lightning bolt and waited nearly 20 minutes for it to tell me there were no blocking locks. The error message that the user had provided in the email quite clearly referenced a locking situation, so I was a bit stumped, on two accounts: where is the lock; and why did the lock query take so long to run?
Here is a sample lock script I pulled from the web and tested the situation with:
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
Results: No Records Found
This wasn’t the confidence-boosting exercise that I had anticipated. Instead, I got what most people would call a character-building experience. The key, as I was later to learn, is that RAC is not like your typical single-instance database where all locks can be detected by looking at the
v$lock view – with the introduction of multiple instances for a single database you need to use a new view called
gv$session as well.
Let’s tweak the earlier lock script to incorporate a couple of items – the
gv$ views and the
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
[email protected] ( INST=1 SID=194 ) is blocking
[email protected] ( INST=2 SID=228 )
Elvis has left the building!
Granted this modified lock script is a bit simplistic and unrefined, but it serves well to define my point and highlight the power of the
gv$ views in a RAC-enabled environment, as well as to show the limitations of the
v$session views. The original locking script will only show you locks where both the blocker and blocked sessions are on the instance that you are currently logged into. This is very important if you have load-balancing or failover involved.
The other bonus is that the modified script ran in less than four seconds, where the original one ran in 263 seconds.
Thanks for posting this, which I have already found useful. I did notice some issues with it and have posted a revised version below.
Firstly your SQL only joined the instance ids between the gv$lock and gv$session views for the blocking session (l1.inst_id = s1.inst_id). This is also needed for the blocked session (l2.inst_id = s2.inst_id), otherwise it may show too many sessions as blocked.
l2.id2 = l2.id2, will always evaluate to true and should be l1.id2 = l2.id2.
On my RAC database I found that the “block” column held a 2 for the blocking session, not a 0 or 1 as stated in the documentation. I therefore needed to replace l1.BLOCK1=1 with l1.BLOCK1 > 0.
select distinct s1.username || ‘@’ || s1.machine
|| ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘@’ || s2.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s2.sid || ‘ ) ‘
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2;
Thanks for the feedback Patrick – you are correct with your suggestions. I was going with simplicity as much as possible to illustrate the difference between the types of views – I should have remembered that my target audience are sticklers for details (keeps me on my toes!). I’m very glad you made the comments to clarify.
I would have posted our internal script for reference as it is a fantastic one, but I didn’t write it and felt uncomfortable posting someone else’s intellectual property. The great thing about the gv$ views is that they can be your best friend and your worst enemy.
Glad that this has helped – that makes my day!
I’m having lots of problems with locks en a rac enviroment.
You mencioned your internal script and i agree with you about posting it.
But can you please send it to me directly?
[Email address removed. -Ed.]
Thanks in advance.
I too would like to know that query, if it is ok for you to send me on my email id.
Thanks in advace.
Thanks Patrick . Superb catch.