Tuning the Shared Pool

In the previous recipe, we have seen how to inspect and tune the Library Cache, which is a part of the Shared Pool. In this recipe, we will see the memory structures in the Shared Pool and how we can tune it by keeping PL/SQL blocks in it.

How to do it...

The following steps will demonstrate tuning of the Shared Pool:

1. Connect to the database as SYSDBA:

   CONNECT / AS SYSDBA
   

2. Inspect which objects can be shared by querying the V$DB_OBJECT_CACHE dynamic performance view:

   COL OWNER FOR A20
   COL NAME FOR A30
   COL TYPE FOR A20
   SELECT OWNER, NAME, TYPE, SHARABLE_MEM
   FROM V$DB_OBJECT_CACHE
   WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE',
     'FUNCTION', 'TRIGGER')
   AND KEPT = 'NO'
   ORDER BY SHARABLE_MEM;
   

3. Force a package to be kept in the shared pool:

   EXEC SYS.DBMS_SHARED_POOL.KEEP('SYS.DBMS_SCHEDULER');
   

4. Show the objects in the shared pool with a certain size:

   SET SERVEROUTPUT ON
   EXEC SYS.DBMS_SHARED_POOL.SIZES(500);
   

5. Inspect the shared pool reserved memory:

   SELECT * FROM V$SHARED_POOL_RESERVED...