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 believe if they are pinned by an executing session or by the keep procedure that the flush won't, so it depends what care you took.

>
> 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?

I think that is far too oversimplified given the dynamics of the pools.
See the stuff about heapdumps and x$ksmlru at http://www.ixora.com.au/q+a/pool.htm .

>
> Kind regards,
>
> Saeed
>
> sr_ng 786

jg

--
@home.com is bogus.
What a scam.  Who could possibly resist hearing what people are saying
behind their backs? 
http://groups.google.com/groups?q=share+experiences+website+group:*abuse*&hl=en&lr=&ie=UTF-8&scoring=d