Question: do I need to know anything in this blog post?
Answer: No, it is a true cache and CELLMEMORY works automatically
Question: so why should I read this blog post?
Answer: because you like to keep a toolkit of useful ways to control the system when needed
The Exadata engineering team has done a lot of work to make the flash cache automatically handle a variety of very different workloads happening simultaneously which is why we now typically discourage users from specifying:
SQL> Alter Table T storage (cell_flash_cache keep);
and trust the AUTOKEEP pool to recognize table scans that would most benefit from caching and to cache them in a thrash resistant way. Our Best Practice for CELLMEMORY is typically not to use the DDL. That said, there will be cases when the DBA wishes to override the system’s default behavior and we will look at a couple of those reasons. But first here’s the DDL which is a very cut down portion of the INMEMORY syntax:
Alter Table T [ [ NO ] CELLMEMORY [ MEMCOMPRESS FOR [ QUERY | CAPACITY ] [ LOW | HIGH ] ] ]
So let’s break this down piece by piece.
SQL> Alter Table T NO CELLMEMORY
The NO CELLMEMORY clause prevents a table being eligible for the rewrite from 18.104.22.168 format to 12.2 In-Memory format. There are a variety of reasons you may wish to do this and we’ll look at those at the end of this post.
SQL> Alter Table T CELLMEMORY SQL> Alter Table T CELLMEMORY MEMCOMPRESS FOR CAPACITY
These allows a table to be cached in the default 12.2 In-Memory format (you’d only ever need this to undo a NO CELLMEMORYyou done earlier or to revert a change in the compression level used).
SQL> Alter Table T CELLMEMORY MEMCOMPRESS FOR QUERY
I mentioned above that CELLMEMORY, by default, undergoes two rounds of compression: first a round of semantic compression and secondly a round of bitwise compression using LZO is applied. This is the default to try and keep CELLMEMORY’s flash cache footprint as close as we can to HCC flash space usage but run queries much faster than HCC. But, even though LZO has a relatively low decompression cost it is not zero. There are some workloads which run faster with MEMCOMPRESS FOR QUERY but they also use typically twice as much flash space. It would not be appropriate for the system to automatically start using twice as much flash but if you wish to experiment with this, the option is there. Also compressing with LZO takes CPU time which is not needed with MEMCOMPRESS FOR QUERY.
What about [ LOW | HIGH ] ?
Currently, unlike INMEMORY, these are throw away words but we retained them in the grammar for future expansion and because people are used to specifying them in a MEMCOMPRESS clause. this means in effect:
- CELLMEMORY’S MEMCOMPRESS FOR QUERY
is roughly equivalent of INMEMORY’S MEMCOMPRESS FOR QUERY HIGH
- CELLMEMORY’S MEMCOMPRESS FOR CAPACITY
is roughly equivalent of INMEMORY’S MEMCOMPRESS FOR CAPACITY LOW
Why might I want to overrule the default?
There are a few reasons:
- Turning CELLMEMORY off may be useful if you know an HCC table will only be queried a few times and then dropped or truncated soon and it is not worth the CPU time to create the In-Memory formats
- Turning CELLMEMORY off may be useful if your cells are already under a lot of CPU pressure and you wish to avoid the CPU of creating the In-Memory formats
- Switching to MEMCOMPRESS FOR QUERY may be useful to get better query performance on some workloads (YMMV) and reduce the CPU cost of creating In-Memory formats
What about INMEMORY with CELLMEMORY?
We allow both INMEMORY and CELLMEMORY on the same table
SQL> Create Table T (c1 number) INMEMORY NO CELLMEMORY; SQL> Create Table T (c1 number) INMEMORY CELLMEMORY MEMCOMPRESS FOR QUERY;
Why would you want both INMEMORY and CELLMEMORY?
DBIM implements a priority system where the In-Memory area is first loaded with the critical tables and then finally down to the PRIORITY LOW tables. If you have a PRIORITY LOW table that is unlikely to get loaded in memory it is fine to also specify CELLMEMORY so as to still get columnar performance. Note: an HCC encoded INMEMORY table will still get automatic CELLMEMORY if you don’t use any DDL at all.