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.
The following steps will demonstrate tuning of the Shared Pool:
CONNECT / AS SYSDBA
V$DB_OBJECT_CACHEdynamic 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;