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
PROMPT Server Statisitics
PROMPT
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
),
2
) VALUE
FROM gv$sysstat group by inst_id
UNION ALL
SELECT 2, inst_id NODE,'Dictionary Hit Ratio',
(1 - (SUM (getmisses) / SUM (gets))) * 100 VALUE
FROM gv$rowcache group by inst_id
UNION ALL
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
UNION ALL
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
/

No comments:

topics