当前位置: 代码迷 >> 综合 >> data dictionary cache 命中率
  详细解决方案

data dictionary cache 命中率

热度:84   发布时间:2023-12-14 21:43:26.0

如果 shared pool 设置过小,那么 data dictionary cache 也会相应的过小,没有足够的空间存储ORACLE的系统数据字典信息,从而导致ORACLE需要从硬盘读取数据字典信息,我们查看执行计划的时候看到的recursive calls就是代表的是从磁盘读取数据字典的次数。

SQL> select count(*) from dba_source;

  COUNT(*)
----------
    228222
Statistics
----------------------------------------------------------
        312  recursive calls
          0  db block gets
        888  consistent gets
        538  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select count(*) from dba_source;

  COUNT(*)
----------
    228222
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        818  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
可以看到第一次没有访问数据字典,所以recursive calls等于312,第二次,由于缓存了数据字典信息,所以recursive calls为0 data

查看 data dictionary cache 的命中率
SQL> select sum(gets),sum(getmisses),(1-(sum(getmisses)/(sum(gets)+sum(getmisses)))) hitratio from v$rowcache;

 SUM(GETS) SUM(GETMISSES)   HITRATIO
---------- -------------- ----------
    264014          15600 .944208802

查看data dictionary cache 的大小
SQL> select sum(sharable_mem) from v$sqlarea;

SUM(SHARABLE_MEM)
-----------------
         14354029

如果data dictionary cache的命中率小于95%,应该适当增加shared pool 的大小

  相关解决方案