call_vs_exec_spool.sql

call_vs_exec_spool.sql

SQL> 
SQL> create or replace procedure xt_proc(n in int, o out number) as
  2     dummy number:=0;
  3  begin
  4     for i in 1..n loop
  5        for j in 1..n loop
  6           dummy:=dummy+ln(i+j);
  7        end loop;
  8     end loop;
  9     o:=dummy;
 10  end;
 11  /

Procedure created.

SQL> var o number;
SQL> -- parsing:
SQL> call xt_proc(1,:o);

Call completed.

SQL> print o;

         O
----------
.693147181

SQL> exec xt_proc(1,:o);

PL/SQL procedure successfully completed.

SQL> print o;

         O
----------
.693147181

SQL> -- test:
SQL> call xt_proc(100,:o);

Call completed.

SQL> print o;

         O
----------
45050.8901

SQL> exec xt_proc(100,:o);

PL/SQL procedure successfully completed.

SQL> print o;

         O
----------
45050.8901

SQL> 
SQL> col sql_text for a30
SQL> select
  2    sql_text
  3   ,s.COMMAND_TYPE
  4   ,s.SHARABLE_MEM
  5   ,s.PERSISTENT_MEM
  6   ,s.RUNTIME_MEM
  7   ,s.executions
  8   ,s.END_OF_FETCH_COUNT
  9   ,s.ELAPSED_TIME
 10   ,s.ROWS_PROCESSED
 11  from v$sql s
 12  where upper(s.sql_text) like '%XT_PROC(100%'
 13    and lower(s.sql_text) not like '%v$sql%'
 14  /

SQL_TEXT                       COMMAND_TYPE SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS   EO_FTCHS ELAPSED_TIME ROWS_PROCESSED
------------------------------ ------------ ------------ -------------- ----------- ---------- ---------- ------------ --------------
BEGIN xt_proc(100,:o); END;              47        23989           1472         576          1          1       834809              1
call xt_proc(100,:o)                    170        15613           6192        4672          1          1       835200              0

2 rows selected.

SQL> spool off;