Cost of Function Call

If you’re calling a routine frequently and that routine contains constants then store them in variables;

set serveroutput on

declare

procedure step_1 is
BEGIN
for i in 1 .. 1000000 loop
if sysdate+i/10000 > TO_DATE(’18/02/1977′, ‘DD/MM/YYYY’) then
null;
end if;
end loop;
end;

procedure step_2 is
l_date_1 date := TO_DATE(’18/02/1977′, ‘DD/MM/YYYY’);
l_date_2 date := sysdate;
BEGIN
for i in 1 .. 1000000 loop
if l_date_2+i/10000 > l_date_1 then
null;
end if;
end loop;
end;

begin
runstats_pkg.rs_start;

step_1;

runstats_pkg.rs_middle;

step_2;

runstats_pkg.rs_stop(1000);
end;
/

Run1 ran in 1047 hsecs
Run2 ran in 147 hsecs
run 1 ran in 712,24% of the time

Name Run1 Run2 Diff
STAT…session pga memory 65,536 0 -65,536

Run1 latches total versus runs — difference and pct
Run1 Run2 Diff Pct
1,032 470 -562 219.57%

PL/SQL procedure successfully completed.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
http://asktom.oracle.com/tkyte/runstats.html
http://www.oracledba.co.uk

Advertisement