Re: PL/SQL package size and SGA

Saeed <sr_ng_at_goawaynms-sys-lts.demon.co.uk> wrote in message news:<LMxoxVAxev2AFw6P_at_nms-sys-ltd.demon.co.uk>...
> 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 8.1.7.4.
> 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