> What's the difference between "library cache lock" and "library cache pin"? The difference is that a library cache lock is used to locate (find) a library cache object. A pin is taken only when the session actively executes something, and that "something" (SQL statement and referenced objects in library cache) is pinned during that period. You can watch this by running dbms_lock.sleep() and check x$kglpn (where kglpnuse matches your session saddr in v$session). When your session finishes running it, x$kglpn no longer has the entries for DBMS_LOCK and "BEGIN dbms_lock.sleep(100); END;". But x$kgllk still has the SQLs (some are recursive) and packages the session used earlier, because they're held (locked) in null mode with the so-called breakable parse locks. (The actual object names in x$kglpn can be seen by matching kglpnhdl with x$kgllk.kgllkhdl or equivalently x$kglob.kglhdadr.) http://www.itpub.net/thread-1009501-1-1.html > Are "library cache lock" and "library cache pin" latches or wait events? The question can be looked at this way: What do the latches "library cache pin" and "library cache lock" do and what's the difference between "latch free" wait event where the latches are these two types and the "library cache pin" or "library cache lock" waits themselves? Do the numbers v$latch.gets for library cache lock/pin or the numbers v$system_event.total_waits for them tell us the actual number of library cache locks/pins we've had in the database since instance startup? If you check v$latch for these two latches and compare with the same name wait events in v$system_event, you'll see the two latches are get'ed much more frequently. I think the numbers for the two latches in v$latch accurately reflect the actual pins/locks we've had. There's no statistic in v$sysstat or v$sesstat for latch gets, probably because v$latch.gets serves the purpose already. If the database is not too busy causing waits on them, the numbers in v$system_event (or other wait related views) will not increment. In fact, v$latch.sleeps should be equal to these latches' "latch free" waits because latch waits are the same as latch sleeps. You can test on a quiet single-user database, with no jobs or scheduler jobs, and very quickly type select name, gets, misses, sleeps, immediate_gets from v$latch where name in ('library cache pin', 'library cache lock'). You'll see the pin gets keep going up (by 2 in my 10.2.0.1 database) while lock gets stay the same. The 2 pins must be due to this SQL itself. Values in v$system_event stay the same. Yong Huang Old writing. Need review, or delete: CREATE OR REPLACE package body MYMIS_CUST_DRILL_DOWN hangs. The session waits for library cache pin, handle address 884EC49C, pin address 94934FE4 SQL> select * from x$kglob where kglhdadr = '884EC49C'; ADDR INDX INST_ID KGLHDADR KGLHDPAR KGLNAOWN -------- ---------- ---------- -------- -------- ---------------------------------------------------------------- KGLNAOBJ -------------------------------------------------------------------------------------------------------------------------------------------------------------------- KGLNADLK KGLNAHSH KGLNATIM KGLNAPTM KGLHDNSP KGLHDLMD KGLHDPMD KGLHDFLG KGLHDOBJ KGLHDLDC ---------------------------------------------------------------- ---------- --------- --------- ---------- ---------- ---------- ---------- -------- ---------- KGLHDIVC KGLHDEXC KGLHDLKC KGLHDKMK KGLHDDMK KGLHDAMK KGLOBFLG KGLOBSTA KGLOBTYP KGLOBHS0 KGLOBHS1 KGLOBHS2 KGLOBHS3 KGLOBHS4 KGLOBHS5 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KGLOBHS6 KGLOBHS7 KGLOBHD0 KGLOBHD1 KGLOBHD2 KGLOBHD3 KGLOBHD4 KGLOBHD5 KGLOBHD6 KGLOBHD7 KGLOBPC0 KGLOBPC6 KGLOBTP0 KGLOBT00 KGLOBT01 KGLOBT02 ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- ---------- ---------- -------- ---------- ---------- ---------- KGLOBT08 KGLOBT09 KGLOBT10 KGLOBT11 KGLOBT12 KGLOBT13 KGLOBT14 KGLOBT15 KGLOBT16 KGLOBT17 KGLOBT18 KGLOBT19 KGLOBT20 KGLOBT21 KGLOBT22 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KGLOBT23 KGLOBT24 KGLOBT25 KGLOBT26 KGLOBT27 KGLOBT32 KGLOBTL0 KGLOBTL1 KGLOBTS0 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- KGLOBTS1 KGLOBTN0 KGLOBTN1 KGLOBTN2 KGLOBTN3 KGLOBTN4 KGLOBTN5 ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- KGLOBTS2 ---------------------------------------------------------------- 01A4CB28 18006 1 884EC49C 884EC49C WAREHOUSE MYMIS_CUST_DRILL_DOWN 3693881814 22-JUL-02 22-JUL-02 2 1 2 33554432 88531648 52 0 97 1 0 25 0 5 1 11 3267 0 0 0 59048 0 0 0 8850B70C 00 00 00 884E8AF0 00 00 00 1 0 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 select * from dba_objects where object_name = 'MYMIS_CUST_DRILL_DOWN'; says the package and package body are valid. select * from v$sql where upper(sql_text) like '%MYMIS_CUST_DRILL_DOWN%' and users_executing > 0; returns a SQL that has 1 user executing, for which the address is 83DFE0A8 SQL> select * from v$open_cursor where address = '83DFE0A8'; SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------ 9419A924 57 PORTAL30_PUBLIC 83DFE0A8 2018393744 declare rc__ number; begin owa.init_cgi_env(:n__,:nm__,:v_ 9418CE2C 31 PORTAL30_PUBLIC 83DFE0A8 2018393744 declare rc__ number; begin owa.init_cgi_env(:n__,:nm__,:v_ After these 2 sessions are killed, everything is fine! This session hangs: SQL> alter PACKAGE WWSBR_SECURITY compile body; Package body altered. From another session: SVRMGR> select * from v$session_wait where sid = 124; SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN STATE ---------- ---------- ---------------------------------------------------------- ------ ---------------------------------------------------------------- -------- -- -------- ---------------------------------------------------------------- --- ------- -------- --------------------------------------------------------------- - ---------- -------- ---------- ---------- ------------------- 124 474 library cache lock handle address 34017339 36 CAC25730 lock address 348 9988668 D005003C 10*mode+namespace 32 00000020 0 142 WAITING 1 row selected. SVRMGR> select * from dba_kgllock where kgllkhdl = 'CAC25730'; KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL -------- -------- ---------- ---------- ---- D0C716B8 CAC25730 0 3 Lock D0CB7F40 CAC25730 0 3 Lock D0CBD378 CAC25730 0 3 Lock ... D0BF4250 CAC25730 0 3 Lock D0C53F18 CAC25730 1 0 Lock D0C0E768 CAC25730 1 0 Lock D0BEF684 CAC25730 1 0 Lock D0C205C0 CAC25730 3 0 Lock D0C53F18 CAC25730 0 2 Pin D0C205C0 CAC25730 0 3 Pin D0BEF684 CAC25730 2 0 Pin D0C0E768 CAC25730 2 0 Pin 182 rows selected. SVRMGR> select * from v$session where saddr = 'D0C205C0'; SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHI NE TERMINAL PROGRAM TYPE SQL_ADDR SQL_HASH_V PREV_SQL PREV_HASH_ MODULE MODULE_HAS ACTION ACTION_HAS CLIENT_INFO FIXED_TABL ROW_WAIT_O ROW_WAIT_F ROW_WAI T_B ROW_WAIT_R LOGON_TIM LAST_CALL_ PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CO NSUMER_GROUP PDML_STA PDDL_STA PQ_STATU -------- ---------- ---------- ---------- -------- ---------- ------------------ ------------ ---------- ---------- -------- -------- -------- --------- -------- -- ------------------------------ ------------------------------ --------- ----- ----------------------------------------------------------- -------------------- ---------- ------------------------------------------------ ---------- -------- ---------- -------- ---------- ------------------------------------------------ ---------- -------------------------------- ---------- ------------------------- --------------------------------------- ---------- ---------- ---------- ------- --- ---------- --------- ---------- --- ------------- ---------- --- ----------- --------------------- -------- -------- -------- D0C205C0 116 3259 99465 D0B243E4 40 PORTAL30_PUBLIC 0 2147483644 CD38EBAC ACTIVE DEDICATED 40 PORTAL30_PUBLIC oracle 17226 eris httpd AT eris (TNS V1-V3) USER CA26D7E4 3109932529 CBCD475C 2182723903 0 0 2481 -1 0 0 0 25-MAY-01 6312 NO NONE NONE NO DEFAULT_CON SUMER_GROUP DISABLED ENABLED ENABLED 1 row selected. SVRMGR> alter system kill session '116,3259'; Statement processed. Killing that httpd process did not free the session to compile package body. At OS (super is a freeware program like sudo): super stop-ias-apache super start-ias-apache This does the trick. The compile finishes immediately. Log into SYS (words in brackets are mine; the 1st row is at CAC25730 and is body): SQL> select * from x$kglob where kglnaobj = 'WWSBR_SECURITY'; ADDR INDX INST_ID KGLHDADR KGLHDPAR KGLNAOWN -------- ---------- ---------- -------- -------- ---------------------------------------------------------------- KGLNAOBJ ---------------------------------------------------------------------------------------------------------------------------------------------------------------- KGLNADLK KGLNAHSH KGLNATIM KGLNAPTM KGLHDNSP KGLHDLMD KGLHDPMD KGLHDFLG KGLHDOBJ KGLHDLDC ---------------------------------------------------------------- ---------- --------- --------- ---------- ---------- ---------- ---------- -------- ---------- KGLHDIVC KGLHDEXC KGLHDLKC KGLHDKMK KGLHDDMK KGLHDAMK KGLOBFLG KGLOBSTA KGLOBTYP KGLOBHS0 KGLOBHS1 KGLOBHS2 KGLOBHS3 KGLOBHS4 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KGLOBHS5 KGLOBHS6 KGLOBHS7 KGLOBHD0 KGLOBHD1 KGLOBHD2 KGLOBHD3 KGLOBHD4 KGLOBHD5 KGLOBHD6 KGLOBHD7 KGLOBPC0 KGLOBPC6 KGLOBTP0 KGLOBT00 KGLOBT01 ---------- ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- ---------- ---------- -------- ---------- ---------- KGLOBT02 KGLOBT08 KGLOBT09 KGLOBT10 KGLOBT11 KGLOBT12 KGLOBT13 KGLOBT14 KGLOBT15 KGLOBT16 KGLOBT17 KGLOBT18 KGLOBT19 KGLOBT20 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KGLOBT21 KGLOBT22 KGLOBT23 KGLOBT24 KGLOBT25 KGLOBT26 KGLOBT27 KGLOBT32 KGLOBTL0 KGLOBTL1 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KGLOBTS0 KGLOBTS1 KGLOBTN0 KGLOBTN1 ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- KGLOBTN2 KGLOBTN3 KGLOBTN4 KGLOBTN5 KGLOBTS2 ---------- ---------- ---------- ---------- ---------------------------------------------------------------- 01A37450 0 1 CAC25730 CAC25730 PORTAL30 WWSBR_SECURITY 2689957164 25-MAY-01 24-MAY-01 2[namespace] 0[lockmode] 0[pinmode] 33554432 CAC255D4 3 0 4928 0 0 25 0 5 1 11 3395 0 0 0 49860 0 0 0 CAC256C0 00 00 00 CAC1CCCC 00 00 00 0 0 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 01A36DA8 16212 1 CB7EDEE4 CB7EDEE4 PORTAL30 WWSBR_SECURITY 2689957163 25-APR-01 1 0 0 33554432 CAC3B6F4 3 0 4741 0 0 21 0 5 1 9 1451 0 44136 0 9360 0 0 0 CAC5D58C 00 CAC26E08 00 CAC3B788 00 00 00 0 0 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0