O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required