LIBRARY CACHE LOCK AND PIN DEMONSTRATED

In this post, I will demonstrate library cache locks and pins

.– Start 4 SYS sessions and one HR session

 

–– Flush buffer cache

SYS1>alter system flush shared_pool;

– Check that there are no objects belonging to HR or whose name is like employees
which are in library cache presently
Only HR user is there

SYS1>select kglnaown, kglnaobj from x$kglob
 where kglnaown='HR'
 or kglnaobj like '%employees%'
 and kglnaobj not like '%kgl%'
 /

KGLNAOWN                                                         KGLNAOBJ
—————————————————————- ————————-
HR                                                               HR

– create a procedure tst_employees –

HR1>create or replace procedure tst_employees as
 begin
 for i in 1..100 loop
 dbms_lock.sleep(5);
 update employees set salary=salary;
 end loop;
 end;
 /

– Check that following objects belonging to HR or whose name is like employees   are loaded in  library cache
– HR (user)
– TST_EMPLOYEES procedure
– EMPLOYEES table (accessed in procedure)

SYS1>select kglnaown, kglnaobj from x$kglob
 where kglnaown='HR'
 or kglnaobj like '%employees%'
 and kglnaobj not like '%kgl%'
 /

KGLNAOWN                                                         KGLNAOBJ
—————————————————————- ————————-
HR                                                               TST_EMPLOYEES
BEGIN tst_employees; END;
BEGIN tst_employees; END;
HR                                                               HR
HR                                                               EMPLOYEES

– Check for locks on objects belonging to HR or whose name is like employees
   Only HR (user ) is there since the user’s session is open

SYS2>select  l.kglnaobj
 from x$kgllk l, x$kglob o
 where l.kgllkhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

KGLNAOBJ
——————————
HR

– Logout from HR session –

HR>Exit

– Check that lock on HR is also gone

SYS2>select  l.kglnaobj
 from x$kgllk l, x$kglob o
 where l.kgllkhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

– Login as HR user again –

$sqlplus hr/hr

~– Check for pins on objects belonging to HR or whose name is like employees
Note that there are no pins

SYS3>select  o.kglnaobj
 from x$kglpn l, x$kglob o
 where l.kglpnhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

– Now , I will demonstrate that pin is obtained while a procedure is being executed

 
– Eexcute the procedure tst_employees

HR>exec tst_employees;

– Check for locks on objects belonging to HR or whose name is like employees

SYS2>select  l.kglnaobj
 from x$kgllk l, x$kglob o
 where l.kgllkhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

KGLNAOBJ
——————————
TST_EMPLOYEES
BEGIN tst_employees; END;
BEGIN tst_employees; END;
HR

– Check for pins on objects belonging to HR or whose name is like employees     repeatedly.Note that procedure tst_employees is pinned as it is being executed

SYS3>select  o.kglnaobj
 from x$kglpn l, x$kglob o
 where l.kglpnhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

KGLNAOBJ
——————————————————————————–
TST_EMPLOYEES

– Flush the shared pool while the procedure is still executing

SYS4>alter system flush shared_pool;

– Check that pinned objects have not been flushed out

SYS1>select kglnaown, kglnaobj from x$kglob
 where kglnaown='HR'
 or kglnaobj like '%employees%'
 and kglnaobj not like '%kgl%'
 /

KGLNAOWN                                                         KGLNAOBJ
—————————————————————- ————————-
HR                                                               TST_EMPLOYEES
BEGIN tst_employees; END;
BEGIN tst_employees; END;
HR                                                               HR
HR                                                               EMPLOYEES

– Wait till the execution of the procedure is over or abort it–

 
~– Check for pins on objects belonging to HR or whose name is like employees
    Note that there are no pins as soon as the procedure stops executing.

SYS3>select  o.kglnaobj
 from x$kglpn l, x$kglob o
 where l.kglpnhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

no rows selected

– Check for locks on objects belonging to HR or whose name is like employees    after the executionof the procedure is over
   Note that lock(Parse lock) is still there

SYS2>select  l.kglnaobj
 from x$kgllk l, x$kglob o
 where l.kgllkhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

KGLNAOBJ
——————————
TST_EMPLOYEES
BEGIN tst_employees; END;
BEGIN tst_employees; END;
HR

– Recompile the procedure –

HR>alter procedure tst_employees compile;

–    Note that lock is not  there (parse lock broken)

SYS2>select  l.kglnaobj
 from x$kgllk l, x$kglob o
 where l.kgllkhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

KGLNAOBJ
——————————
HR
alter procedure tst_employees
compile
Hence,
– A procedure is pinned while it is executing
– Parse lock remains even after execution is over
– Parse lock is broken when the procedure is recompiled.

– Now I will demonstrate that lib cache pin is also obtained when u gather statististics     for an object

– Gather statistics for employees table in a loop

 HR>begin
 for i in 1..1000 loop
 execute immediate 'analyze table hr.employees compute statistics';
 end loop;
 end;
 /

- Check that  pins are obtained on employees table while the statistics are being gathered in the HR session
   As soon the statistics gathering is over, pins are also gone

SYS3>select  'Lib cache pin' Type, o.kglnaobj
 from x$kglpn l, x$kglob o
 where l.kglpnhdl=o.kglhdadr
 and (o.kglnaown='HR' or o.kglnaobj like '%employees%')
 and o.kglnaobj not like '%kgl%'
 /

TYPE          KGLNAOBJ
————- ——————————
Lib cache pin EMPLOYEES

– cleanup –

HR>drop procedure tst_employees;

Conclusion:
– The lock on a procedure is obtaned when it is executed and remains there even after
the execution is over
– The locks obtained on a procedure in library cache (parse locks) are broken when the
procedure is recompiled
– Pins in library cache are obtained when a procedure is executing
– Objects being executed are not flushed out even after flushing the shared pool
– Pins are obtained on a table while its statistics are being gathered.
References:
https://rajat1205sharma.wordpress.com/2015/03/31/library-cache-wait-events-in-rac/