Unsolved
This post is more than 5 years old
28 Posts
0
1261
January 29th, 2012 14:00
Oracle % read miss calculation
I've been looking into how to calculate a % read miss ratio, inclusive of cache and direct reads. Such a % read miss ratio is good for understanding the effectiveness of introducing EFDs (SSDs) technologies. The greater the read miss % the more effective EFDs might be enhancing the performance of Oracle database data and indexes stored on EFDs. Other factors are, of course, involved, such as the average IO read block size; EFDs show their best performance gain with small-block random reads.
Starting insight is how Oracle recommends calculating the cache hit rate:
Form Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Part Number B14211-03
The cache hit rate =
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
Where
consistent gets from cache |
Number of times a consistent read was requested for a block from the buffer cache. |
db block gets from cache |
Number of times a CURRENT block was requested from the buffer cache. |
physical reads cache |
Total number of data blocks read from disk into buffer cache. |
Over time Oracle has differentiated “from cache” vs. “direct.”
With logical reads = consistent gets + db block gets
= consistent gets from cache + consistent get direct + db block gets from cache + db block gets direct
Ditto, physical reads = physical reads from cache + physical reads direct
Having looked at a few AWR/Statspack reports these number add up, given half a decimal point or so.
We end up with:
consistent gets = consistent gets from cache + consistent get direct
db block gets = db block gets from cache + db block gets direct
and logical reads = consistent gets + db block gets
So it looks like a % read misses rate should be:
% Percentage physical reads to total reads = 100*(physical reads per sec / (logical reads per sec + physical reads per sec).
The % read miss here captures both cache reads and direct reads.
BG
WBGaynor
28 Posts
0
January 29th, 2012 14:00
To be clear this is % read misses out of total reads, where total reads consists of hits and misses.
bg
LouisLu
161 Posts
0
January 29th, 2012 18:00
The deduction explain the read misses rate well. I like it.
The only concern is the
% Percentage physical reads to total reads = 100*(physical reads per sec / logical reads per sec) seems more closer to original value
The cache hit rate =1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')after my caculation on one real AWR sample.
Your opinion?
Lu
WBGaynor
28 Posts
0
January 30th, 2012 07:00
Lu,
Actually I agree.
% read misses rate should be:
% Percentage physical reads to total reads = 100*(physical reads per sec / logical reads per sec),
where logical reads = consistent gets + db block gets
= consistent gets from cache + consistent get direct + db block gets from cache + db block gets direct
Caveat is physical reads per sec may overstate reads as it includes PGA reads and I'm having a hard time determining if logical reads does, but it seems not.
From Oracle® Database Reference 11g Release 1 (11.1) Part Number B28320-03:
"physical reads --Total number of data blocks read from disk. This value can be greater than the value of "physical reads direct" plus "physical reads cache" as reads into process private buffers also included in this statistic.
physical reads cache -- Total number of data blocks read from disk into the buffer cache. This is a subset of "physical reads" statistic.
physical reads direct -- Number of reads directly from disk, bypassing the buffer cache. For example, in high bandwidth, data-intensive operations such as parallel query, reads of disk blocks bypass the buffer cache to maximize transfer rates and to prevent the premature aging of shared data blocks resident in the buffer cache.
consistent gets direct -- Number of times a consistent read was requested for a block bypassing the buffer cache (for example, direct load operation). This is a subset of "consistent gets" statistics value.
consistent gets from cache -- Number of times a consistent read was requested for a block from buffer cache. This is a subset of "consistent gets" statistics value.
db block gets direct -- Number of times a
CURRENTblock was requested bypassing the buffer cache (for example, a direct load operation). This is a subset of "db block gets" statistics value.db block gets from cache -- Number of times a
CURRENTblock was requested from the buffer cache. This is a subset of "db block gets" statistics value."bg
LouisLu
161 Posts
0
January 31st, 2012 01:00
BG,
The details really makes sense for me. If available, I like to talk with you on PGA reads links to physical reads. I am not considerate on overstate reads before.
Lu