Re: Oracle internal memory management

From: Simon Kelly <simon_at_skelly.demon.co.uk>
Date: 1996/10/29
Message-ID: <01bbc5eb$9c1aa940$be3adec2_at_skelly.demon.co.uk>#1/1


Chris,

        The problem you are experiencing is likely to be caused by shared pool fragmentation. As the shared pool space is used and freed, it is not automatically "sewn" back into contiguous chunks.

Ways of discouraging fragmentation are :

  1. Using bind variables instead of vaules in SQL will help increase reuse of parsed SQL and will hence decrease the ageing out of objects in the shared pool.
  2. Pinning frequently used packages (especially large ones) into the shared pool so that they never get aged out will help. To do this, you can use the procedure dbms_shared_pool.keep('package name') on database startup for each package that is used a lot. You can monitor the shared pool by looking at v$db_object_cache to find the large frequently used packages. You may need to increase the shared pool size to accomodate the packages that you pre-load so that there is sufficient space left for other packages to get loaded and aged out as normal.

Hope this helps.

Regards....Simon Received on Tue Oct 29 1996 - 00:00:00 CET

Original text of this message