12 w.wait_Time,

13 w.seconds_in_Wait

14 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w

15 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)

16 and ob.kglhdadr = pn.kglpnhdl

17 and pn.kglpnuse = ses.addr

18 and w.sid = ses.indx

19 order by seconds_in_wait desc

20 ;

SID SERIAL# USERN MACHINE OBJ_O OBJ_NA PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

---------- ---------- ----- ------- ----- ------ ---------- ---------- ---------- ------------------- -------------------- ---------- ---------------

22 27 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 875

42 49 SYS oracle SYS RHYS 0 0 3 WAITING library cache pin 0 868

42 49 SYS oracle SYS RHYS 3 2 0 WAITING library cache pin 0 868

SQL>

可以看到会话2（sid为42）正在等待获得pin（exclusive），但是会话1（sid22正好获得了该对象的pin（share），因此library cache pin就产生了。

在会话 三：

SQL> select * from v$mystat where rownum<2;

SID STATISTIC# VALUE

---------- ---------- ----------

46 0 0

SQL> execute rhys(true,false);

查询结果如下：

SID SERIAL# USERN MACHINE OBJ_O OBJ_NA PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

---------- ---------- ----- ------- ----- ------ ---------- ---------- ---------- ------------------- -------------------- ---------- ---------------

22 27 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 2361

42 49 SYS oracle SYS RHYS 0 0 3 WAITING library cache pin 0 19

42 49 SYS oracle SYS RHYS 3 2 0 WAITING library cache pin 0 19

46 11 SYS oracle SYS RHYS 0 0 2 WAITING library cache pin 0 4

SQL>

可以看到pin在rhys上的sid为22持有share pin，在42请求获得exclusive pin，46请求获得share pin。

在session 4执行：

SQL> execute rhys(true,false);

如何我在session 5执行：

execute rhys(false ,true);

SID SERIAL# USERN MACHINE OBJ_O OBJ_NA PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

---------- ---------- ----- ------- ----- ------ ---------- ---------- ---------- ------------------- -------------------- ---------- ---------------

22 27 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 2537

42 49 SYS oracle SYS RHYS 0 0 3 WAITING library cache pin 0 195

42 49 SYS oracle SYS RHYS 3 2 0 WAITING library cache pin 0 195

46 11 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 165

48 11 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 58

43 29 SYS oracle SYS RHYS 3 2 0 WAITING library cache lock 0 0

6 rows selected

那么这时session5就会请求library cache lock。

如何降低library cache lock？