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!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 03 14:01:08 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
