Keeping PL/SQL in Oracle Memory

If an application calls a BIG pl/sql block into a memory, it may result in kicking out several other cached SQL statements because of LRU (least recently used) algorithm. Now the subsequent call will increase the 'reloads'. That's where the reserved area of shared pool comes in. This area is called Shared Pool Reserved Area which is set by SHARED_POOL_RESERVED_SIZE parameter. The size of the reserved pool can be up 50 % of the shared_pool_size.

We can also get a help on properly setting the shared_pool_reserved_size  by querying the dynamic performance view called V$SHARED_POOL_RESERVED as below

SQL>SELECT free_space,request_misses, request_failures
2        FROM v$shared_pool_reserved;

FREE_SPACE            REQUEST_MISSES        REQUEST_FAILURES
-------------------------------------------------------------------------------------------------------
37181672             0                                             1

 If you see the following then probably your reserved_pool_size is over congigured

1. Request_misses shows constantly 0 or static
2. The value shown in FREE_SPACE is more than 50 % of the shared_pool_size
3. Any non-zero or steady increase size of REQUEST_FAILURE shows that reserved area is too small.

We can use the below script to find out the size of shared_pool and see if the FREE_SPACE is more than 50 % of it.

SQL> SELECT pool, sum(bytes) "SIZE"

  2  FROM v$sgastat

  3  WHERE pool = ’shared pool’

  4 GROUP BY pool;

POOL              SIZE

----------- ---------------------

shared pool  838860800

From that we know that our free_space column value in v$SHARED_POOL_RESERVED is less than 50 % of the shared pool. So we are good here!

Now the process of adding the  PL/SQL code permanently into the memory is call pinning. Once pinned, the PL/SQL code will remain in a portion of shared pool allocated by shared_pool_reserved parameter until the instance is bounced.

Since, now we have the basic understanding of shared pool reserved area and reasons for its configuration, lets now configure it in two simple steps

1.  Build DBMS_SHARED_POOL package as its not installed by default by running dbmspool.sql script

SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

2. Use the dbms_shared_pool two procedures (KEEP or UNKEEP) to pin the object in memory as below

SQL> EXECUTE DBMS_SHARED_POOL.KEEP (‘PREOCEDURE_TO_BE_PINNED’);

Once done you can verify your steps above by query the below mentioned view

SQL> SELECT owner, name, type

  2  FROM v$db_object_cache

  3  WHERE kept = ’YES’;