1. 首页
  2. IT资讯

等待模拟-library cache shared pool 硬解析

drop table test1;
create table test1
(it int);
insert into test1
values(10);
create table test2
as
select * from test1;
create table test3
as
select * from test1;
create table test4
as
select * from test1;
create table test5
as
select * from test1;
create table test6
as
select * from test1;
create table test7
as
select * from test1;
create table test8
as
select * from test1;
create table test9
as
select * from test1;
create table test10
as
select * from test1;
create table test11
as
select * from test1;
create table test12
as
select * from test1;
create table test13
as
select * from test1;
create table test14
as
select * from test1;
create table test15
as
select * from test1;
create table test16
as
select * from test1;
create table test17
as
select * from test1;
create table test18
as
select * from test1;
create table test19
as
select * from test1;
create table test20
as
select * from test1;
create table test21
as
select * from test1;
引发500000次的硬解析,
为了不引发软解析,创建唯一的SQL语句。
SGA设置为150M
create or replace procedure do_hard_parse(p_idx in number)
 is
   v_value number;
   v_cursor sys_refcursor;
begin
  for idx in 1..500000 loop
    open v_cursor for ‘select ‘||p_idx||’ from ‘||rpad(”,idx)||’ test’||p_idx||’ where rownum=1′;
    fetch v_cursor into v_value;
    close v_cursor;
 end loop;
end;
清空shared pool
alter system flush shared_pool;
同时在30个会话执行
var job_no number;
begin 
  for idx in 1..20 loop
      dbms_job.submit(:job_no,’do_hard_parse(‘||idx||’);’);
end loop;
commit;
end;
开启一个会话执行
execute do_hard_parse(21);
使用观察
select * from v$session_event a,(select SID from v$mystat where rownum<=1) b
where a.SID=b.sid order by TIME_WAITED desc;

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

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183705.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code