Smart Flash Cache and Smart Flash Logging - Oracle Exadata Recipes: A Problem-Solution Approach - page 530

This script allows you to compare total read requests and bytes with “optimized” read requests and bytes.
Optimized reads are reads satisfied from Smart Flash Cache. In the previous output, notice that the cell flash
cache read hits and physical read requests optimized statistics are the same on each instance; these statistics
are measuring the same thing—flash cache read hits. You can modify the query in Listing 18-3 to query V$SESSTAT or
V$MYSTAT if you are interested in session level Smart Flash Cache statistics as well.
As you are likely thinking, one question that most Exadata DMAs ask is “What percentage of my reads or total
bytes is satisfied by Smart Flash Cache?” To calculate this, simply divide ( physical read requests optimized ) by
( physical read total IO requests) and/or ( physical read total bytes optimized ) by ( physical read total
bytes) . Using the statistics above, our flash cache hit ratio across our cluster was the following:
SFCHitRatio = (19358 +17198) / (41128 + 25202) = 55%
To conduct an arguably more beneficial analysis, run the script in Listing 18-4. This script reports the total
number of physical read I/Os per second, physical read I/O bandwidth (MB), optimized I/Os per second, optimized
I/O bandwidth, and a flash cache utilization percentage for both I/Os bandwidth and I/Os per second.
Listing 18-4. lst18-04-sfc-awrstat.sql
SQL> select id,tm,dur,prtb,prtbo,
100*(prtbo/prtb) byteshr,
priops,priopso,
100*(priopso/priops) iopshr
from (
select snaps.id, snaps.tm,snaps.dur,snaps.instances,
((sysstat.fcrh -
lag (sysstat.fcrh,1) over (order by snaps.id)))/dur/60 fcrh,
((sysstat.prtb -
lag (sysstat.prtb,1) over (order by snaps.id)))/dur/60/1024/1024 prtb,
((sysstat.prtbo -
lag (sysstat.prtbo,1) over (order by snaps.id)))/dur/60/1024/1024 prtbo,
((sysstat.priops -
lag (sysstat.priops,1) over (order by snaps.id)))/dur/60 priops,
((sysstat.priopso-
lag (sysstat.priopso,1) over (order by snaps.id)))/dur/60 priopso
from
( /* DBA_HIST_SNAPSHOT */
select distinct id,dbid,tm,instances,max(dur) over (partition by id) dur from (
select distinct s.snap_id id, s.dbid,
to_char(s.end_interval_time,'DD-MON-RR HH24:MI') tm,
count(s.instance_number) over (partition by snap_id) instances,
1440*((cast(s.end_interval_time as date) - lag(cast(s.end_interval_time as date),1)
over (order by s.snap_id))) dur
from dba_hist_snapshot s,
v$database d
where s.dbid=d.dbid)
) snaps,
( /* DBA_HIST_SYSSTAT */
select * from
(select snap_id, dbid, stat_name, value from
dba_hist_sysstat
) pivot