Inmemory: Not all inmemory_size is usable to store tables.
Posted by Riyaj Shamsudeen on September 11, 2014
I have been testing the inmemory column store product extensively and the product is performing well for our workload. However, I learnt a bit more about inmemory column store and I will be blogging a few them here. BTW, I will be talking about internals of inmemory in Oaktable world presentation, if you are in the open world 2014, you can come and see my talk: http://www.oraclerealworld.com/oaktable-world/agenda/
Inmemory_size initialization parameter determines the amount of memory allocated to the in-memory column store. But only 80% of that memory value is allocated to store the objects. For examples, if you set inmemory_size=272G, then only 217G (=272*0.8) is used to store the objects, and the remaining 55GB is allocated for inmemory journal and internal objects. This is the reason why the inmemory heap is also split and tagged: IMCA_RO and IMCA_RW. IMCA_RW seems to be storing inmemory journal and IMCA_RO is to store objects in the memory. (Previous statement is not completely validated yet).
SELECT mem inmem_size, tot disk_size, bytes_not_pop, (tot/mem) compression_ratio, 100 *((tot-bytes_not_pop)/tot) populate_percent FROM (SELECT SUM(INMEMORY_SIZE)/1024/1024/1024 mem, SUM(bytes) /1024/1024/1024 tot , SUM(bytes_not_populated)/1024/1024/1024 bytes_not_pop FROM v$im_segments ) / INMEM_SIZE DISK_SIZE BYTES_NOT_POP COMPRESSION_RATIO POPULATE_PERCENT ---------- ---------- ------------- ----------------- ---------------- 217.25 231.17 .00 1.06407869 100
So, you should plan accordingly when you enable inmemory option. Notice that the compression ratio is about 1, but, that is because these objects are already compressed using HCC compression and so, memory compression is not going to give us a better compression. However, I see that size of a few objects have increased in-memory compared to the disk size, but, I am hoping that will be considered as a bug and will be fixed in later release.
Unfortunately, inmemory area chunks are not visible in v$sgastat at all, but v$sga shows the inmemory area correctly (show SGA output is correct as it queries v$sga too).