conn / as sysdba
grant select on v_$mystat to scott;
grant select on v_$statname to scott;

conn scott/tiger

set serveroutput on
set timing on

select value from v$mystat 
where statistic# = (select statistic# from v$statname where name='session pga memory');
select value from v$mystat 
where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');


declare 
type big_aa_t is table of varchar2(10) index by pls_integer;
v_aa big_aa_t;
n number;
begin
for i in 1..100000000 loop
v_aa(i) := '1234567890';
if mod(i,1000000)=0 then
select value into n from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');
n:=round(n/1000000);
dbms_output.put_line('Array has '||i||' elements, PGA is now '||n||'MB');
end if;
end loop;
end;
/


create global temporary table gtt1(c1 varchar2(10));
declare n number;
begin 
for i in 1..100 loop
insert into gtt1 select '1234567890' from dual connect by level <= 1000000;
select value into n from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');
n:=round(n/1000000);
dbms_output.put_line('GTT has '||i||' million rows, size is now '||n||'MB');
end loop;
end;
/

--2 mins 18 seconds, 1805MB temp space used

alter session set temp_undo_enabled=true;
--any difference?