/*
Name:		BulkOps.sql

Author:	Niall Litchfield

Purpose:	Script to investigate performance and resource usage of
		bulk binds and collection in PL/SQL.
		NB: All this is inspired by Connor McDonald's book
		Practical PL/SQL.

Requires:	SELECT ON SYS.V_$SYSSTAT,SYS.V_$MYSTAT

History:	1.0 25th May 2004 Inital Revision

*/

/* 
setup table & view
*/

drop table large_tab;
create table large_tab(
	col1	varchar2(10));

create or replace view myStats
as
select s.name,m.value
from v$sysstat s,v$mystat m
where s.statistic#=m.statistic#;


/* 
populate table
*/
begin
	for i in 1..500000 loop
		insert into large_tab values('Mr '||i);
	end loop;
	commit;
end;
/


create or replace procedure 
bulk_test 
is
 mem_used number;
 cpu_used number;
 elapsed number;
 c number;
 t number;
 type recs is table of large_tab%rowtype;
 r recs;
 cursor c1 is select * from large_tab;
begin
 for i in 0 .. 10 loop
	t := dbms_utility.get_time;
	select value into c from mystats where name= 'CPU used by this session';
	open c1;
	loop
		fetch c1
		bulk collect into r
			limit power(2,i*2);
		exit when c1%notfound;
	end loop;
	close c1;
	elapsed := dbms_utility.get_time - t;
	select value-c into cpu_used from myStats where name = 'CPU used by this session';
	select value into mem_used from myStats where name = 'session pga memory max';
	dbms_output.put_line('Rows:'||power(2,i*2));
	dbms_output.put_line('- 	Time: '||elapsed);
	dbms_output.put_line('- 	Max Mem: '||mem_used);
	dbms_output.put_line('- 	CPU: '||cpu_used);
 end loop;
 end;
/

show errors

set serveroutput on

exec limit_test;