Tracing Library Cache Locks 2: Using Debugger

This blog post was prompted by a Twitter exchange about an exclusive library cache lock that was allocated when altering an index.

The question arose: why was the library cache lock allocated for the table instead of the index?

I’ll describe the methodology for answering this question without speculating. I’ll be using debugger to get the names of the involved Oracle C functions and the content of CPU registers, but you can follow the same steps for your particular case, even if you aren’t familiar with Oracle internals.

Using debugger on a running database is hazardous, so this procedure is intended for lab environments only.

My model is simple – just a table and an index:

create table t (n1 integer,n2 integer);
create index ix_t on t(n1,n2);
exec dbms_stats.gather_table_stats (null, 'T', cascade => true ) ;

In the debugger, setup the following breakpoint:

break kgllkal if $rcx==3
commands 1
backtrace
p/z $rdx
finish
end

The breakpoint is conditional. The execution stops on entering the kgllkal function, but only when the library cache lock is requested in exclusive mode. The mode is passed by rcx CPU register. “3” is the value for exclusive locks. By the way, the lock mode is externalized in x$kgllk.kgllkmod.

One important information is the stack trace, because it provides the context. The other is the content of the rdx CPU register, which contains the lock handle address. This address is externalized in x$kgllk.kgllkhdl.

finish is executed immediately after extracting needed information to avoid holding library cache mutex longer than necessary.

Attach the debugger to the process which will execute the command you’d like to inspect. In my case, it was:

alter index ix_t invisible ;

Find the captured output below:

Thread 2 hit Breakpoint 1, 0x00000000068fb6a0 in kgllkal ()
#0  0x00000000068fb6a0 in kgllkal ()
#1  0x00000000068dfa9e in kglLock ()
#2  0x00000000069dc43c in kglget ()
#3  0x0000000006963e96 in kkdllk0 ()
#4  0x000000000f6dd4a8 in kkdllppac0 ()
#5  0x000000000dcdf394 in aindrv ()
#6  0x0000000006040b9a in opiexe ()
#7  0x00000000065d5922 in opiosq0 ()
#8  0x0000000006769625 in kpooprx ()
#9  0x0000000006692004 in kpoal8 ()
#10 0x000000000602d6c9 in opiodr ()
#11 0x0000000006037303 in ttcpip ()
#12 0x0000000006032cfc in opitsk ()
#13 0x0000000006031818 in opiino ()
#14 0x000000000602d6c9 in opiodr ()
#15 0x000000000602cc8f in opidrv ()
#16 0x000000000602c8c7 in sou2o ()
#17 0x000000000602c711 in opimai_real ()
#18 0x000000000602c1eb in ssthrdmain ()
#19 0x000000000602bf59 in main ()
$1 = 0x00000000a8fd7370)
0x00000000068dfa9e in kglLock ()

Now, feed the rdx CPU register value into the query to find out the locked object:

select kgllkmod,kglnaobj from x$kgllk 
  where kgllkhdl = upper('00000000a8fd7370') ;

  KGLLKMOD KGLNAOBJ
---------- ------------------------------------------------------------
         3 T

I always use Frits Hoogland’s orafun for interpreting the meaning of the functions in the stack:

#0 0x00000000068fb6a0 in kgllkal ()                kernel generic library cache management library cache lock allocate 
#1 0x00000000068dfa9e in kglLock ()                kernel generic library cache management library cache lock 
#2 0x00000000069dc43c in kglget ()                 kernel generic library cache management get a lock on an object 
#3 0x0000000006963e96 in kkdllk0 ()                kernel compile dictionary lookup lock an object  [partial hit for: kkdllk ] 
#4 0x000000000f6dd4a8 in kkdllppac0 ()             kernel compile dictionary lookup  [partial hit for: kkdl ] 
#5 0x000000000dcdf394 in aindrv ()                 alter index  [partial hit for: ain ] 
#6 0x0000000006040b9a in opiexe ()                 oracle program interface execute 
#7 0x00000000065d5922 in opiosq0 ()                oracle program interface prepare to parse a sql command 0 
#8 0x0000000006769625 in kpooprx ()                kernel programmatic interface oracle open, parse, and optionally execute 
#9 0x0000000006692004 in kpoal8 ()                 kernel programmatic interface oracle V8 bundled execution 
...

We can conclude that the exclusive library cache lock was requested on behalf of alter index to protect the dictionary lookup.

In summary, the reasons for library cache lock allocations aren’t instrumented, but we can find them out by inspecting the inputs of Oracle C functions and combining this information with externalized in-memory C program structures.