---压力测试的存储过程
---测试用存储过程
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博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-759281/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-759281/