Home » RDBMS Server » Performance Tuning » What Is The Correct Formula For Buffer Cache Hit Ratio (11.2.0.4 SE, Redhat 7.1)
| What Is The Correct Formula For Buffer Cache Hit Ratio [message #650094] |
Fri, 15 April 2016 01:20  |
 |
juniordbanewbie
Messages: 250
Registered: April 2014
|
Senior Member |
|
|
Dear all,
from Doc ID 1039290.6 SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters
BEGIN
SELECT value
INTO :c
FROM v$sysstat
WHERE name = 'consistent gets';
SELECT value
INTO :d
FROM v$sysstat
WHERE name = 'db block gets';
SELECT ( 1 - value / ( :c + :d ) )
INTO :r
FROM v$sysstat
WHERE name IN ( 'physical reads' );
END;
/
SELECT global_name,
:r "Hit Ratio"
FROM global_name;
result
PERFSTAT@berlin>variable d number
PERFSTAT@berlin>variable c number
PERFSTAT@berlin>variable r number
PERFSTAT@berlin>begin
2 select value into :c
3 from v$sysstat
4 where name = 'consistent gets';
5 select value into :d
6 from v$sysstat
7 where name = 'db block gets';
8 select (1- value/( :c + :d)) into :r
9 from v$sysstat
10 where name in ( 'physical reads');
11
12 end;
13 /
PL/SQL procedure successfully completed.
PERFSTAT@berlin>
PERFSTAT@berlin>select global_name, :r "Hit Ratio" from global_name;
GLOBAL_NAME Hit Ratio
-------------------- ----------
berlin .340026174
however from https://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#PFGRF94281
SELECT 1 - ( ( pr.value ) / ( cg.value + dbc.value ) ) db_cache_hit_ratio
,
To_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR')
systimestamp_C
FROM (SELECT name,
value
FROM v$sysstat
WHERE name IN ( 'physical reads cache' )) pr,
(SELECT name,
value
FROM v$sysstat
WHERE name IN ( 'consistent gets from cache' )) cg,
(SELECT name,
value
FROM v$sysstat
WHERE name IN ( 'db block gets from cache' )) dbc;
result as follow
PERFSTAT@berlin>SELECT 1 - ((pr.value) / (cg.value + dbc.value)) db_cache_hit_ratio, to_CHAR(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') systimestamp_C FROM
2 (SELECT NAME, VALUE
3 FROM V$SYSSTAT
4 WHERE NAME IN ('physical reads cache')) pr,
5 (SELECT NAME, VALUE
6 FROM V$SYSSTAT
7 WHERE NAME IN ('consistent gets from cache')) cg,
8 (SELECT NAME, VALUE
9 FROM V$SYSSTAT
10 WHERE NAME IN ('db block gets from cache')) dbc;
DB_CACHE_HIT_RATIO SYSTIMESTAMP_C
------------------ --------------------------------------------------------------
.98378228 2016-04-15 05:57:29.328 +02:00
seems that there's a lot of difference between statistics with from cache and statistics without from cache
column name format a45
column value format 999,999,999,999.9999
column occur_datetime format a35
SET LINESIZE 110
SELECT stat.name, stat.value, to_char(snap.snap_time, 'YYYY-MM-DD HH24:MI:SS') snap_time_c FROM stats$sysstat stat
JOIN stats$snapshot snap
ON stat.snap_id = snap.snap_id
WHERE (name LIKE 'consistent gets%' OR name LIKE 'db block gets%' OR name LIKE 'physical reads%')
AND snap.snap_time >=SYSDATE-40/24/60 AND snap.snap_time <=SYSDATE
ORDER BY snap.snap_time, name;
output of statistics at each snap time is as follow
PERFSTAT@berlin>SELECT name, value, TO_CHAR(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF TZR') occur_datetime FROM v$sysstat WHERE name LIKE 'consistent gets%' OR name LIKE 'db block gets%' OR name LIKE 'physical reads%' ORDER BY name;
NAME VALUE OCCUR_DATETIME
--------------------------------------------- --------------------- -----------------------------------
consistent gets 16,558,615,921.0000 2016-04-15 05:57:29.918372 +02:00
consistent gets - examination 227,528,345.0000 2016-04-15 05:57:29.918372 +02:00
consistent gets direct 11,072,688,981.0000 2016-04-15 05:57:29.918372 +02:00
consistent gets from cache 5,485,926,940.0000 2016-04-15 05:57:29.918372 +02:00
consistent gets from cache (fastpath) 5,227,318,074.0000 2016-04-15 05:57:29.918372 +02:00
db block gets 362,565,711.0000 2016-04-15 05:57:29.918372 +02:00
db block gets direct 75,810.0000 2016-04-15 05:57:29.918372 +02:00
db block gets from cache 362,489,901.0000 2016-04-15 05:57:29.918372 +02:00
db block gets from cache (fastpath) 105,477,061.0000 2016-04-15 05:57:29.918372 +02:00
physical reads 11,167,536,971.0000 2016-04-15 05:57:29.918372 +02:00
physical reads cache 94,847,984.0000 2016-04-15 05:57:29.918372 +02:00
NAME VALUE OCCUR_DATETIME
--------------------------------------------- --------------------- -----------------------------------
physical reads cache prefetch 92,405,617.0000 2016-04-15 05:57:29.918372 +02:00
physical reads direct 11,072,688,987.0000 2016-04-15 05:57:29.918372 +02:00
physical reads direct (lob) 11,134.0000 2016-04-15 05:57:29.918372 +02:00
physical reads direct temporary tablespace .0000 2016-04-15 05:57:29.918372 +02:00
physical reads for flashback new .0000 2016-04-15 05:57:29.918372 +02:00
physical reads prefetch warmup 125,606.0000 2016-04-15 05:57:29.918372 +02:00
physical reads retry corrupt .0000 2016-04-15 05:57:29.918372 +02:00
physical reads 11,167,536,971.0000
physical reads cache 94,847,984.0000
db block gets 362,565,711.0000
db block gets from cache 362,489,901.0000
consistent gets 16,558,615,921.0000
consistent gets from cache 5,485,926,940.0000
so which url is correct? MOS Doc ID 1039290.6 SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters
OR 11.2 documentation https://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#PFGRF94281
how can I verified?, I don't see any buffer cache hit ratio in my statspack report
or is the below Buffer Hit equivalent to Buffer Cache hit?
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 Optimal W/A Exec %: 100.00
Library Hit %: 95.83 Soft Parse %: 97.12
Execute to Parse %: 23.65 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 85.00 % Non-Parse CPU: 97.54
while it is understandable that the difference in statistics between the two snapshots will give us the actual statistics that happen, i.e.
physical reads 11,167,535,979.0000 2016-04-15 05:30:14
physical reads 11,167,536,505.0000 2016-04-15 05:50:14
=>actual physical reads is 11,167,536,505.0000 - 11,167,535,979.0000= 526 reads
what is the meaning of ratio in a statspack report, does it give the ratio at the start of the snapshot or the ratio at the end of the snapshot?
or the ratio is (y2-y1)/(x2-x1)?
thanks a lot in advance!
|
|
|
|
|
|
|
|
| Re: What Is The Correct Formula For Buffer Cache Hit Ratio [message #650097 is a reply to message #650096] |
Fri, 15 April 2016 03:44  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Forgive me but it seems that you're searching for an answer without a question. It is a common thing.
Is there a problem and what are the symptoms? If there is not, have you been given one of those hideous, wishy-washy "please health check the database" tasks?
The thing with the cache is that is pretty much manages itself and really you want to be fixing problems, not chasing percentages. If the hit ratio is 50% and everything is fine, what good will taking it to 75% be? Equally, if it is 95% and you have a problem...you still have to fix the problem.
|
|
|
|
| Re: What Is The Correct Formula For Buffer Cache Hit Ratio [message #650102 is a reply to message #650096] |
Fri, 15 April 2016 04:38 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:so my next question is when do we know which item to check?
when do we check SQL ordered by Reads
when do we check SQL ordered by Gets Perhaps you are approachng this from the wrong direction. Rather than looking at a report to detect the problem SQLs (if there are any) you need to talk to the users. They will should tell you which SQLs (or which business processes, perhaps) are under performing. Is it, for example, a screen refresh that is too slow? Or an overnight batch job that doesn't finish till lunchtime? Without that precise information, you are just tilting at windmills.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 21:57:59 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
