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:
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
@kevinclosson was kind enough to genlty correct my embarassing misconception about how the Exadata Smart Flash Cache (ESFC) deals with smart scans. Somehow I had got the impression that the ESFC was storing parts of the smart scan results - kind of like the result set cache. I really can't remember where I got that impression but as Kevin pointed out, its completely incorrect. What is being cached are the underlying table blocks - and you can clearly see this using LIST FLASHCACHECONTENT and looking at the flash hit rates for other queries.
So the overhead then is easier to understand since we are looking at placing all the blocks for a reasonably large table into the flash cache. This involves a large number of write operations over a very short period of time which in turn probably requires some on the fly garbage collection and signifcant write amplification.
The conclusion to draw is probably NOT to set CELL_FLASH_CACHE KEEP for a table of significant size unless you know for sure that it will be re-scanned within a short period of time. If the blocks age out of the cache and have to be reloaded you'll probably see a degradation rather than an improvement in scan performance.