Exadata Flash Cache Hit Ratio

Here is the cumulative numbers for system physical I/Os and cell flash cache read hits:

SQL>select name,value  from v$sysstat where name in('physical read total IO requests','cell flash cache read hits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                  3052130616
cell flash cache read hits                                        452644513

Flash cache hit ratio is approximately %15

Following sql calculates cumulative stats and ratios using dba_hist_sysstat:

SQL> select c.snap_time, sum(a.value) physical_read_IO_requests, sum(b.value) flash_cache_read_hits,  round((sum(b.value)/sum(a.value) * 100),2) flash_cache_hit_ratio from
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) a,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) b,
(select to_char(begin_interval_time,'YYYY-MM-DD hh24') snap_time,snap_id from DBA_HIST_SNAPSHOT where instance_number=1) c
where
a.snap_id=b.snap_id and a.instance_number=b.instance_number and a.snap_id=c.snap_id
group by c.snap_time
order by 1
/

In order to find not cumulative but delta stats and ratios:

select c.snap_time, sum(a.value-d.value) physical_read_IO_req_delta, sum(b.value-e.value) flash_cache_read_hits_delta,  round((sum(b.value-e.value)/sum(a.value-d.value) * 100),2) flash_cache_hit_ratio from
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) a,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) b,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) d,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) e,
(select to_char(begin_interval_time,'YYYY-MM-DD hh24') snap_time,snap_id from DBA_HIST_SNAPSHOT where instance_number=1 and begin_interval_time > sysdate-3) c
where
a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.snap_id=(d.snap_id+1) and a.instance_number=d.instance_number
and a.snap_id=(e.snap_id+1) and a.instance_number=e.instance_number
and a.snap_id=c.snap_id
group by c.snap_time
order by 1
/