shared_pool关于dictionary cache

Shared Pool: Dictionary Cache Statistics
shared pool：字典内存统计信息

Typically, if the shared pool is adequately sized for the library cache, it will also be
adequate for the dictionary cache data.
标准情况下，如果共享池的大小对于library cache 的大小是充足的，它对于Library
cache 的大小也是充足的。

Misses on the data dictionary cache are to be expected in some cases. On instance
startup, the data dictionary cache contains no data.

在dictionary cache上的丢失在某些情况下将会发生。当实例刚刚启动的时候，data dictionary
没有包含任何数据。

 Therefore, any SQL statement issued is likely to result in cache misses.
As more data is read into the cache, the likelihood of cache misses decreases.

因此，任何申明的sql语句都将导致 cache 丢失。当越来越多的数据读入
cache，cahce丢失的可能性将会减少。

Eventually, the database reaches a steady state, in
which the most frequently used dictionary data is in the cache. At this point, very few
cache misses occur.

最终的，数据库将到达一个稳定的状态，在这种情况下，最近常使用的的数据字典信息
都将保存在cache中。这个时间点，会有非常少的 cache丢失。

Each row in the V$ROWCACHE view contains statistics for a single type of data
dictionary item. These statistics reflect all data dictionary activity since the most recent
instance startup. The columns in the V$ROWCACHE view that reflect the use and
effectiveness of the data dictionary cache are listed in Table 7–2.
在v$ROWCACHE视图中的每行包含了某个种类的字典对象的统计信息。这些信息
将反映最近数据库实例启动以来所有的数据字典活动信息。

Use the following query to monitor the statistics in the V$ROWCACHE view over a
period of time while your application is running. The derived column PCT_SUCC_
GETS can be considered the item-specific hit ratio:
column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;

使用下面的查询信息来监视v$rowcache视图下的统计信息，当你的应用程序
运行了一段时间之后。PCT_SUCC_GETS列可以得出某个对象的 命中率。
得到的查询结果类似下面：

The output of this query will be similar to the following:
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
--------------------- ---------- -------------- ------------- ------------
dc_database_links 81 1 98.8 0
dc_free_extents 44876 20301 54.8 40,453
dc_global_oids 42 9 78.6 0
dc_histogram_defs 9419 651 93.1 0
dc_object_ids 29854 239 99.2 52
dc_objects 33600 590 98.2 53
dc_profiles 19001 1 100.0 0
dc_rollback_segments 47244 16 100.0 19
dc_segments 100467 19042 81.0 40,272
dc_sequence_grants 119 16 86.6 0
dc_sequences 26973 16 99.9 26,811
dc_synonyms 6617 168 97.5 0
dc_tablespace_quotas 120 7 94.2 51
dc_tablespaces 581248 10 100.0 0
dc_used_extents 51418 20249 60.6 42,811
dc_user_grants 76082 18 100.0 0
dc_usernames 216860 12 100.0 0
dc_users 376895 22 100.0 0

Examining the data returned by the sample query leads to these observations:
■ There are large numbers of misses and updates for used extents, free extents, and
segments. This implies that the instance had a significant amount of dynamic
space extension.
■ Based on the percentage of successful gets, and comparing that statistic with the
actual number of gets, the shared pool is large enough to store dictionary cache
data adequately.

检查上面的实例查询我们可以得到下面的结论：
1>在已经使用的扩展，空闲的扩展，以及段上，都存在大量的丢失和跟新。
这暗示数据库实例有大量的动态空间扩展。
2>基于成功的获取的比例，那这些信息和实际的获得次数，share pool 有足够大，
并足够储存dictionary cache 数据。

It is also possible to calculate an overall dictionary cache hit ratio using the following
formula; however, summing up the data over all the caches will lose the finer
granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

我们也可以用下面的公式来计算整个的dictionary cache命中率，然而，在所有的
cache中统计数据将会丢失数据的好的区分度：
select (SUM(GETS-GETMISSES-FIXED))/sum(GETS) "ROW CACHE" FROM V$ROWCACHE;