Oracle 查看 Shared Pool 信息的相关脚本
关于Oracle SGA中Shared Pool的详细说明，参考我的blog：
http://blog.csdn.net/tianlesoftware/article/details/6560956
在上篇blog里，介绍了shared pool 的组成和一些原理， 也有一些脚本，在这篇blog里，在补充几个查看Shared Pool 的脚本。
From：http://vsbabu.org/oracle/sect13.html
一. Quick Check
/* Formatted on 2011/7/21 10:41:56(QP5 v5.163.1008.3004) */
SELECT 'You mayneed to increase the SHARED_POOL_RESERVED_SIZE'Description,
'RequestFailures = ' || REQUEST_FAILURES Logic
FROMv$shared_pool_reserved
WHEREREQUEST_FAILURES > 0
AND 0 != (SELECT TO_NUMBER (VALUE)
FROMv$parameter
WHERE NAME = 'shared_pool_reserved_size')
UNION
SELECT 'You maybe able to decrease the SHARED_POOL_RESERVED_SIZE'
Description,
'RequestFailures = ' || REQUEST_FAILURES Logic
FROMv$shared_pool_reserved
WHEREREQUEST_FAILURES < 5
AND 0 != (SELECT TO_NUMBER (VALUE)
FROMv$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
/* Formatted on 2011/7/21 10:44:32(QP5 v5.163.1008.3004) */
SELECT OWNER, NAME || ' - ' || TYPE object,SHARABLE_MEM
FROMv$db_object_cache
WHERESHARABLE_MEM > 10000
AND TYPE IN ('PACKAGE', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')
ORDER BYSHARABLE_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.
/* Formatted on 2011/7/21 10:45:24(QP5 v5.163.1008.3004) */
SELECT OWNER, NAME || ' - ' || TYPE object, LOADS
FROMv$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 theobject
（3）Executions - Total number oftimes this object has been executed
/* Formatted on 2011/7/21 10:46:15(QP5 v5.163.1008.3004) */
SELECT OWNER, NAME || ' - ' || TYPE object,EXECUTIONS
FROMv$db_object_cache
WHEREEXECUTIONS > 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
/* Formatted on 2011/7/21 10:48:52(QP5 v5.163.1008.3004) */
SELECT OWNER,
NAME,
DB_LINK,
NAMESPACE,
TYPE,
SHARABLE_MEM,
LOADS,
EXECUTIONS,
LOCKS,
PINS
FROMv$db_object_cache
ORDER BY OWNER, NAME
六. LibraryCache 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 thecache. 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
/* Formatted on 2011/7/21 10:58:02(QP5 v5.163.1008.3004) */
SELECTNAMESPACE,
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 thanSHARED_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.
/* Formatted on 2011/7/21 10:59:50(QP5 v5.163.1008.3004) */
SELECT NAME, VALUE
FROMv$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
/* Formatted on 2011/7/21 11:00:41(QP5 v5.163.1008.3004) */
SELECT NAME, TYPE, KEPT
FROMv$db_object_cache
WHERE KEPT = 'YES'
-------------------------------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823
DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
