Exadata smart scans and the flash cache
One of the key features of Exadata V2 is the flash cache. Although commonly thought of as an OLTP-specific feature, it has also been marketed as a data warehouse accelerator. According to this frequently-used presentation slide, a full Exadata rack provides 21 GB/sec of disk throughput and 50GB/sec of flash throughput.
(Note to readers: I’m referring to the Exadata smart flash cache, not the 11gR2 database flash cache/extended SGA, which is an altogether different animal)
I was testing throughput using a simple query, making use of both smart scans and parallel execution. Here’s what the objects look like. They’re running on a quarter rack system with a stated capacity of 4.5GB/sec disk and 11GB/sec flash.
SQL> desc ckbig; Name Null? Type ------------------------------------------------------------------------ -------- ------------------------------------------------- ID NUMBER FILLER VARCHAR2(3900) SQL> explain plan for select count(*) from ckbig; Explained. SQL> set lines 132 SQL> SELECT * FROM TABLE( dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3990549585 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 188K (1)| 00:37:43 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 44M| 188K (1)| 00:37:43 | Q1,00 | PCWC | | | 6 | TABLE ACCESS STORAGE FULL| CKBIG | 44M| 188K (1)| 00:37:43 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------------------- SQL> select trunc(sum(bytes)/1024/1024/1024)||'G' from dba_segments where segment_name = 'CKBIG'; TRUNC(SUM(BYTES)/1024/1024/1024)||'G' ----------------------------------------- 76G SQL> explain plan for select count(*) from ckbig; Explained. SQL> SELECT * FROM TABLE( dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3990549585 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 188K (1)| 00:37:43 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 23M| 188K (1)| 00:37:43 | Q1,00 | PCWC | | | 6 | TABLE ACCESS STORAGE FULL| CKBIG | 23M| 188K (1)| 00:37:43 | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------
Testing the query:
SQL> select name, value from v$mystat ms, v$statname sn where ms.statistic# = sn.statistic# and name in ('physical read total bytes','cell flash cache read hits');
NAME VALUE
---------------------------------------------------------------- ----------
physical read total bytes 0
cell flash cache read hits 0
SQL> set timing on
SQL> select count(*) from ckbig;
COUNT(*)
----------
20000000
Elapsed: 00:00:17.91
SQL> col value format 999999999999
SQL> select name, value from v$mystat ms, v$statname sn where ms.statistic# = sn.statistic# and name in ('physical read total bytes','cell flash cache read hits');
NAME VALUE
---------------------------------------------------------------- -------------
physical read total bytes 81921196032
cell flash cache read hits 0
SQL> select 81921196032/17.91/1024/1024/1024 as gbps from dual;
GBPS
----------
4.25991397
So in this test, we’re getting very close to the stated disk throughput (with some overhead associated with parallel query). But flash isn’t being used at all!
Looking through Oracle’s flash cache technical whitepaper, I found this paragraph in the section about pinning objects:
While the default behavior for sequential scans is to bypass the flash cache, this is not the case when KEEP is specified. If KEEP has been specified for an object, and it is accessed via an offloaded Smart Scan, the object is kept in and scanned from cache. Another advantage of the flash cache is that when an object that is kept in the cache is scanned, the Exadata software will simultaneously read the data from both flash and disk to get a higher aggregate scan rate than is possible from either source independently.
It would appear that one aspect of the flash path that is not automatically adjusting is the flash cache group, and by default the flash cache isn’t used by smart scans at all. There is a good reason for this though: when data sets are larger than the cache size, doing large reads through the cache can age out other cached objects, polluting the cache.
Repeating the test using the “KEEP” parameter:
SQL> alter table ckbig storage (cell_flash_cache keep);
Table altered.
SQL> select name, value from v$mystat ms, v$statname sn where ms.statistic# = sn.statistic# and name in ('physical read total bytes','cell flash cache read hits');
NAME VALUE
---------------------------------------------------------------- ----------
physical read total bytes 0
cell flash cache read hits 0
SQL> set timing on
SQL> select count(*) from ckbig;
COUNT(*)
----------
20000000
Elapsed: 00:00:22.31
SQL> col value format 999999999999
SQL> select name, value from v$mystat ms, v$statname sn where ms.statistic# = sn.statistic# and name in ('physical read total bytes','cell flash cache read hits');
NAME VALUE
---------------------------------------------------------------- -------------
physical read total bytes 81920647168
cell flash cache read hits 259
Slower: maybe overhead for populating the cache? There are virtually no cache read hits because there’s nothing in the cache. Running again, in a new session :
SQL> set timing on
SQL> select count(*) from ckbig;
COUNT(*)
----------
20000000
Elapsed: 00:00:06.63
SQL> col value format 999999999999
SQL> select name, value from v$mystat ms, v$statname sn where ms.statistic# = sn.statistic# and name in ('physical read total bytes','cell flash cache read hits');
NAME VALUE
---------------------------------------------------------------- -------------
physical read total bytes 81920516096
cell flash cache read hits 61209
Elapsed: 00:00:00.00
SQL> select 81920516096/6.63/1024/1024/1024 as gbps from dual;
GBPS
----------
11.5074549
This is actually slightly better than the stated rate of 11 GB/sec.
Confirming that the object is in the cache, by running CellCLI on a storage server:
CellCLI> list flashcachecontent where objectNumber = 115825 detail;
cachedKeepSize: 27370684416
cachedSize: 27409580032
dbID:
dbUniqueName: DBNAME
hitCount: 141814
hoursToExpiration: 24
missCount: 174408
objectNumber: 115825
tableSpaceNumber: 8
27409580032 * 3 cells = 76.6 G, very close to the 76.4G database segment size. So the entire object should be cached.
Running the query once more and getting numbers:
CellCLI> list flashcachecontent where objectNumber = 115825 detail;
cachedKeepSize: 27370684416
cachedSize: 27409580032
dbID:
dbUniqueName: DBNAME
hitCount: 161972
hoursToExpiration: 24
missCount: 180627
objectNumber: 115825
tableSpaceNumber: 8
So the delta is:
hitCount: 20158 (1/3 of total read hits reported at the DB server)
missCount: 6219 (24% of all reads)
total reads: 26377
26377 reads * 3 cells = 79131
79131 * 1024 * 1024 = 82974867456, just over the total read bytes, meaning that the read hit units are in megabytes
By extension, in the original query, there were 61209 flash cache read hits, or 60G. This leaves another 16G read off disk.
A quick summary:
- Objects must be marked as ‘CELL_FLASH_CACHE KEEP’ to be utilized by smart scans
- Once marked as such, reads involving the objects use both disk and flash
- Smart scans with flash assistance are about 2.5 times faster than from disk alone
- My actual measured disk+flash throughput of 11.5 GB/sec is slightly above the stated rate of 11
