In-Memory column store

[Roshan]

Oracle Database 12.1.0.2
RHEL 6.7
RAM: 64G

Hi,

I would like to implement the in-memory column on a database instance. If my current sga_size is 38G, how do I calculate the inmemory_size parameter to set?

Is there a limit on the size of tables I can populate in memory?


Regards,

Joe

[ErmanArslansOracleBlog]

in_memorysize should be set to at least the size needed to accommodate all the tables which you want use the in-memory column store..

So you need to calculate it...
In general, you need to set in_memorysize according to the total size of the tables that you want to put in to the memory column store, but after this setting, you should also have necessary space left in your SGA for other allocations..

So do your checks accordingly... there is no magic number for in_memorysize.

[Roshan]

For example, if my table size is 1TB, I should allocate some 1.5T for in memory? And if the size of the table grows, do I need to increase the memory?

[ErmanArslansOracleBlog]

As Oracle says:  The amount of memory required by the IM column store depends on the database objects stored in it and the compression method applied on each object. To make the greatest reduction in memory size, choose the FOR CAPACITY HIGH or FOR CAPACITY LOW compression methods. However, these options require additional CPU during query execution to decompress the data. To get the best query performance, choose the FOR QUERY HIGH or FOR QUERY LOW compression methods. However, these options consume more memory.  

So, you have inmemory compression option that you can use..

As Oracle says: The compression used during the population of the IM column store is different to any of Oracle’s previous types of compression. These new compression algorithms not only help save space but also improve query performance by allowing queries to execute directly against the compressed columns. This means all scanning and filtering operations will execute on a much smaller amount of data. Data is only decompressed when it is required for the result set.

So if  you use compression , then you will occupy less space in memory.
Also, there are things that you can do in RAC environments.

For ex: You can put one part of a big table into the memory column store of your first RAC node, and one part of that table into the memory column store of your second RAC node..

Once configured, you can also increse the Size of the IM Column Store dynamically.. (but for this to work; your db version should be 12.2.0. at least)

[Roshan]

Thanks for support Erman :)