SHARED_POOL_SIZE指定了共享池的大小，在10g R2中，该参数默认为0，表示由Oracle自动分配。

共享池缓存了共享SQL语句、数据字典缓存、库缓存，使Oracle能快速解析该对象。

不同的SQL语句，即使包括大小写不同、多余的空格数，也被视作不同的SQL语句，而无法使用缓存。除非参数CURSOR_SHARING被指定为SIMILAR或FORCE。

查询数据字典命中率（V$ROWCACHE），应该维持在95%以上。

点击(此处)折叠或打开

select ((1 - (Sum(GetMisses) / (Sum(Gets) + Sum(GetMisses)))) * 100) "Hit Rate"
from V$RowCache
where Gets + GetMisses <> 0;

    Hit Rate
    ----------
    96.1514775

查询详细的数据字典缓存（行缓存），这个查询会对丢失率大于10%的条目前加上星号*。

点击(此处)折叠或打开

column parameter format a20 heading 'Data Dictionary Area'
column gets format 999,999,999 heading 'Total|Requests'
column getmisses format 999,999,999 heading 'Misses'
column modifications format 999,999 heading 'Mods'
column flushes format 999,999 heading 'Flushes'
column getmiss_ratio format 9.99 heading 'Miss|Ratio'
set pagesize 50
ttitle 'Shared Pool Row Cache Usage'
select parameter, gets, getmisses, modifications, flushes,
(getmisses / decode(gets,0,1,gets)) getmiss_ratio,
(case when (getmisses / decode(gets,0,1,gets)) > .1 then '*' else ' ' end) " "
from v$rowcache
where Gets + GetMisses <> 0;

Total Miss
Data Dictionary Area Requests Misses Mods Flushes Ratio
-------------------- ------------ ------------ -------- -------- ----- --
dc_segments 8,577 936 43 43 .11 *
dc_tablespaces 32,241 7 0 0 .00
dc_tablespace_quotas 11 1 0 0 .09
dc_files 35 5 0 0 .14 *
dc_users 79,066 26 4 4 .00
dc_rollback_segments 4,361 21 21 20 .00
dc_objects 14,419 1,820 93 36 .13 *
dc_global_oids 35,617 85 0 0 .00
dc_object_ids 64,736 1,088 56 0 .02
dc_sequences 32 8 32 32 .25 *
dc_usernames 3,963 18 0 0 .00
dc_histogram_defs 32,068 6,850 1,608 1,606 .21 *
dc_profiles 712 2 0 0 .00
outstanding_alerts 791 126 225 225 .16 *
dc_awr_control 436 1 15 15 .00
dc_object_grants 433 116 0 0 .27 *
dc_histogram_data 3,961 250 0 0 .06
dc_histogram_data 5,092 59 0 0 .01
dc_users 1,938 20 0 0 .01

库缓存中包括了对SQL语句和执行计划的缓存信息。

库缓存的重载率，表示缓存对象之前在库缓存中，但过时后被移除，然后也装入库缓存。库缓存的重载率应不高于1%，否则可以考虑增大SHARED_POOL_SIZE。

点击(此处)折叠或打开

select Sum(Pins) "Hits",
Sum(Reloads) "Misses",
((Sum(Reloads) / Sum(Pins)) * 100) "Reload %"
from V$LibraryCache;

Hits Misses Reload %
---------- ---------- ----------
370575 3636 .981177899

查看库缓存的命中率，应不低于99%

点击(此处)折叠或打开

select Sum(Pins) "Hits",
Sum(Reloads) "Misses",
Sum(Pins) / (Sum(Pins) + Sum(Reloads)) "Hit Ratio"
from V$LibraryCache;

Hits Misses Hit Ratio
---------- ---------- ----------
382526 4142 .989287968

查看库缓存详细信息，关注pinhitratio，pinhits与pins的比率，该数应接近1。

点击(此处)折叠或打开

set numwidth 3
set space 2
set newpage 0
set pagesize 58
set linesize 80
set tab off
set echo off
ttitle 'Shared Pool Library Cache Usage'
column namespace format a20 heading 'Entity'
column pins format 999,999,999 heading 'Executions'
column pinhits format 999,999,999 heading 'Hits'
column pinhitratio format 9.99 heading 'Hit|Ratio'
column reloads format 999,999 heading 'Reloads'
column reloadratio format .9999 heading 'Reload|Ratio'
spool cache_lib.lis
select namespace, pins, pinhits, pinhitratio, reloads,
reloads/decode(pins,0,1,pins) reloadratio
from v$librarycache;

Hit Reload
Entity Executions Hits Ratio Reloads Ratio
-------------------- ------------ ------------ ----- -------- ------
SQL AREA 228,021 221,726 .97 1,563 .0069
TABLE/PROCEDURE 102,214 93,735 .92 2,414 .0236
BODY 48,768 48,596 1.00 74 .0015
TRIGGER 3,036 2,986 .98 22 .0072
INDEX 1,044 779 .75 57 .0546
CLUSTER 597 576 .96 13 .0218
OBJECT 0 0 1.00 0 .0000
PIPE 0 0 1.00 0 .0000
JAVA SOURCE 0 0 1.00 0 .0000
JAVA RESOURCE 0 0 1.00 0 .0000
JAVA DATA 0 0 1.00 0 .0000

总结：当字典缓存命中率<95%，或库缓存重载率>1%，可以考虑增大SHARED_POOL_SIZE。

ITPub博客

调整SHARED_POOL_SIZE参数

最新文章