In-memory Columnar Store hands-on

As I’ve written in my previous post, the inmemory_size parameter is static, so you need to restart your instance to activate it or change its size. Let’s try to set it at 600M.

 

First interesting thing: it has been rounded to 608M so it works in chunks of 16M. (to be verified)

Which views can you select for further information?

V$IM_SEGMENTS gives a few information about the segments that have a columnar version, including the segment size, the actual memory allocated, the population status and other compression indicators.

The other views help understand the various memory chunks and the status for each column in the segment.

Let’s create a table with a few records:

The table is very simple, it’s a cartesian of two “all_tables” views.

Let’s also create an index on it:

The table uses 621M and the index 192M.

How long does it take to do a full table scan almost from disk?

15 seconds! Ok, I’m this virtual machine is on an external drive 5400  RPM… 🙁

Once the table is fully cached in the buffer cache, the query performance progressively improves to ~1 sec.

There is no inmemory segment yet:

You have to specify it at table level:

The actual creation of the columnar store takes a while, especially if you don’t specify to create it with high priority. You may have to query the table before seeing the columnar store and its population will also take some time and increase the overall load of the database (on my VBox VM, the performance overhead of columnar store population is NOT negligible).

Once the in-memory store created, the optimizer is ready to use it:

The previous query now takes half the time on the first attempt!

The columnar store for the whole table uses 23M out of 621M, so the compression ratio is very good compared to the non-compressed index previously created!

 

This is a very short example. The result here (2x improvement) is influenced by several factors. It is safe to think that with “normal” production conditions the gain will be much higher in almost all the cases.
I just wanted to demonstrate that in-memory columnar store is space efficient and really provides higher speed out of the box.

Now that you know  about it, can you live without? 😛

Oracle Database 12c in-memory option, a quick overview

Oracle Database 12.1.0.2 is finally out, and as we all knew in advance, it contains the new in-memory option.

I think that, despite its cost ($23k per processor), this is another great improvement! 🙂

Consistent savings!

This new feature is not to be confused with Times Ten. In-memory is a feature that enable a new memory area inside the SGA that is used to contain a columnar organized copy of segments entirely in memory. Columnar stores organize the data as columns instead of rows and they are ideal for queries that involve a few columns on many rows, e.g. for analytic reports, but they work great also for all extemporary queries that cannot make use of existing indexes.

Columnar stores don’t replace traditional indexes for data integrity or fast single-row look-ups,  but they can replace many additional indexes created for the solely purpose of reporting. Hence, if from one side it seems a waste of memory, on the other side using in-memory can lead to consistent memory savings due to all the indexes that have no more reason to exist.

Let’s take an example of a table (in RED) with nine indexes (other colors).

inmem_table_indexes

If you try to imagine all the blocks in the buffer cache, you may think about something like this:

inmem_sga1

Now, with the in-memory columnar store, you can get the rid of many indexes because they’ve been created just for reporting and they are now superseded by the performance of the new feature:

inmem_no_indexes

 

In this case, you’re not only saving blocks on disk, but also in the buffer cache, making room for the in-memory area. With columnar store, the compression factor may allow to easily fit your entire table in the same space that was previously required for a few, query-specific indexes. So you’ll have the buffer cache with traditional row-organized blocks (red, yellow, light and dark blue) and the separate in-memory area with a columnar copy of the segment (gray).

inmem_sga2

The in-memory store doesn’t make use of undo segments and redo buffer, so you’re also saving undo block buffers and physical I/O!

 

The added value

In my opinion this option will have much more attention from the customers than Multitenant for a very simple reason.

How many customers (in percentage)  would pay to achieve better consolidation of hundreds of databases? A few.

How many  would pay or are already paying for having better performance for critical applications? Almost all the customers I know!

 

Internal mechanisms

In-memory is enabled on a per-segment basis: you can specify a table or a partition to be stored in-memory.

Each column is organized in separate chunks of memory called In Memory Compression Units (IMCU). The number of IMCUs required for each column may vary.

Each IMCU contains the data of the column and a journal used to guarantee read consistency with the blocks in the buffer cache. The data is not modified on the fly in the IMCU, but the row it refers to is marked as stale in a journal that is stored inside the IMCU itself. When the stale data grows above a certain threshold the space efficiency of the columnar store decreases and the in-memory coordinator process ([imco]) may force a re-population of the store.
Re-population may also occur after manual intervention or at the instance startup: because it is memory-only, the data actually need to be populated in the in-memory store from disk.

Whether the data is populated immediately after the startup or not, it actually depends on the priority specified for the specific segment. The higher the priority, the sooner the segment will be populated in-memory. The priority attribute also drives which segments would survive in-memory in case of “in-memory pressure”. Sadly, the parameter inmemory_size that specifies the size of the in-memory area is static and an instance restart is required in order to change it, that’s why you need to plan carefully the size prior to its activation. There is a compression advisor for in-memory that can help out on this.

Conclusion

In this post you’ve seen a small introduction about in-memory. I hope I can publish very soon another post with a few practical examples.