Re: Pinning (keeping) Packaged Procedures in the Shared Pool

I do not remember if the sys.dbms_shared_pool package will work on 7.0.16, but even on 7.1.3 it does not actually pin the complete package into memory. It actually only loads the specification so you need to load the entire package into memory. I suggest you seriously consider pinning then loading all large packages into the shared pool on startup by executing a dummy procedure. You could compile each package, but the dummy procedure is faster. Execution of an invalid package will cause it to recompile and valid packages will be loaded into the pool much quicker by execution.

As for the size differences of the same package when viewed by different schemas: 1) When a package is compiled there is overhead attached that is not there when the package is just loaded into memory 2) Every package involves sharable memory and private memory for each using process. I would be willing to bet that part of the difference is just this. Also there is a v$view that will give you four sizes for every package, source, compiled, sharable memory and something else but I can not remember right now and my manual is at work.

Hope this helps. Just try the package to see if it works. I believe Oralce back ported it.

Mark Powell -- No witty disclaimer Received on Mon Aug 12 1996 - 00:00:00 CEST