---压力测试的存储过程
---测试用存储过程
create or replace procedure proc_hard_parse
as
v_cnt pls_integer;
begin
for i in 1..100000000 loop
select count(1) into v_cnt from t_hps where 1=i;
end loop;
end;

---会话1 执行上述加压存储过程
SQL> exec proc_hard_parse;

--会话:2 对存储过程引用表ddl
SQL> alter table t_hps add c int;

Table altered

---会话3:马上发现产生了library cache lock
SQL> select sid,event from v$session where type='USER' and rownum<=20;

       SID EVENT
---------- ----------------------------------------------------------------
        36 SQL*Net message from client
        37 SQL*Net message to client
        38 SQL*Net message from client
        39 SQL*Net message from client
        40 library cache lock
        46 SQL*Net message from client
        48 SQL*Net message from client
        52 jobq slave wait
        57 jobq slave wait

9 rows selected

小结：1,当某个sql正在运行，所引用的对象发生了ddl变化，也包括grant,revoke等授权动作,则产生library cache lock
      2,library cache lock是为了获取 library cache handle
      3,而library cache pin则是正在运行存储过程或对象时，马上编译存储过程产生 library cache pin

ITPub博客

library cache lock模拟小记

最新文章