« Using SSD for a temp tablespace on Exadata | Main | Redo on SSD: effect of redo size (Exadata) »
Monday
Dec302013

Can the Exadata Smart Flash Cache slow smart scans?

I’ve been doing some work on the Exadata Smart Flash Cache recently and came across a situation in which setting CELL_FLASH_CACHE to KEEP will significantly slow down smart scans on a table.

If we create a table with default settings, then the Exadata Smart Flash Cache (ESFC) will not be involved in smart scans, since by default only small IOs get cached.  If we want the ESFC to be involved, we need to set the CELL_FLASH_CACHE to KEEP.  Of course, we don’t expect immediate improvements, since we expect that the next smart scan will need to populate the cache before subsequent scans can benefit. 

HOWEVER, what I’m seeing in practice is that the next smart scan following an ALTER TABLE … STORAGE(CELL_FLASH_CACHE KEEP) is significantly degraded, while subsequent scans get a performance boost.  Here’s an example of what I observe:

image

 

The big increase in CELL IO time is in an increase in both the number and latency of cell smart table scans.  The wait stats for the first scan with a default setting looked like this:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  gc cr disk read                                 1        0.00          0.00
  cell single block physical read                 2        0.01          0.01
  row cache lock                                  2        0.00          0.00
  gc cr grant 2-way                               1        0.00          0.00
  SQL*Net message to client                    1021        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                2        0.00          0.00
  cell smart table scan                        9322        0.14          7.60
  SQL*Net message from client                  1021        0.00          0.02

For the first scan with KEEP cache it looked like this:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1021        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                2        0.00          0.00
  cell smart table scan                       14904        1.21         33.37
  SQL*Net message from client                  1021        0.00          0.02

Looking at the raw trace file didn’t help – it just shows a bunch of lines like this, with only a small number (3 in this case) of unique cellhash values… I couldn’t see a pattern:

WAIT #… : nam='cell smart table scan' ela= 678 cellhash#=398250101 p2=0 p3=0 obj#=139207 tim= …

I’m at a loss to understand why there would be such a high penalty for the initial smart scan with CELL_FLASH_CACHE KEEP setting.  You expect some overhead from constructing and storing the result set blocks in the cache, but an IO penalty of 200=300% seems way too high.   Anybody seen anything like this or have a clear explanation?

Test script is here, and formatted tkprof here

References (21)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: naprawa agd
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: weblink
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: Nutrapump Reviews
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: Arthur Falcone
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: Phil St Ores
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: snorerx Or ZQuiet
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: rage dna Reviews
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: terminsdhf
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: Gainseville SEO
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: UK Models
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: ZQuiet Video
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Response: janome.com
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?
  • Response
    Guy Harrison - Yet Another Database Blog - Can the Exadata Smart Flash Cache slow smart scans?

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>