How Can We Calculate Hit Ratios

How can we calculate hit ratios in a cluster environment? I mean RAC.
Because each instance has its own parameter file, and 2 or more instance will be attached to one database.
So the hit ratios you will get from database dictionary is of which node.

In RAC, You can get the information from gv$views (gv$librarycache / gv$sysstat ) etc
But, Hit ratios like buffer cache Hit ratio are not a valid UNIT of measure for performance.
It is just JUNK data.  Programmatically, you can get any hitratio you want.

For RAC env please use this scripts to find stats, but is useless to use stats for any pratical purpose.
Please try to use oracle OWI for any practical tuning purpose.

SET echo off
SET feedback off
SET linesize 512
PROMPT Server Statisitics
COLUMN dummy      noprint
COLUMN value     format 999.99
COLUMN  statname     format a30 heading 'Statistics Name'
COLUMN NODE format a10

BREAK on report

SELECT 1 dummy,decode(inst_id,1,'taurus','libra') NODE, 'Buffer Cache Hit Ratio' statname,
      ROUND ((  (  1
                 - (  SUM (DECODE (NAME, 'physical reads', VALUE, 0))
                    / (  SUM (DECODE (NAME, 'db block gets', VALUE, 0))
                       + (SUM (DECODE (NAME, 'consistent gets', VALUE, 0)))
              * 100
            ) VALUE
 FROM gv$sysstat group by inst_id
SELECT 2, inst_id  NODE,'Dictionary Hit Ratio',
      (1 - (SUM (getmisses) / SUM (gets))) * 100 VALUE
 FROM gv$rowcache group by inst_id
SELECT 3, decode(inst_id,1,'taurus','libra') NODE,'Library Cache Get Hit Ratio', SUM (gethits) / SUM (gets)
                                        * 100 VALUE
 FROM gv$librarycache group by inst_id
SELECT 4, decode(inst_id,1,'taurus','libra') NODE,'Library Cache Get Pin  Ratio',
      SUM (pinhits) / SUM (pins) * 100 VALUE
 FROM gv$librarycache group by inst_id

Quick Links:
Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Best Regards,
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.