Script to test IO performance in oracle DB

 set timing on;

set serveroutput on size unlimited

set feedback on

column tm new_value file_time noprint

column tm2 new_value MARKET_ENV noprint

select to_char(sysdate, 'YYYYMMDD') tm from dual ;

select '&ENV' tm2 FROM DUAL;

spool C:\D\DatabaseUpgrade\&MARKET_ENV._IO_Testing_large_&file_time..log

PROMPT &MARKET_ENV

select sysdate AS RUN_DATE from dual;

create  type number_ntt as table of number;

/

create function row_generator ( rows_in in pls_integer ) return number_ntt pipelined is

begin

   for i in 1 .. rows_in loop

      pipe row (i);

   end loop;

   return;

end;

/

--drop table big_table;

create table big_table as select rownum id from dual where 1=0

/

alter table big_table nologging;

-- flush ALL

--alter system flush shared_pool;

--alter system flush buffer_cache;

--exec runstats_pkg.rs_start;

-- first execution

--insert /*+ append */ into big_table select * from table(row_generator(10000));

--commit;

--exec runstats_pkg.rs_middle;

-- second execution for drop the recursive calls and hard parse

insert /*+ append */ into big_table select * from table(row_generator(100000000));

commit;

--exec runstats_pkg.rs_stop;

--SELECT * FROM big_table

CREATE INDEX BIG_TABLE_IDX ON BIG_TABLE

(id)

TABLESPACE SCPODATA;

SET TIMING ON ;

SELECT /*+ INDEX_FFS(BIG_TABLE BIG_TABLE_IDX)*/ COUNT(*) FROM BIG_TABLE;

SET TIMING OFF;

drop table big_table purge;

drop function row_generator;

drop type number_ntt;

spool off