Ok. I'm still not over this. I know because it has been said on many disscusions before, that "High buffer cache hit ratio really does not mean
that your database is doing right or wrong, and low buffer cache hit ratio really does not mean that your database is doing right or wrong.". But the
thing is, our application team is complaining about this. They've been recieving Pinpoint alerts that the buffer quality is very low, always around 87%.
But when we checked the buffer quality on ST04(SAP), it shows that the buffer quality is 99%, which is considered acceptable.BTW we are also experiencing high
Physical Reads during this time.
Here is the action that we did:
-Since we based Pinpoint alerts, which are showing 87% of Buffer Quality, what we need did is we increased the db_cache_size.
After increasing db_cache_size, we looked at the pinpoint alerts, and nothing seem to change.But, the physical reads that we are getting was reduced.
By Looking into this, to get the buffer quality on ST04(SAP), we need to use this query:
A.
**Get the Buffer Cache Hit Ratio (Oracle) / Buffer Quality (SAP):
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2)||'%' "Buffer Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets from cache'
AND con.name = 'consistent gets from cache'
AND phy.name = 'physical reads cache';
Buffer Cache Hit Ratio
-----------------------------------------
97.11%
To get the buffer quality on Pinpoint alerts, we need to use this query:
B.
**Get the Buffer Hit Ratio:
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2)||'%' "Buffer Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads';
Buffer Hit Ratio
-----------------------------------------
92.92%
Finally, my question is:
1. How is A. different from B.?
2. How can I increase the result of B aka Buffer Hit Ratio?
3. What is the use of A?
4. What is the use of B?
*We are confused because we thought they were the same
Thanks so much for reading! Hope you guys could help! Sorry if this is very long, I tried to give as many details as possible.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production