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
/
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 © www.sap-img.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.sap-img.com 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
www.sap-img.com or the content authors.