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 Go to next message
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 #650095 is a reply to message #650094] Fri, 15 April 2016 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BHR is meaningless only its changes are, don't spend time on this subject, choose any formula, it does not matter.
If your performances tool is StatsPack then just follow StatsPack.
Note that you can see which formula StatsPack use just having a look to its code.

Re: What Is The Correct Formula For Buffer Cache Hit Ratio [message #650096 is a reply to message #650095] Fri, 15 April 2016 03:17 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Note that you can see which formula StatsPack use just having a look to its code.
=>I guess you are referring to spreport.sql which will eventually lead to sprepins.sql


BHR is meaningless only its changes are=>

are you referring to the changes in component that make up the buffer cache hit ratio?

simplifying
(y2-y1)/(x2-x1)

=>either (y2-y1) or (x2 -x1) as ratio hides the certain facts right? (2000-1000)/(2000-1000) maybe normal, but not (5000-2000)/(5000-2000)? I means is it the components (y2-y1) or (x2-x1) that matter, not the ratio right?

from https://jonathanlewis.wordpress.com/2007/09/26/hit-ratios-4/

Quote:


If you are worried by the high volume of physical reads then go and look for the largest sources of the physical reads. (Statspack/AWR: "SQL ordered by Reads")

If you are worried by the fact that the data buffer hit ratio is high when you have a necessarily high level of physical reads then go and look for the largest sources of logical I/Os. (Statspack.AWR: SQL ordered by Gets")



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

thanks a lot!
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: What Is The Correct Formula For Buffer Cache Hit Ratio [message #650116 is a reply to message #650097] Fri, 15 April 2016 10:11 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Yes I was given those task. The reason I asked about buffer cache hit ratio is because we have a program that calculate the buffer cache hit ratio which they calculate to be be 30%. However not only did they calculated wrongly. i.e they did not do a diff before calculating the ratio, they also failed to realize that ratio hides a lot of thing as Jonathan Lewis has pointed out.

I can't tell them bluntly that hey your program is build on wrong concepts right? I got to rely on documentation and MOS knowledge base to support me. Unfortunately some of the documentation and MOS knowledge base is also wrong.

from https://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#PFGRF94281, they never tell u that u need to do a diff before calculating the ratio.

Sometimes, when I open a MOS request, the engineer can also tell me to do the wrong things. So I have no choice to ask around whether my concept is correct or not.

many thanks for your reply
Re: What Is The Correct Formula For Buffer Cache Hit Ratio [message #650117 is a reply to message #650116] Fri, 15 April 2016 11:15 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I can't tell them bluntly that hey your program is build on wrong concepts right?

Not exactly. But you can ask them how many end users have telephoned them to complain that "the buffer cache hit ratio is too low".
Previous Topic: ASH data to identify CPU 100%
Next Topic: Performance Issue after upgrade to 11.2.0.4
Goto Forum:
  


Current Time: Thu Mar 28 18:47:46 CDT 2024