Performance Standalone Procedure vs Packaged Procedure in Oracle

Question

What is the difference of performance between standalone procedure and packaged procedure? Which will be good performance wise and why? Is there any difference in execution of both?

Answer 1

There should be no difference between the two.

A major use of packages is to group a set of similar/associeted functions+procedures

Answer 2

There isn't a performance difference except that packages can have state and standalone procedures and functions not.

The use of package is more about ordening and grouping of code. You could see them as an alternative of namespaces.

Answer 3

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.

Answer 4

The primary reason to use packages is they break the dependency chain. For instance if you have two stand-alone procedures, procedure A which calls procedure B and you recompile procedure B you will also need to recompile procedure A. This gets quite complicated as you increase the number of procedures and functions.

If you move these to two to different packages you will not need to recompile them as long as the specification does not change.

Answer 5

The other answers here are all good (e.g. packages have state, they separate interface from implementation, etc).

Another difference is when procedures or packages are wrapped - it is simple to unwrap a procedure, but not a package body.