Oracle RAC and gv$ Views: A Second Look

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;

The first statement generally returns no records, but occasionally will (I suspect this may be due to the speed with which locks are created and released). But I believe I have enough evidence to determine that the v$ and gv$ views are showing the same data in a single-instance database. One thing that I don’t know is if the gv$ views are available in a database that has never been RAC-enabled. The database we are using was RAC-enabled before cloning and was specified as a single instance database during the cloning process.

My conclusion from this test is that our cloned database can also utilize the gv$ views to check for blocking sessions. This is great news as it doesn’t require building and testing another production-quality script. I’m all for reusing what you have and keeping consistency where possible.

Now to the second part, what do the WHERE clauses in a block-checking script actually do? Someone with more experience could probably wax poetic on the various meanings of the columns, but that’s just not me. Let’s use the same approach that most of us took when we wanted to learn how computer hardware works: disassemble it and start understanding the pieces. Fortunately, I have a very good script to start with for the purposes of analyzing (thanks to many DBAs who have blazed the trail before us) — so will you if you stay tuned.

The first thing that you must do to find blocking sessions is identify where you have a holder and a waiter. These are shown as two separate lines in the v$lock view but they share id1 and id2 columns values. Using the INTERSECT command is an elegant way to accomplish this and improves the script by giving you only the lines in the gv$lock view that you are specifically interested in:

(gvh.id1, gvh.id2) in (SELECT id1, id2 FROM gv$lock WHERE request=0

                            INTERSECT

                       SELECT id1, id2 FROM gv$lock WHERE lmode=0)

Holders can be identified where the request is 0, as the request column specifies the type of wait request — for a holder this column has no meaning. Waiters, likewise, can be seen as having an lmode of 0 as that is only a valid value for a holder. Using the INTERSECT command specifies that the only lines to be returned are those where you have both a holder and a waiter. Very cool stuff, see?

Next, we will want information in our SELECT statement on the holders in addition to the waiters in a single record, so we need to reference the gv$lock view twice in the FROM clause — once aliased as gvh and again as gvw. Once we have that, we need to join the two gv$views together in the WHERE clause as such:

AND gvh.id1=gvw.id1

AND gvh.id2=gvw.id2

Now you might look at those previous statements and ask, “but won’t you be joining holders to holders, holders to waiters, and waiters to waiters?” And you would be correct — two additional WHERE clauses are needed to pare down the data so that you only have holders joined to waiters:

AND gvh.request=0

AND gvw.lmode=0

This specifies that the only records we are interested in from a holder standpoint are those where the request column is 0 because, as stated earlier, this column will always show a 0 for a holder because it is irrelevant in this context. And thus, the same rationale for the waiter with the lmode column. We now have the a solid foundation for a very good query on blocking locks. Throw in a reference to the gv$session view in the WHERE clause, join it to the gv$lock holder view based on sid and inst_id, and you have the makings of a very useful script for diagnosing and resolving blocking locks.

You didn’t think I was going to build and hand you the script now, did you? What’s the fun in that!

TIP: One of our senior DBAs added an interesting twist to the basic script — he added a line into the SELECT statement that would build the alter system kill session statement so that we could cut and paste it if we wanted to get rid of a particular blocking session.

I hope you have as much fun reading this as I did in writing it. OK, OK – thanks to Patrick, here is what the completed script looks like – enjoy!

set pagesize 200
set linesize 150
column module format a35
SELECT gvh.inst_id Locking_Inst, gvh.sid Locking_Sid, gvs.serial# Locking_Serial,
       gvs.status Status, gvs.module Module, gvw.inst_id Waiting_Inst, gvw.sid Waiter_Sid,
       decode(gvh.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-') Waiter_Lock_Type,
       decode(gvw.request, 0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-') Waiter_Mode_Req ,
       'alter system kill session '|| '''' || gvh.sid || ',' || gvs.serial# || ''';' "Kill_Command"
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) in (
           SELECT id1, id2 FROM gv$lock WHERE request=0
                INTERSECT
           SELECT id1, id2 FROM gv$lock WHERE lmode=0)
  AND gvh.id1=gvw.id1
  AND gvh.id2=gvw.id2
  AND gvh.request=0
  AND gvw.lmode=0
  AND gvh.sid=gvs.sid
  AND gvh.inst_id=gvs.inst_id;

Please note that neither I, nor my colleagues, take any credit for development of the script above – we have only modified it to suit our particular needs, as I hope that you are encouraged to do as well.