conn / as sysdba

set timing off feedback off
column trace_name_col new_value trace_name

alter session set current_schema = acme;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=500000000;

alter system flush buffer_cache;
set timing on feedback on

alter session set current_schema = acme;

declare
	L_cost number;
	L_sqls number := 0;
begin
	dbms_random.seed(0);

	L_sqls := L_sqls + 1;
	for a in (	select pkcol
			from accs 
			where rownum < 500
			order by dbms_random.value)
	loop
		L_sqls := L_sqls + 1;
		for t in (	select itmpkcol
				from txns
				where accpkcol = a.pkcol
				order by pkcol)
		loop
			L_sqls := L_sqls + 1;
			select sum(cost)
			into L_cost
			from itms
			where pkcol = t.itmpkcol;
		end loop;
	end loop;
	dbms_output.put_line('sqls='||L_sqls);
end;
/


select event,time_waited_micro / total_waits avg_wait_micro,total_waits,time_waited_micro from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like 'db%' order by time_waited ;

conn / as sysdba

set timing off feedback off
alter session set current_schema = acme;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=500000000;

alter system flush buffer_cache;

select value trace_name_col from v$diag_info where name = 'Default Trace File';
exec dbms_monitor.session_trace_enable(null,null,true,true);
set timing on feedback on

declare
	L_cost number;
	L_sqls number := 0;
begin
	dbms_random.seed(0);

	L_sqls := L_sqls + 1;
	for a in (	select pkcol
			from accs 
			where rownum < 500
			order by dbms_random.value)
	loop
		L_sqls := L_sqls + 1;
		for t in (	select itmpkcol
				from txns
				where accpkcol = a.pkcol
				order by pkcol)
		loop
			L_sqls := L_sqls + 1;
			select sum(cost)
			into L_cost
			from itms
			where pkcol = t.itmpkcol;
		end loop;
	end loop;
	dbms_output.put_line('sqls='||L_sqls);
end;
/

select event,time_waited_micro / total_waits avg_wait_micro,total_waits,time_waited_micro from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like 'db%' order by time_waited ;

conn / as sysdba

set timing off feedback off
alter session set current_schema = acme;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=500000000;

alter system flush buffer_cache;

select value trace_name_col from v$diag_info where name = 'Default Trace File';
prompt ln -s /dev/null &trace_name.
host ln -s /dev/null &trace_name.

exec dbms_monitor.session_trace_enable(null,null,true,true);
set timing on feedback on

declare
	L_cost number;
	L_sqls number := 0;
begin
	dbms_random.seed(0);

	L_sqls := L_sqls + 1;
	for a in (	select pkcol
			from accs 
			where rownum < 500
			order by dbms_random.value)
	loop
		L_sqls := L_sqls + 1;
		for t in (	select itmpkcol
				from txns
				where accpkcol = a.pkcol
				order by pkcol)
		loop
			L_sqls := L_sqls + 1;
			select sum(cost)
			into L_cost
			from itms
			where pkcol = t.itmpkcol;
		end loop;
	end loop;
	dbms_output.put_line('sqls='||L_sqls);
end;
/

select event,time_waited_micro / total_waits avg_wait_micro,total_waits,time_waited_micro from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like 'db%' order by time_waited ;