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
/
No comments:
Post a Comment