概述
分享一些关于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'胜象大百科
