Oracle Database 11gR2 Performance Tuning Cookbook

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