dbms_Shared_pool ( Performance )

dbms_shared_pool

Imagine a large object has to be loaded into the shared pool.

The database has to search for free space for the object.

If it cannot get enough contiguous space, it will free many small objects to satisfy the request.

If several large objects need to be loaded, the database has to throw out many small objects in the shared pool.

Finding candidate objects and freeing memory is very costly.  These tasks will impact CPU resources.

One approach to avoiding performance overhead and memory allocation errors is to keep large PL/SQL objects in the shared pool at startup time.This process is known as pinning.

This loads the objects into the shared pool and ensures that the objects are never aged out of the shared pool. If the objects are never aged out, then that avoids problems with insufficient memory when trying to reload them.


Pinning an object :  exec dbms_shared_pool.keep('owner.object');

View Pinned objects : select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';


How to Identify candidates that should be kept in the shared pool:

Step 1 :
select owner||'.'||name  Name ,
           type,
           sharable_mem,
           loads,
           executions,
           kept
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0
order by executions desc,loads desc,sharable_mem desc;

Step 2:

select * from x$ksmlru;

The x$ksmlru table keeps track of the current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.

KSMLRNUM  shows the number of objects that were flushed to load the large object
KSMLRISZ shows the size of the object that was loaded (contiguous memory allocated).

Analyze the x$ksmlru output to determine if there are any large allocations that are flushing other objects.
If this is the case, analyze the v$db_object_cache to identify the objects with high loads or executions.  These should be kept in the shared pool.