ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 等待模拟-library cache shared pool 硬解析

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

原创 Linux操作系统 作者:gaopengtttt 时间:2013-06-27 15:41:49 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    681
  • 访问量
    2937541