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

gaopengtttt發表於2013-06-27
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章