Flash cache used when smart scans are disabled

I just wanted to double check something on my Exadata slides.  I think I had checked this before but became unsure if something I was saying was true, namely that when you run a normal non-smart scan query on Exadata the cell storage servers still query the flash cache memory for cached disk blocks.  So,I tried a full scan with smart scans disabled and it appears, based on statistics kept by the database, that the flash cache was used.  Here is the (edited for clarity) output of my test script:

SQL> alter session set cell_offload_processing = FALSE;

SQL> select sum(BLOCKS) from test;

SUM(BLOCKS)
-----------
 2213273152

SQL> select a.name,b.value/1024/1024 MB from v$sysstat a,
  2  v$mystat b
  3  where a.statistic# = b.statistic# and
  4  (a.name in ('physical read total bytes',
  5  'physical write total bytes',
  6  'cell IO uncompressed bytes') or a.name like 'cell phy%');

NAME                                                              MB
--------------------------------------------------------------- ----
physical read total bytes                                       56.5
physical write total bytes                                         0
cell physical IO interconnect bytes                             56.5
cell physical IO bytes sent directly to DB node to balanceCPU u    0
cell physical IO bytes saved during optimized file creation        0
cell physical IO bytes saved during optimized RMAN file restore    0
cell physical IO bytes eligible for predicate offload              0
cell physical IO bytes saved by storage index                      0
cell physical IO interconnect bytes returned by smart scan         0
cell IO uncompressed bytes                                         0

SQL> select a.name,b.value from v$sysstat a,
  2  v$mystat b
  3  where a.statistic# = b.statistic# and
  4  (a.name like '%flash cache read hits' or
  5   a.name ='physical read total IO requests');

NAME                                                           VALUE
-------------------------------------------------------------- -----
physical read total IO requests                                 1839
cell flash cache read hits                                      1805

The alter session turned off the smart scans.

The zero in the “cell physical IO interconnect bytes returned by smart scan” statistic indicates that the smart scans were not used.

The “cell flash cache read hits” statistic > zero indicates that the flash cache is used.

So, assuming we can trust these statistics this test shows that non-smart scan reads of data blocks from cell storage servers can access blocks from flash cache.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Flash cache used when smart scans are disabled

  1. Pingback: Exadata Flash cache latency .4 milliseconds | Bobby Durrett's DBA Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.