Performance Standalone Procedure vs Packaged Procedure in Oracle

oracle, stored-procedures

Solution

Tom says:

Always use a package. Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing)

There you can also find a very good discussion about their performance. Just search for "performance" on that page. If still seriously in doubt, you can always test yourself which one is faster. You'll certainly learn something new by doing so.

My take on your question: while it's true that calling package procedures/functions seems to be slower in certain situations than calling standalone procedures/functions, the advantages offered by the additional features available when using packages far outweigh the performance loss. So, just like Tom puts it, use packages.

The link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537

Test code(20 million calls, runstats_pkg is a package I wrote based on the runstats package by Tom Kyte):

CREATE OR REPLACE PACKAGE testperf AS
   FUNCTION pow(i INT) RETURN INT;
END;
/

CREATE OR REPLACE PACKAGE BODY testperf AS
   FUNCTION pow(i int) RETURN INT AS
   BEGIN
      RETURN i * i;
   END;
END;
/

CREATE OR REPLACE FUNCTION powperf(i INT) RETURN INT AS
BEGIN
   RETURN i * i;
END;
/

DECLARE
   I INT;
   S INT DEFAULT 0;
BEGIN
   runstats_pkg.start1;
   FOR I IN 1 .. 20000000 LOOP
      s := s + (powperf(i) / i);
   END LOOP;
   runstats_pkg.stop1;

   dbms_output.put_line(s);
   s := 0;

   runstats_pkg.start2;
   FOR I IN 1 .. 20000000 LOOP
      s := s + (testperf.pow(i) / i);
   END LOOP;
   runstats_pkg.stop2;

   dbms_output.put_line(s);

   runstats_pkg.show;
END;

Results(Oracle XE):

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
       2,491       2,439         -52    102.13%

Run1 ran in 2304 hsecs
Run2 ran in 2364 hsecs
run 1 ran in 97.46% of the time

Results(Oracle 11g R1, different machine):

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
       2,990       3,056          66     97.84%

Run1 ran in 2071 hsecs
Run2 ran in 2069 hsecs
run 1 ran in 100.1% of the time

So, there you go. Really not much of a difference. Want data for something more complex that also involves SQL DML? You gotta test it yourself.

Problem

Original source