概述

分享一些关于Shared Pool 的脚本，仅供参考。

一、 Quick Check

SELECT 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
 'RequestFailures = ' || 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 maybe able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
 'RequestFailures = ' || 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:
--（1） Owner - Owner of the object
--（2） Object - Name/namespace ofthe object
--（3） Sharable Memory - Amount ofsharable 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', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')
 ORDER BY SHARABLE_MEM DESC

三、 Loads

--LOADS INTO SHARED POOL NOTES:
--（1）Owner - Owner of the object
--（2）Object - Name/namespace of theobject
--（3）Loads - Number of times theobject has been loaded. This count also increases when an object has beeninvalidated.
SELECT OWNER, NAME || ' - ' || TYPE object, LOADS
 FROM v$db_object_cache
 WHERE LOADS > 3
 AND TYPE IN ('PACKAGE', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')
 ORDER BY LOADS DESC

四、 Executions

--SHARED POOL EXECUTION NOTES:
--（1）Owner - Owner of the object
--（2）Object - Name/namespace of the object
--（3）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', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')
 ORDER BY EXECUTIONS DESC

五、Details

--SHARED POOL DETAIL NOTES:
--（1）Owner - Owner of the object
--（2）Name - Name of the object
--（3）DB Link - Database link name,if any
--（4）Namespace - Namespace of theobject
--（5）Type - Type of the object
--（6） Sharable Memory - Amount ofsharable memory in the shared pool consumed by the object
--（7）Loads - Number of times theobject has been loaded. This count also increases when an object has beeninvalidated.
--（8）Executions - Total number oftimes this object has been executed
--（9）Locks - Number of userscurrently locking this object
--（10）Pins - Number of userscurrently 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:
--（1） Namespace - Library cache namespace (SQL AREA, TABLE/PROCEDURE,BODY, TRIGGER, INDEX, CLUSTER, OBJECT, PIPE)
--（2） Gets - Number of times the system requests handles to libraryobjects belonging to this namespace
--（3） 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 thenallocated and inserted into the cache.
--（4） GetHit Ratio - Number of GETHITS divided by GETS. Values close to 1indicate that most of the handles the system has
-- tried to get are cached.
--（5） Pins - Number of times the system issues pin requests for objectsin the cache in order to access them.
--（6） PinHits - Number of times that objects the system is pinning andaccessing are already allocated and initialized in the 
-- cache. Otherwise, it isa miss, and the system has to allocate it in the cache and initialize it withdata queried from 
-- the database or generate the data.
--（7） PinHit Ratio - Number of PINHITS divided by number of PINS. Valuesclose to 1 indicate that most of the objects the 
-- system has tried to pin andaccess have been cached.
--（8） Reloads -Number of times that library objects have to be reinitialized and reloaded withdata because they have been
-- aged out or invalidated.
--（9） Invalidations - Number of times that non-persistent library objects(like shared SQL areas) have been invalidated.
--（10） 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:
--（1）Parameter - Name of theparameter
--（2）Value - Current value for theparameter
--（3）shared_pool_reserved_size -Controls the amount of SHARED_POOL_SIZE reserved for large allocations. 
-- Thefixed view V$SHARED_POOL_RESERVED helps you tune these parameters. Begin thistuning only 
-- after performing all other shared pool tuning on the system.
--（4）shared_pool_reserved_min_alloc - Controls allocation for the reserved memory. To create areserved list, 
--SHARED_POOL_RESERVED_SIZE must be greater thanSHARED_POOL_RESERVED_MIN_ALLOC. Only allocations larger 
--than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved listif a chunk of memory of sufficient 
--size is not found on the shared pool's freelists. The default value of SHARED_POOL_RESERVED_MIN_ALLOC 
-- should be adequatefor most systems.
SELECT NAME, VALUE FROM v$parameter WHERE NAME LIKE '%reser%'

八、 Pinned Objects

--PINNED OBJECT NOTES:
--（1）Object Name - Name of theobject
--（2）Object Type - Type of theobject (INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE,FUNCTION, PACKAGE, 
-- PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK)
--（3）Kept Status - YES or NO,depending on whether this object has been "kept" (permanently pinnedin memory) 
-- with the PL/SQL procedure DBMS_SHARED_POOL.KEEP
SELECT NAME, TYPE, KEPT FROM v$db_object_cache WHERE KEPT = 'YES'