Page Comparison

...

• Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html

• Code Block

  SELECT SUM(DECODE(name, 'consistent gets',value, 0)) "Consis Gets", SUM(DECODE(name, 'db block gets',value, 0)) "DB Blk Gets", SUM(DECODE(name, 'physical reads',value, 0)) "Phys Reads", (SUM(DECODE(name, 'consistent gets',value, 0)) + SUM(DECODE(name, 'db block gets',value, 0)) - SUM(DECODE(name, 'physical reads',value, 0))) / (SUM(DECODE(name, 'consistent gets',value, 0)) + SUM(DECODE(name, 'db block gets',value, 0)) ) * 100 "Hit Ratio" FROM v$sysstat;

• RAC External Results:

  • No Format
    Consis Gets DB Blk Gets Phys Reads Hit Ratio -------------- -------------- ------------- ----------------------------------------- 547094930 10815948 316183 99.94332732834795165976312080439485533745

• CAJE-Prod External Results:

  • No Format
    Consis Gets DB Blk Gets Phys Reads Hit Ratio -------------- -------------- ------------- ----------------------------------------- 1222124214 26405978 540427901 56.71487125719423531569671484564307596656

Looking at the Dictionary Cache

• The dictionary cache miss ratio should ideally be less than 1%, although when the database is first started, the miss ratio will be higher because each dictionary item loaded into memory will record a miss. If the miss ratio is greater than 2% and you have spare memory, increase your SHARED_POOL_SIZE. If the ratio has decreased, you should have improved your performance.

  • Code Block
    SELECT SUM(GETS) "Gets", SUM(GETMISSES) "Misses", TO_CHAR(SUM(getmisses) / SUM(gets) * 100 , '999.99')||'%' "Miss Ratio" FROM v$rowcache;

Views to access AWR data

• http://www.dba-oracle.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm

...