scripts related to Shared Pool Information

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'