PL/SQL package size and SGA

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 Received on Thu Jun 24 2004 - 10:48:01 CDT