Memory Management in PLSQL

While working with PLSQL we should take care of memory usage, So I will provide some tips to avoid memory overhead in PLSQL code.

1- Variables Length
You might  allocate large VARCHAR2 variables when you are not sure how big an expression result will be.
You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000.

PLSQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. 
When you specify a size of more than 4000 characters for the VARCHAR2 variable, PLSQL waits until you assign the variable, then only allocates as much storage as needed.

2- Subprograms into Packages
When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool.
Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package ages out of memory, and you reference it again, it is reloaded.

You can improve performance by sizing the shared memory pool correctly. Make it large enough to hold all frequently used packages, but not so large that memory is wasted.

3- Pin Packages in the Shared Memory Pool
You can pin frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it does not age out; it remains in memory no matter how full the pool gets or how frequently you access the package.

4- Use Compiler Warnings
The PLSQL compiler issues warnings about things that do not make a program incorrect, but might lead to poor performance. If you receive such a warning, and the performance of this code is important, follow the suggestions in the warning and make the code more efficient.

Thanks
Mahmoud A. El-Sayed