jump to content

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'