data dictionary cache 命中率

如果 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為0data

檢視 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 的大小