Where can I find more info about a specific Oracle lock?

Oracle uses locks or enqueues to serialize access to certain resources. They 
can be seen in v$lock. Every DBA is familiar with common lock types, such as TX 
and TM. Some other types such as CI, CF, JQ, ST are also fairly well-known and 
documented in the v$lock reference manual (go to documentation -> Reference -> 
v$lock).

Unfortunately, not all lock types are listed in v$lock reference page. In 
addition, that page does not give you the meanings of ID1 and ID2 columns of 
the v$lock view for each lock type. Most people know id1 for a TM lock matches 
dba_objects.object_id (not data_object_id, in case they differ). Some DBAs go 
too far and write scripts matching id1 with an object_id without the type='TM' 
predicate. If you have Anjo Kolk's wait event article, you know that's not 
true. (see "Description of Oracle7 Wait Events and Enqueues" at 
http://www.orsweb.com/techpapers/eventsenque.pdf. Search the Internet if the 
given URL is not valid.) Part 3 of the article lists all lock types in Oracle 
7, giving much more details than just the type names. (If you do need to quote 
anything in there when you talk to Oracle, say you get the info from Metalink 
Note:75705.1 instead.)

Oracle Metalink has a note pointing to other articles about specific lock 
types. Look at Locking Reference section of Note:131775.1. Some good articles 
about oracle locks in general are listed too. Note:267332.1 lists 16 locks new 
to 10g as claimed. But take the word "new" with a grain of salt. Thread 
242572.995 has locks new to 9i (specifically, MD,JD,FB,DP,SW,XR). When 
searching for something obscure on Metalink, make sure you use Advanced Search, 
which you can access through this URL
http://metalink.oracle.com/metalink/plsql/kno_main.newAdvancedQuery
and check Bug Database and Archived Articles checkboxes.

When you search on Google, make sure you search on both Web and Groups pages 
(groups.google.com).

If you need in-depth knowledge of Oracle locks, Steve Adams's book "Oracle8i 
Internal Services" is the ultimate reference unless you work for Oracle (even 
then that bumble bee book is still invaluable).

The problem with searching for lock information is that all locks use 
two-letter codes (see sys.x$ksqst.ksqsttyp). Short strings make it difficult to 
search on the Internet. So often times you have to do your own research. Get a 
rough idea of what this lock is about with any available reference. Let's say 
it's CI, somehow named for Cross Instance as if it were for RAC or OPS only. 
According to Anjo Kolk's article, the lock is held for checkpoints, log 
switches... Let's check. In 8i query for x$ksqst.ksqstget where ksqsttyp= 'CI' 
and in 9i v$enqueue_stat.total_req# and succ_req# where eq_typ = 'CI'. These 
queries may tell you whether your recent actions caused enqueue gets to 
increment. My test shows that checkpoints indeed increment the gets but log 
switches do not. If you can find a way to hold the lock for some time, that's 
even better, because then you can look at v$lock instead, and possibly figure 
out what id1 and id2 are. If you can't hold the lock, you may still be able to 
catch it by tracing the session. In your own session, alter session set events 
= '10046 trace name context forever, level 8', or exec 
sys.dbms_system.set_ev(<sid>,<seq#>,10046,8,'') for a victim session. In 9i, 
the generated trace file in udump contains lines like
WAIT #1: nam='enqueue' ela= 910796 p1=1128857606 p2=2 p3=5
That tells us that the approximately 1 second wait is on an enqueue of type CI. 
I know it's CI because p1 is 0x43490006 in hex and 0x43 0x49 are ASCII codes 
for C and I. The ending 6 means it's waiting for a CI lock held in exclusive 
mode. ID1 for the lock is 2 and ID2 is 5. According to Anjo's paper, ID1=2 
means "DBWR synchronization of SGA with control file", which makes perfect 
sense in checkpoint, ID2=5 means "Used to queue up interested clients".

Unfortunately not all lock gets cause waits and therefore are not exposed as 
wait events. For instance, I don't know how to artificially induce a CF 
(controlfile) enqueue wait even though the lock is very frequently held even on 
a quiet database (just keep running select * from v$enqueue_stat where eq_type 
= 'CF'). If you see such CF in v$lock, you have a precious opportunity for 
research. Maybe dump the process state just for a record and analyze later, and 
possibly a hanganalyze trace, which is a simplified systemstate dump. [Note: 
Update for 10g]

Yong Huang
yong321@yahoo.com
http://yong321.freeshell.org/computer.html
(This note is republished at http://www.orafaq.net/kb/link.php?id=514)

[Appendix A] My other postings about some lock types
PS lock:
http://groups.google.com/group/comp.databases.oracle.server/msg/815059bc69c02cf3
TC lock:
http://groups.google.com/group/comp.databases.oracle.server/msg/befe4225f21658ab
TO lock: ./TOLocks.txt
SEE ALSO:
./LocksExperiment.txt

[Appendix B] New in 10g
In 10g, you can find all id1 and id2 parameter descriptions with this simple 
query:
col name for a45
col parameter2 for a25
col parameter3 for a25
select name, parameter2, parameter3
from v$event_name
where name like 'enq: %'
order by name;
or
col TYPE for a2
col NAME for a32
col ID1_TAG for a21
col ID2_TAG for a22
col IS_USER for a3
col DESCRIPTION for a130
select * from v$lock_type order by 1;
In addition, if name doesn't end with "contention", it also tells you what the
lock is about. What a great improvement! In fact, even if you don't have 10g,
ask your friend that runs 10g to give you ouput of that query for your 
reference.

[Note] Update for 10g
Event 10704 is "Print out information about what enqueues are being obtained". 
But before 10g, you won't get any trace if you run, for instance,
exec sys.dbms_system.set_ev(4,1,10704,10,'')
to set the event at level 10 in session 4, serial# 1 (usually CKPT). In 10g, 
doing this to CKPT (sid 396 here) generates a trace file in bdump:

*** 2006-01-27 22:40:14.468
*** SERVICE NAME:(SYS$BACKGROUND) 2006-01-27 22:40:14.453
*** SESSION ID:(396.1) 2006-01-27 22:40:14.453
ksqgtl: CF-00000000-00000003 mode=6 flags=0x110 timeout=21474836
ksqgtl: returns 0
ksqrcl: CF,0,3
ksqrcl: returns 0

and the above lines repeat about 3 seconds apart. So CKPT acquires these CF 
locks. Doing the same to all background processes, you'll find only CKPT 
acquires CF locks (SMON acquires a TA lock, and CJQ0 acquires a bunch of JS 
locks once every 3 seconds.)