概述

分享一些关于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')UNIONSELECT '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 objectSELECT 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 executedSELECT 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 objectSELECT 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 > 70SELECT 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.KEEPSELECT NAME, TYPE, KEPT FROM v$db_object_cache WHERE KEPT = 'YES'

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~