Shared Pool Information For Tuning

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'