03. massive_parse

--------------------------------------------------
-- @name: massive_parse
-- @author: dion cho
-- @description: massive parse and contention
--------------------------------------------------

-------------------------------------------------------------------------
-- case1: hard parse

create or replace procedure hard_parse(p_seed in number)
is
  v_value varchar2(4000);
begin
  dbms_random.initialize(p_seed);
 
  -- do the hell hard parse
  for idx in 1 .. 5000 loop
    begin
      execute immediate
        'select ''' || idx || '_' || dbms_random.random || ''' from dual'
        into v_value
      ;
    exception when others then null;
    end;
  end loop;
end;
/

ed massive_parse_1
/*
exec hard_parse(&1);
exit;
*/

ho start sqlplus ukja/ukja@ukja102 @massive_parse_1 100000
ho start sqlplus ukja/ukja@ukja102 @massive_parse_1 200000
ho start sqlplus ukja/ukja@ukja102 @massive_parse_1 300000
ho start sqlplus ukja/ukja@ukja102 @massive_parse_1 400000

@mysid

@mon_on &v_sid

exec hard_parse(500000);

@mon_off

@mon_show


------------------------------------------------------------------
-- case2: parse failure

create or replace procedure hard_parse_failure(p_seed in number)
is
  v_value varchar2(4000);
begin
  dbms_random.initialize(p_seed);
 
  -- do the hell hard parse failure
  for idx in 1 .. 500 loop
    begin
      execute immediate
        'select ' || dbms_random.random || ' from dualx'
        into v_value
      ;
    exception when others then null;
    end;
  end loop;
end;
/


ed massive_parse_2
/*
exec hard_parse_failure(&1);
exit;
*/

ho start sqlplus ukja/ukja@ukja102 @massive_parse_2 100000
ho start sqlplus ukja/ukja@ukja102 @massive_parse_2 200000
ho start sqlplus ukja/ukja@ukja102 @massive_parse_2 300000
ho start sqlplus ukja/ukja@ukja102 @massive_parse_2 400000

@mysid

@mon_on &v_sid

exec hard_parse_failure(500000);

@mon_off

@mon_show


----------------------------------------------------------------
-- case3: soft parse
-- create garbage table
begin
  for idx in 1 .. 10 loop
    begin
      execute immediate 'drop table t_' || idx || ' purge';
    exception when others then null;
    end;
    
    begin
      execute immediate 'create table t_' || idx || '(c1 int)';
    exception when others then null;
    end;
  end loop;
end;
/



create or replace procedure soft_parse
is
  v_value varchar2(4000);
  v_cursor sys_refcursor;
begin
  -- session cached cursors (change this!!!)
  execute immediate 'alter session set session_cached_cursors = 100';
   
  -- do the hell soft parse
  for idx in 1 .. 5000 loop
      for idx2 in 1 .. 10 loop
        open v_cursor for
          'select ' ||  idx2 ||
          ' from t_1, t_2, t_3, t_4, t_5, t_6, t_7, t_8, t_9, t_10';
        close v_cursor;
      end loop;
  end loop;
end;
/

ed massive_parse_3
/*
exec soft_parse;
exit;
*/

ho start sqlplus ukja/ukja@ukja102 @massive_parse_3
ho start sqlplus ukja/ukja@ukja102 @massive_parse_3
ho start sqlplus ukja/ukja@ukja102 @massive_parse_3
ho start sqlplus ukja/ukja@ukja102 @massive_parse_3

@mysid

@mon_on &v_sid

exec soft_parse;

@mon_off

@mon_show


-- session#2
@wait

Comments