--dbms_lock使用
---安装并赋权
$ORACLE_HOME/rdbms/admin/dbmslock.sql
grant execute on dbms_lock to username;

--db版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

--sessionA:
---在scoot用户下创建存储过程zyp并执行
SQL> conn scott/123;
Connected.
SQL> create or replace procedure zyp
2 as
3 begin
4 dbms_lock.sleep(100000);
5 end;
6 /

Procedure created.

SQL> exec zyp;

--sessionB:
---在sys用户下重新编译存储过程scott.zyp
SQL> show user
USER is "SYS"
SQL> create or replace procedure scott.zyp
2 as
3 begin
4 dbms_lock.sleep(100000);
5 end;
6 /

--sessionC:
---查询library cache pin等待事件的相关会话
SQL> set linesize 300
SQL> select saddr,sid,username,event,p1raw from v$session where event='library cache pin';

SADDR SID USERNAME EVENT P1RAW
-------- ---------- ------------------------------ ---------------------------------------------------------------- ----------------
3D2C0984 145 SYS library cache pin 000000003D5B1884

---找到持有library cache pin以及等待library cache pin的session
SQL> SELECT s.sid,s.username, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
2    FROM v$session_wait w, x$kglpn p, v$session s, v$process o
3   WHERE p.kglpnuse = s.saddr
4     AND kglpnhdl = '&p1raw'
5     and w.event like '%library cache pin%'
6     and s.paddr = o.addr;
Enter value for p1raw: 3D5B1884
old   4:    AND kglpnhdl = '&p1raw'
new   4:    AND kglpnhdl = '3D5B1884'

       SID USERNAME                             Mode        Req OS Process
---------- ------------------------------ ---------- ---------- ------------------------
       145 SYS                                     0          3 12884
       143 SCOTT                                   2          0 12823
重新编译存储过程scott.zyp的session是145，从上述结果里我们可以看出来145现在想以Exclusive模式（即Req=3）去持有library cache pin，同时现在持有上述library cache pin的是session 143，且143的持有模式是Share（即Mode=2），也就是说143阻塞了145

---获取sid为143的SQL_HASH_VALUE（间隔10s多执行几次）
SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;

DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE)
-------------------------------------------------------
3192006346

SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;

DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE)
-------------------------------------------------------
3192006346

SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;

DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE)
-------------------------------------------------------
                                             3192006346
---根据获得的SQL_HASH_VALUE获得对应的sqltext
SQL> select sql_text from v$sqltext where hash_value='&SQL_HASH_VALUE' order by piece;
Enter value for sql_hash_value: 3192006346
old   1: select sql_text from v$sqltext where hash_value='&SQL_HASH_VALUE' order by piece
new   1: select sql_text from v$sqltext where hash_value='3192006346' order by piece

SQL_TEXT
----------------------------------------------------------------
BEGIN zyp; END;

---kill持有锁的session
SQL> !
[oracle@ora11 ~]$ kill -9 12823
[oracle@ora11 ~]$ exit
exit

---再次查询library cache pin等待消失
SQL> select saddr,sid,username,event,p1raw from v$session where event='library cache pin';

no rows selected

ITPub博客

模拟library cache pin并处理

最新文章