Oracle RAC, v$, and gv$

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$lock, and gv$session as well.

Let’s tweak the earlier lock script to incorporate a couple of items – the gv$ views and the inst_id columns:

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;

Results: APPS@test.server.com ( INST=1 SID=194 ) is blocking APPS@test.server.com ( 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$lock and 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.

Cheers!