0

We have recently added a lot of new packages to our system, and trying to determine incremental memory usage from a prior version of our product. I know I can get the sharable_mem value from v$db_object_cache. However, that will not include all packages in our schema, as they may be aged out.

Is there any way to determine the memory size of a package that is not in the object cache? And, I do not mean lines of source code (ALL_SOURCE), but size of whatever it is that oracle does under the hood to compile/parse the package.

Thanks!

|improve this question
  • dba_object_size ... generally parsed_size+code_size should give you an idea. and then you can join with db_object_cache to see what is missing, how much size etc. – Raj Oct 19 '17 at 17:21
  • Just out of curiosity, are you expecting that your code will consume a meaningful fraction of the available memory? I would be very surprised if the potential incremental increase in memory usage from code would be meaningful unless you're in a rather weird environment. – Justin Cave Oct 19 '17 at 18:30
  • That's a good question. I am not 100% if it would matter relative to all the other stuff in the SGA, so maybe I am worried about a non-problem. – OldProgrammer Oct 19 '17 at 18:42
2

Have a look at PL/SQL Program Limits

In the shared memory pool, a package specification, ADT specification, standalone subprogram, or anonymous block is limited to 67108864 (2^26) DIANA nodes which correspond to tokens such as identifiers, keywords, operators, and so on. This allows for ~6,000,000 lines of code unless you exceed limits imposed by the PL/SQL compiler, some of which are given in Table C-1.

I remember long time ago with Oracle 8i I "managed" once to get an PLS-00123: program too large error. At this time the limit was 64K DIANA nodes.

I think nowadays it is hardly possible to exceed the limit.

|improve this answer
0

If you use PL/SQL collections, then the amount of memory needed to run a stored procedure is entirely data driven. How much data are you caching with each stored procedure?

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.