call_vs_exec.sql

call_vs_exec.sql

set feed on timing on echo on;
spool &_SPOOLS./call_vs_exec_spool.sql

create or replace procedure xt_proc(n in int, o out number) as
   dummy number:=0;
begin
   for i in 1..n loop
      for j in 1..n loop
         dummy:=dummy+ln(i+j);
      end loop;
   end loop;
   o:=dummy;
end;
/

var o number;
-- parsing:
call xt_proc(1,:o);
print o;
exec xt_proc(1,:o);
print o;
-- test:

call xt_proc(100,:o);
print o;
exec xt_proc(100,:o);
print o;

col sql_text for a30
select 
  sql_text
 ,s.COMMAND_TYPE
 ,s.SHARABLE_MEM
 ,s.PERSISTENT_MEM
 ,s.RUNTIME_MEM
 ,s.executions
 ,s.END_OF_FETCH_COUNT
 ,s.ELAPSED_TIME
 ,s.ROWS_PROCESSED
from v$sql s 
where upper(s.sql_text) like '%XT_PROC(100%'
  and lower(s.sql_text) not like '%v$sql%'
/
spool off;