[AD]共享池中的pin和lock

一，Library Cache Pin及Library Cache Lock分析
1,Oracle使用两种数据结构来进行SharedPool的并发访问控制：lock和pin。lock比pin具有更高的级别。
2,lock在handle上获得，在pin一个对象之前，必须首先获得该handle的锁定。锁定主要有3中模式：NULL、Share和Exclusive。在读取访问对象时，通常需要获取NULL（空）模式以及Share（共享）模式的锁定。在修改对象时，需要获得Exclusive（排他）锁定。
3,在锁定了Library Cache对象以后，一个进程在访问之前必须pin该对象。同样pin有3中模式：NULL、Shared和Exclusive。只读模式时获得共享pin，修改模式获得排他pin。通常访问、执行过程和Package时，获得的都是共享pin，如果排他pin被持有，那么数据库此时就要产生等待。
二，LIBRARY CACHE PIN等待事件
1，Oracle文档上这样介绍这个等待事件：Library Cache Pin是用来管理Library Cache的并发访问的，pin一个Object会引起相应的heap被载入内存中（如果此前没有被载），pins可以在三个模式下获得：NULL、Share和Exclusive，可以认为pin是一种特定形式的锁。
2，当LibraryCachePin等待事件出现时，通常说明该pin被其他用户以非兼容模式持有。
3，LibraryCachePin的等待时间为3秒钟，其中有1秒钟用于PMON后台进程，即在取得pin之前最多等待3秒钟，否则就超时。
4，Library Cache Pin的参数如下,有用的主要是P1和P2。
P1-----KGL Handle address
P2-----Pin address
P3-----Encode Mode & Namespace
5，Library Cache Pin通常发生在编译或重新编译PL/SQL、VIEW、TYPES等Object时，编译通常都是显性的，如安装应用程序、升级、安装补丁程序等，另外，alter、grant和revoke等操作也会使Object变得无效，可以通过Object的LAST_DDL_TIME观察这些变化。
6，当Object变得无效时，Oracle会在第一次访问次Object时试图去重新编译它，如果此时其他Session已经把此Object pin到Library Cache中，就会出现问题，特别是当有大量的活动session并且存在复杂的dependence时。在某种情况下，重新编译Object可能会花几个小时时间，从而阻塞其他试图访问次Object的进程。
##################################################不建议阅读我自己都没懂################################ ####
二，实验 模拟LibraryCachePin等待
1，创建测试用存储过程
SQL> create or replace PROCEDURE pining
  2  IS
  3  BEGIN
  4  NULL;
  5  END;
  6  /
Procedure created.
SQL>
SQL> create or replace PROCEDURE calling
  2  IS
  3  BEGIN
  4  pining;
  5  dbms_lock.sleep(3000);
  6  END;
  7  /
Procedure created.
SQL>
2，模拟竞争
首先执行calling过程，在calling过程中调用pining过程。此时pining过程上获得共享pin，如果此时尝试对pining进行授权或重新编译，将产生Library Cache Pin等待，直到calling执行完毕。
sesion1:
SQL> exec calling;
PL/SQL procedure successfully completed.
SQL>
session2:
如果在老版本的Oracle可能会出现session2挂起现象。但是在Oracle10g中session2不会挂起。
SQL> grant execute on pining to tianmao;
Grant succeeded.
SQL>
所以在这里我没有查询出等待
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
  2  from v$session_wait where event like 'library%';
no rows selected
SQL>
3，如果有输出类似下面的结果，P1列是Library Cache Handle Address，Pn字段是十进制表示，PnRAW字段是十六进制表示。

      SID       SEQ# EVENT                                  P1 P1RAW            P2 P2RAW            P3 P3RAW    STATE
---------- ---------- ------------------------------ ---------- -------- ---------- -------- ---------- -------- ----------
       155        211 rdbms ipc message                   30000 00007530          0 00                0 00       WAITING

此等待的对象的haddle地址XXXXXXX(注：由于在Oracle 10g没有已经没有了这个bug，这里用XXXXXXX代表)。通过这个地址，查询X$KGLOB视图可以得到对象的具体信息。
4，X$KGLOB的名称含义为[K]eneric [L]ibrary Cache Manager [OB]ject。
SQL> col KGLNAOWN for a10
SQL> col KGLNAOBJ for a20
SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
  2  from X$KGLOB
  3  WHERE KGLHDADR='XXXXXXX'
  4  /
no rows selected
SQL>

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
B7F83AEC 322FE1CC 322FE1CC            select   schedule_mo 1079377382 2F9A64C8
                                      de, start_calibrate,
                                       num_votes,   synced
                                      _time, last_vote, st
                                      atus from   WRI$_SCH
                                      _CONTROL where   sch
                                      edule_id = :id

5，这里KGLNAHSH代表该对象的Hash Value，由此可知，在pining对象上正经历Library Cache Pin的等待，然后引入另一个内部视图X$KGLPN。
6，X$KGLPN的名称含义为[K]ernel[G]eneric[L]ibrary Cache Manager object [P]i[N]s.


SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
  2  b.KGLPNLCK,b.KGLPNMOD,b.KGLPNREQ
  3  from v$session a,x$kglpn b
  4  where a.saddr=b.kglpnuse and b.kglpnhdl='XXXXXXX' and b.KGLPNMOD<>0;
no rows selected
SQL>
       SID USERN PROGRAM                        ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ
---------- ----- ------------------------------ -------- -------- -------- -------- -------- -------- ---------- ----------
       161       oracle@rhel.oracle.com (MMON)  B7F841C8 30FD784C 31DB45CC 31DB45CC 322E1BD4 00                2          0
