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 :
- 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.
- 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 CST