Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL package size and SGA

Re: PL/SQL package size and SGA

From: Mark D Powell <>
Date: 25 Jun 2004 07:02:35 -0700
Message-ID: <>

Saeed <> wrote in message news:<>...
> Two package's, pa_large and pa_small.
> Both have a procedure called pr_main, which does exactly the same,
> simple thing.
> Pa_small has just this procedure, but pa_large has very many more,
> giving it a total size of 300Kb, pa_small has a total size of around
> 1500Kb. However there is no difference between the pr_main procedure in
> the 2 packages.
> I was curious to learn how Oracle handles the two packages during
> execution of pr_main, given the huge difference in package size.
> I put the calls to pr_main in wrapper scripts that do a select on
> v$sgastat before and after (also during execution), this is on
> I also took care to flush the shared pool before each test. The database
> and server were totally isolated.
> I found that when executing pa_small.pr_main, the free SGA reduces by
> around 200kB.
> When the pa_large.pr_main is called, the SGA free reduces by about
> 500kB.
> So it appears the difference is account for by the size of package's. It
> also appears that there is an overhead of around 198Kb, perhaps due to
> the standard Oracle packages loading.
> Does anyone disagree with these findings?
> Kind regards,
> Saeed
> sr_ng 786

Saeed, the size requirements for your packages will vary depending if the packages require recompilation when loading. There is extra overhead that shows after a compile. If you are interested in stored object sizes take a look at dba_object_size.

AUT1 > desc dba_object_size

 Name                                      Null?    Type
 ----------------------------------------- --------
 OWNER                                     NOT NULL VARCHAR2(30)
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(13)
 SOURCE_SIZE                                        NUMBER
 PARSED_SIZE                                        NUMBER
 CODE_SIZE                                          NUMBER
 ERROR_SIZE                                         NUMBER

HTH -- Mark D Powell -- Received on Fri Jun 25 2004 - 09:02:35 CDT

Original text of this message