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
where a.SID=b.sid order by TIME_WAITED desc;
