DBA Scripts - Very Useful - 13. Shared Pool Information

Here are some scripts related to Shared Pool Information .

Quick Check

SHARED POOL QUICK CHECK NOTES:

select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,

       'Request Failures = '||REQUEST_FAILURES  Logic

from        v$shared_pool_reserved

where       REQUEST_FAILURES > 0

and          0 != (

               select       to_number(VALUE) 

        from                v$parameter 

        where              NAME = 'shared_pool_reserved_size')

union

select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,

       'Request Failures = '||REQUEST_FAILURES Logic

from        v$shared_pool_reserved

where       REQUEST_FAILURES < 5

and          0 != ( 

               select       to_number(VALUE) 

               from        v$parameter 

               where       NAME = 'shared_pool_reserved_size')

Memory Usage

SHARED POOL MEMORY USAGE NOTES:

  Owner - Owner of the object

  Object - Name/namespace of the object

  Sharable Memory - Amount of sharable memory in the shared pool consumed by the object

select       OWNER,

               NAME||' - '||TYPE object,

               SHARABLE_MEM

from        v$db_object_cache

where       SHARABLE_MEM > 10000 

and          type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')

order        by SHARABLE_MEM desc

Loads

LOADS INTO SHARED POOL NOTES:

  Owner - Owner of the object

  Object - Name/namespace of the object

  Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.

select       OWNER,

               NAME||' - '||TYPE object,

               LOADS

from        v$db_object_cache

where       LOADS > 3 

and          type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')

order        by LOADS desc

Executions

SHARED POOL EXECUTION NOTES:

  Owner - Owner of the object

  Object - Name/namespace of the object

  Executions - Total number of times this object has been executed

select       OWNER,

               NAME||' - '||TYPE object,

               EXECUTIONS

from        v$db_object_cache

where       EXECUTIONS > 100 

and          type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')

order        by EXECUTIONS desc

Details

SHARED POOL DETAIL NOTES:

  Owner - Owner of the object

  Name - Name of the object

  DB Link - Database link name, if any

  Namespace - Namespace of the object

  Type - Type of the object

  Sharable Memory - Amount of sharable memory in the shared pool consumed by the object

  Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.

  Executions - Total number of times this object has been executed

  Locks - Number of users currently locking this object

  Pins - Number of users currently pinning this object

select       OWNER,

               NAME,

               DB_LINK,

               NAMESPACE,

               TYPE,

        SHARABLE_MEM,

        LOADS,

        EXECUTIONS,

        LOCKS,

        PINS

from        v$db_object_cache

order        by OWNER, NAME

Library Cache Statistics

SHARED POOL V$LIBRARYCACHE STATISTIC NOTES:

  Namespace - Library cache namespace (SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, PIPE)

  Gets - Number of times the system requests handles to library objects belonging to this namespace

  GetHits - Number of times the handles are already allocated in the cache. If the handle is not already allocated, it is a miss. The handle is then allocated and inserted into the cache.

  GetHit Ratio - Number of GETHITS divided by GETS. Values close to 1 indicate that most of the handles the system has tried to get are cached.

  Pins - Number of times the system issues pin requests for objects in the cache in order to access them.

  PinHits - Number of times that objects the system is pinning and accessing are already allocated and initialized in the cache. Otherwise, it is a miss, and the system has to allocate it in the cache and initialize it with data queried from the database or generate the data.

  PinHit Ratio - Number of PINHITS divided by number of PINS. Values close to 1 indicate that most of the objects the system has tried to pin and access have been cached.

  Reloads - Number of times that library objects have to be reinitialized and reloaded with data because they have been aged out or invalidated.

  Invalidations - Number of times that non-persistent library objects (like shared SQL areas) have been invalidated.

  GetHit Ratio and PinHit Ratio should be > 70

select       NAMESPACE,

               GETS,

               GETHITS,

               round(GETHITRATIO*100,2) gethit_ratio,

               PINS,

               PINHITS,

               round(PINHITRATIO*100,2) pinhit_ratio,

               RELOADS,

               INVALIDATIONS

from        v$librarycache

Reserve Pool Settings

SHARED POOL RESERVED SIZE NOTES:

  Parameter - Name of the parameter

  Value - Current value for the parameter

  shared_pool_reserved_size - Controls the amount of SHARED_POOL_SIZE reserved for large allocations. The fixed view V$SHARED_POOL_RESERVED helps you tune these parameters. Begin this tuning only after performing all other shared pool tuning on the system.

  shared_pool_reserved_min_alloc - Controls allocation for the reserved memory. To create a reserved list, SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC. Only allocations larger than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool's free lists. The default value of SHARED_POOL_RESERVED_MIN_ALLOC should be adequate for most systems.

select       NAME,

               VALUE

from        v$parameter

where       NAME like '%reser%'

Pinned Objects

PINNED OBJECT NOTES:

  Object Name - Name of the object

  Object Type - Type of the object (INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK)

  Kept Status - YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP

select       NAME,

               TYPE,

               KEPT

from        v$db_object_cache

where       KEPT = 'YES'