Oracle 查看 Shared Pool 信息的相关脚本

关于Oracle SGA中Shared Pool的详细说明，参考我的blog：

Oracle Shared pool 详解

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表空间和数据文件的关系，否则拒绝申请