spool result.txt
set timing on

drop table test;
create table test (
	id integer not null,
	constraint pk primary key (id)
);

create or replace procedure filltest is
	id_ integer;
begin
	id_:= 0 ;
	while id_< 1000000  loop
		insert into test values (id_);
		id_:=id_+ 1 ;
	end loop;
	commit;
end filltest;
/

@stat

set timing off
spool off
ed result.txt

create table test (
	id	integer not null,
	constraint pk primary key (id)
)@

commit@

drop procedure filltest(timestamp,timestamp)@

create procedure filltest (
	out start timestamp,
	out stop timestamp
) language sql
m: begin
	declare id integer default  0 ;
	set start=current timestamp;
	while m.id< 1000000  do
		insert into test values (m.id);
		set m.id=m.id+ 1 ;
	end while;
	set stop=current timestamp;
end
@
commit@

select
	m.STATISTIC#,
	n.name,
	m.value
from
	v$mystat m,
	v$statname n
where
	m.STATISTIC# in ( 133 , 173 )
	and m.STATISTIC#=n.STATISTIC#;

update dbm cfg using DFT_MON_BUFPOOL   ON@
update dbm cfg using DFT_MON_LOCK 	   ON@
update dbm cfg using DFT_MON_SORT      ON@
update dbm cfg using DFT_MON_STMT      ON@
update dbm cfg using DFT_MON_TABLE     ON@
update dbm cfg using DFT_MON_TIMESTAMP ON@
update dbm cfg using DFT_MON_UOW       ON@

drop procedure GET_IO_STATISTICS()@

create procedure GET_IO_STATISTICS()
language sql
result sets  1 
begin
	declare CDN cursor with return for
		select 
			AGENT_ID,
			UOW_LOG_SPACE_USED,
			POOL_DATA_TO_ESTORE+POOL_INDEX_TO_ESTORE as WRITTEN_PAGES,
			POOL_INDEX_FROM_ESTORE+POOL_DATA_FROM_ESTORE as READ_PAGES,
			POOL_DATA_WRITES,
			POOL_INDEX_WRITES,
			DIRECT_READS,
			DIRECT_WRITES
		from 
			table(snapshot_appl('test',- 1 )) as a 
		where
			a.agent_id in
				(select 
						AGENT_ID 
					from 
						table(SNAPSHOT_APPL_INFO('test',- 1 )) as tmp 
						where tmp.APPL_ID=APPLICATION_ID()
				);
	open CDN;
end
@

commit@

SQL> set timing on
SQL> call filltest();

Call completed.

Elapsed:  00 : 03 : 56 . 93 
SQL> @stat

STATISTIC# NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
        133  redo size                                                          492431512 
        173  undo change vector size                                            156833164 

Elapsed:  00 : 00 : 00 . 12 

UPDATE MONITOR SWITCHES USING BUFFERPOOL ON
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

UPDATE MONITOR SWITCHES USING TIMESTAMP ON
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

UPDATE MONITOR SWITCHES USING UOW ON
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

UPDATE MONITOR SWITCHES USING TABLE ON
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

commit
DB20000I  The SQL command completed successfully.

call filltest(?,?)

  Value of output parameters
  --------------------------
  Parameter Name  : START
  Parameter Value :  2005 - 03 - 02 - 01 . 20 . 57 . 500000  

  Parameter Name  : STOP
  Parameter Value :  2005 - 03 - 02 - 01 . 22 . 04 . 125000  

  Return Status =  0 


call GET_IO_STATISTICS()


  Result set  1 
  --------------

  AGENT_ID             UOW_LOG_SPACE_USED   WRITTEN_PAGES        READ_PAGES           POOL_DATA_WRITES     POOL_INDEX_WRITES    DIRECT_READS         DIRECT_WRITES       
  -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                     66              229214712                      0                      0                   3988                   3627                    136                  60672 

   1  record(s) selected.

  Return Status =  0 


commit
DB20000I  The SQL command completed successfully.