Warranty: None. Use at your own risk. OraPub is NOT responsible.
Date    : July, 2012
Author  : Craig Shallahamer, craig@orapub.com

Don't just run this script, because it's not designed this way.

-------------------------------------
SETUP STUFF
-------------------------------------
Craig's location: /home/oracle/base/local/parseTest

connect / as sysdba
grant select on v_$mystat to system;
grant select on v_$session_event to system;
grant select on v_$process to system;
grant select on v_$session to system;

connect system

set tab off
set serveroutput on

create or replace function getMySid return number as
begin
  declare
    i number;
  begin
    select sid into i from v$mystat where rownum=1;
    return i;
  end;
end;
/

create or replace function getMyTracefile return varchar2 as
begin
  declare
    result varchar2(500);
  begin
    select tracefile
	into   result
    from   v$process p,
           v$session s
    where  p.addr = s.paddr
      and  s.sid  = (select getmysid from dual);
	
	return result;
  end;
end;
/

drop sequence opGenSeq;
create sequence opGenSeq;

create or replace procedure makeSqlStmtLong as
begin
  declare
    text varchar2(5000);
    i    number;
  begin
    for i in 1..50
    loop
      dbms_output.put_line('select distinct(owner) from opTable' || i || ' where object_id = ' ||  opGenSeq.nextval || ' union ');
    end loop;
    
    dbms_output.put_line('select distinct(owner) from opTable1 where object_id = ' ||  opGenSeq.nextval ||';');
  end;
end;
/

drop table op_results;
create table op_results (
  sql_ident				number,
  exec_num				number,
  parse_cpu_time		number,
  parse_elapsed_time 	number,
  trace_file		    varchar2(300)
);


-- This block will create the tables that will be selected from.
--
declare
  i           number;
  drop_stmt   varchar2(100);
  create_tabl varchar2(100);
  create_indx1 varchar2(100);
  create_indx2 varchar2(100);
  create_indx3 varchar2(100);
begin
  for i in 1..75
  loop
    drop_stmt := 'drop table opTable' || to_char(i) ;
    create_tabl := 'create table opTable' || to_char(i) || ' as select * from dba_objects where 1=0';
    dbms_output.put_line(drop_stmt);
    dbms_output.put_line(create_tabl);

    create_indx1 := 'create index opTable' || to_char(i) || 'idx1 on opTable' || to_char(i) || ' (owner, object_name, subobject_name, timestamp)';
    dbms_output.put_line(create_indx1);
    create_indx2 := 'create index opTable' || to_char(i) || 'idx2 on opTable' || to_char(i) || ' (object_name, subobject_name, timestamp)';
    dbms_output.put_line(create_indx2);
    create_indx3 := 'create index opTable' || to_char(i) || 'idx3 on opTable' || to_char(i) || ' (subobject_name, timestamp)';
    dbms_output.put_line(create_indx3);
    
    begin
      execute immediate drop_stmt ;
      execute immediate create_tabl ;
      execute immediate create_indx1 ;
      execute immediate create_indx2 ;
      execute immediate create_indx3 ;
      exception
        when others then
          execute immediate create_tabl ;
          execute immediate create_indx1 ;
          execute immediate create_indx2 ;
          execute immediate create_indx3 ;
    end;
  end loop;
end;
/

select table_name from user_tables where table_name like 'OPTABLE%';
select index_name from user_indexes where index_name like 'OPTABLE%';

-------------------------------------
DO THE EXPERIMENT
-------------------------------------

The getparsetime.sh script will call the perl script, getparsetime.pl

$ getparsetime.sh 

The output, which is nicely formatted insert statements will be shown on screen,
into the ./op_results.sql file, and loaded into the op_results table.

-------------------------------------
REPORT ON THE RESULTS
-------------------------------------

Below are reporting statements that create output that will easily load into my
Mathematica statistical analysis notepad. I also placed these into a single file,
parseresults.sql

set tab off
set echo on
set feedback off

select parse_cpu_time||',' from op_results where exec_num = 1;
select parse_cpu_time||',' from op_results where exec_num = 2;
select parse_cpu_time||',' from op_results where exec_num = 3;
select parse_cpu_time||',' from op_results where exec_num = 4;
select parse_cpu_time||',' from op_results where exec_num = 5;
select parse_cpu_time||',' from op_results where exec_num = 6;
select parse_cpu_time||',' from op_results where exec_num = 7;

select parse_elapsed_time||',' from op_results where exec_num = 1;
select parse_elapsed_time||',' from op_results where exec_num = 2;
select parse_elapsed_time||',' from op_results where exec_num = 3;
select parse_elapsed_time||',' from op_results where exec_num = 4;
select parse_elapsed_time||',' from op_results where exec_num = 5;
select parse_elapsed_time||',' from op_results where exec_num = 6;
select parse_elapsed_time||',' from op_results where exec_num = 7;