7，SQL> select * from v$session where sid=161;
得到SID，就可以通过v$session.SQL_HASH_VALUE、v$session.SQL_ADDRESS 等字段关联v$sqltext、v$sqlarea等视图获得当前session正在执行的操作。
8，SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='';
这里得到这个用户的执行calling这个存储过程，接下来的工作就应该去查calling在做什么，这个calling做的工作是是dbms_lock.sleep(3000),也就是PL/SQL Lock Timer正在持有的原因。至此找了LibraryCachePin的原因。
##################################################不建议阅读我自己都没懂####################################
三，简化一下查询
1，获得Library Cache Pin 等待对象；
SQL> SELECT addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj
  2  FROM x$kglob
  3  WHERE kglhdadr IN(SELECT p1raw
  4  FROM v$session_wait
  5  WHERE event LIKE 'library%');
no rows selected
SQL>
2,对获得持有等待对象的session信息
SQL> SELECT a.SID,a.username,a.program,b.addr,b.kglpnadr,b.kglpnuse,
  2  b.kglpnses,b.kglpnhdl,b.kglpnlck,b.kglpnmod,b.kglpnreq
  3  FROM v$session a,x$kglpn b
  4  WHERE a.saddr = b.kglpnuse
  5  AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
  8  WHERE event LIKE 'library%');
no rows selected
SQL>
3,获取持有对象用户执行的代码：
SQL> SELECT sql_text
  2  FROM v$sqlarea
  3  WHERE (v$sqlarea.address,v$sqlarea.hash_value) IN (
  4  SELECT sql_address,sql_hash_value
  5  FROM v$session
  6  WHERE SID IN(
  7  SELECT SID
  8  FROM v$session a,x$kglpn b
  9  WHERE a.saddr = b.kglpnuse
 10  AND b.kglpnmod <> 0
 11  AND b.kglpnhdl IN (SELECT p1raw
 12  FROM v$session_wait
 13  WHERE event LIKE 'library%')));
no rows selected
SQL>
4，在grant之前和之后，可以转储一下SharedPool的内容，以进行观察和比较。
SQL>ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';
5，Library Cache Object以排他的模式被锁定，这个锁定是在haddle上获得的。Exclusive锁定可以防止其他用户执行同样的操作，同时防止其他用户创建新的引用此过程的对象。
以Shared模式pin该对象，以执行安全和错误检查。共享pin被释放，重新排他模式pin该对象，执行重编译。是所有依赖该过程的对象失效。释放Exclusive Lock和Exclusive Pin。
四，LIBRARY CACHE LOCK 等待事件
如果此时再发出一条grant或compile的命令，那么library cache lock等待事件静会出现。
SQL> alter procedure pining compile;
此进程挂起，查询v$session_wait视图可以获得信息；
SQL> select * from v$session_wait;
由于haddle上的lock已经被session2以Exclusive模式持有，所以session3产生了等待。可以看到，在生产数据库中权限的授予、对象的重新编译都可能会导致LibraryCachePin等待的出现，所以应该尽量避免在高峰期进行以上操作。
另外，测试的案例本身就说明，如果Package或过程中存在复杂的、交互的依赖关系就极易导致Library Cache Pin的出现，所以在应用开发的过程中，也应该注意这方面的问题。

五，几个有用的查询

1，SQL> select sid,event,p1,p1raw from v$session_wait;
2，SQL> select sid,event,p1,p1raw from v$session_wait where event not like 'SQL*Net%';
3，SQL> select addr,latch#,name,gets,spin_gets from v$latch order by spin_gets;
4，SQL> select name,value from v$sysstat where name like '%parse%';
5，SQL> select sql_text,VERSION_COUNT,INVALIDATIONS,PARSE_CALLS,
  2   OPTIMIZER_MODE,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS,HASH_VALUE
  3   from v$sqlarea where version_count>1000;
6，SQL>  select CHILD_NUMBER,EXECUTIONS,OPTIMIZER_MODE,OPTIMIZER_COST,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS,HASH_VALUE
  2    from v$sql where HASH_VALUE='XXXXXXXXXX';
7，SQL> select distinct(sql_text) from v$sql where OPTIMIZER_COST >1000;
8，SQL> select sql_text,version_count from v$sqlarea where version_count > 10;
9， select * from v$sgastat where name in ('miscellaneous','free memory') and pool='shared pool';
10，mem.leak.sql脚本
##################################################不建议阅读我自己都没懂####################################

set heading off
column what format a40
column value format a30

select 'db instance' what,user||'@'||global_name value from global_name
UNION
select '#rows in v$segstat',to_char(count(*)) from v$segstat;

set linesize 200
set time on
set serveroutput on size 300000

declare
l_temp                 char(1);
l_before               number;
l_after                number := 0;
l_loop_times           pls_integer := 1000;        --try 1000
l_sleep                number := 0.00;              --try makes no difference


cursor c_seg is select * from v$segstat;
r_seg  c_seg%ROWTYPE;

function get_mem return number is
cursor c_mem is select bytes from v$sgastat
where name='free memory' and pool='shared pool';
r_mem   c_mem%ROWTYPE;
begin
open c_mem;fetch c_mem into r_mem; close c_mem;

return r_mem.bytes;
end get_mem;
begin
l_after := get_mem();

for x in 1..l_loop_times loop
l_before := l_after;

OPEN c_seg;FETCH c_seg INTO r_seg;CLOSE c_seg;

l_after := get_mem();
dbms_output.put_line('Loop'||x||':('||
to_char(sysdate,'hh24:mi:ss')||') from '||
to_char(l_before,'999,999,999')||'to'||
to_char(l_after,'999,999,999')||'(loss of'||
to_char((l_before-l_after),'9,999,999')||')');
dbms_lock.sleep(l_sleep);
end loop;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29611940/viewspace-1147619/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29611940/viewspace-1147619/