SELECT 1-(phy.value/(cur.value+con.value)) "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'
;
show engine innodb status\G;
show global status like 'Innodb_buffer_pool_read%';
(1 - (innodb_buffer_pool_reads /innodb_buffer_pool_read_requests)) *100
https://lets.postgresql.jp/documents/technical/statistics/2
SELECT datname,
round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio
FROM pg_stat_database WHERE blks_read > 0;
SELECT relname,
round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2)
AS cache_hit_ratio FROM pg_statio_user_tables
WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio;
SELECT relname, indexrelname,
round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2)
AS cache_hit_ratio FROM pg_statio_user_indexes
WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio;
http://ryuchan.hatenablog.com/entry/2015/01/02/093739
SELECT [cache hit ratio] = ( opc.cntr_value * 1.0 / bcr.cntr_value ) * 100.0
FROM sys.dm_os_performance_counters opc
JOIN (SELECT cntr_value,
object_name
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name LIKE '%Buffer Manager%') bcr
ON opc.OBJECT_NAME = bcr.OBJECT_NAME
WHERE opc.counter_name = 'Buffer cache hit ratio'
AND opc.OBJECT_NAME LIKE '%Buffer Manager%'
;
