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

■ deleting data or even dropping segments does not initially have any impact whatsoever on cached content
for Smart Flash Cache. Smart Flash Cache software automatically employs a modified lrU mechanism to age data from
flash cache, however, so this type of data has a high probability of being aged from cache over time.

Note

18-5. Measuring Smart Flash Cache Performance
Problem
You wish to measure Smart Flash Cache performance statistics and determine the benefit provided by Smart Flash
Cache both databasewide and on a per-SQL statement basis.

Solution
In this recipe, you will learn how to use various system and SQL statistics to measure Smart Flash Cache behavior on
your system. Additionally, you will learn how to query the cell's flashcachecontent object to identify segments that
have experienced high “hit counts,” an indication that Smart Flash Cache is providing benefits.

Measuring SFC Usage for Your Database
To measure the use of Smart Flash Cache for your database instance, query V$SYSSTAT using the script in Listing 18-3.

Listing 18-3. lst18-03-sfc-sysstat.sql

SQL> select i.instance_name,
ss.name, ss.value
from gv$sysstat ss,
gv$instance i
where i.inst_id=ss.inst_id
and ss.name in ('cell flash cache read hits',
'physical read total bytes',
'physical read total bytes optimized',
'physical read total IO requests',
'physical read requests optimized')
order by 2,1 asc
/
Instance Statistic Value
---------- ---------------------------------------- -------------
visx1 cell flash cache read hits 19358
visx2 cell flash cache read hits 17198
visx1 physical read requests optimized 19358
visx2 physical read requests optimized 17198
visx1 physical read total IO requests 41128
visx2 physical read total IO requests 25202
visx1 physical read total bytes 2755176960
visx2 physical read total bytes 517865984
visx1 physical read total bytes optimized 326631424
visx2 physical read total bytes optimized 252706816