Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 30, 2006

Cost of Function Call

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 3:35 pm

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: