Re: Oracle80.exe stack growth

A copy of this was sent to "Johan Wegener" <xjw_at_xdde.xdk> (if that email address didn't require changing) On Tue, 7 Sep 1999 11:52:06 +0200, you wrote:

>Hello,
>
>I am trying to understand how Oracle 8.0.4/NT handles PL/SQL procedure
>calls, particularly how procedure calls impacts the size of the Oracle80
>executable.
>
>On my small server, running the small (extreme) sample below increases the
>size of the Oracle80.exe (according to the task manager) from app. 6 MB in
>its dormant state to more than 76 MB during and after the execution. This
>means something like 80 bytes are allocated per procedure invokation! The
>sample performs 1 million recursive calls.
>
>So can anybody shed any light onto the following:
>
>* How does Oracle allocate the PL/SQL stack? It obviously does not use SGA
>memory for this. Does it use the hardware stack or does it maintain its own?
>

It uses SGA memory if you are running in multi-threaded server mode. It uses dynamically allocated memory in dedicated server mode.

plsql is an interpreted language. the stack is its own.

>* Are there any init.ora parameters that influences this?
>

setting up MTS will move the memory allocation into the SGA. this implies that you must preallocate all of the memory you want to use to the SGA upon startup as the SGA is a fixed chunk of ram.

dedicated server mode will dynamically allocate memory on demand in each thread.

>* Is it possible to "pin" the stack so that Oracle does not need to
>allocate/deallocate it dynamically?
>

No, even in MTS mode the stack will grow dynamically from the HEAP that is the SGA. You could fix the absolute size of all stacks by constraining the overall size of your SGA but then you are just asking for ORA-4031 errors.

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"

// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          dbms_shared_pool package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          INIT.ORA parameters "shared_pool_reserved_size" and 
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size".  

>* Can anybody point me to a web-site or book that explains how Oracle
>handles the PL/SQL stack?
>

It works like all other stacks -- bear in mind it is an interpreted language so its not as efficient as assembler. It varies from release to release as well.

In your example, the stack would contain the 2 local variables, instruction pointers, the plsql context (for exception and such to unwind with) and the return address when you call myfunc recursively.

As it is relatively rare to go hundreds of function calls deep. 80 bytes is really a very small number. If you are going to recursive millions of times (in ANY language) i would start looking for an interative solution (which will almost always be much more machine efficient -- maybe harder to code but it'll run faster and you'll have a fine degree of control over the memory allocations)

>declare
> nMax pls_integer := 1000000;
> n pls_integer := 0;
> procedure myfunc
> is
> begin
> n := n + 1;
> if n >= nMax
> then
> return;
> else
> myfunc;
> end if;
> end myfunc;
>begin
> myfunc;
>end;
>
>Any comment is appreciated, thanks,
>Johan
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 07 1999 - 08:21:29 CDT