estimate package size in shared_pool

how do i estimate the size of my package that is gonna to allocate the shared_pool memory?

select name, sum(vsize(text)) BYTES from user_source
where type in ('PACKAGE','PACKAGE BODY')
group by name;

Quote:

---

Originally posted by julian
select name, sum(vsize(text)) BYTES from user_source
where type in ('PACKAGE','PACKAGE BODY')
group by name;

---

Hey, Julian, he is not asking for the size of his source code! The size of the source code can by no coincidence be a measure of the required memory in shared pool! I can easily write a package that would yield hundreds of kilobytes by your query, yet it will require only few bytes of sharable memory to run.

Saying this I don't know of any method of how to estimate the required memory for a package or procedure. I gues the simplest and easiest method would be to simply load that package/procedure in the shared pool and see the exact required memory size in V$DB_OBJECT_CACHE.

Yes, correct. If he wants SHARABLE_MEM , then V$DB_OBJECT_CACHE is the view. I have a package called julian. I did execute it, and here is the result:

1* select * from V$DB_OBJECT_CACHE where name like '%JULIAN%' and type = 'PACKAGE BODY'

OWNER
---------
NAME
--------------------------------------------------------------------------------------------------------------
DB_LINK NAMESPACE TYPE
---------------------------------------------------------------- ---------------------------- ----------------
SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
------------ ---------- ---------- ---------- ---------- ---
ADS
JULIAN
BODY PACKAGE BODY
14710 29 26 1 0 NO