I decided to reprise my commentary on Oracle RAC and the gv$ views after reading Patrick’s comments on my previous post. It is always encouraging to know that someone is kind enough to read your work and provide insightful feedback - many thanks to him!

I can use a script now to find the locks in a RAC environment, but until this point I couldn’t have told you how the script actually works. Frankly, the documentation that I found on Metalink is dry and boring for such an important (and sometimes entertaining) subject as locks.

There are two questions that I wanted to answer here: Can you use the gv$ views with a non-RAC environment? What do the WHERE clauses in a good block-checking script do?

First, can you use the gv$ views to check for locks when you have a single-instance, non-RAC database? The reason this question is prevalent in my mind is that we just completed an 11.5.9 application clone (with RAC enabled on the source environment but not on the target) for a customer who has been busy purging data from the new environment. When a performance issue arose, one of the first things that we did was to see if there were any locks. We employed the same script that had been developed to tell us if there were locks on our RAC-enabled instances — and the script returned no records. At the time, I thought that perhaps the gv$ views would not be populated in a non-RAC database. I tested this by executing the following SQL statements on the non-RAC database:

select sid, id1, id2 from v$lock minus select sid, id1, id2 from gv$lock;

select sid, serial# from v$session minus select sid, serial# from gv$session;

(more…)

According to the wikipedia page,

“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”. (more…)