In-Memory Column Store versus the Buffer Cache

In this week's blog post I'm going to address just one question. It's actually the question I have gotten the most since I started blogging on Oracle Database In-Memory.

Why does In-Memory populate complete tables or partitions into memory instead of caching frequently accessed blocks like the buffer cache?

Standard caches such as a buffer cache rely on non-uniform access patterns that are common in OLTP applications. Some rows within a table are accessed much more frequently than other rows. That is why you can have a buffer cache that is 10% the size of a table but that captures 95% of the random(OLTP) accesses to the table. In this example, lets assume you get a 20x speedup with a cache that is 10% the size of the table.

The main use case for the In-Memory column store is fast analytics, which has very different access patterns to OLTP. Typically analytic queries access a few columns from a table but they examine all of the entries in those columns. You need to scan the whole table or segment, not just a small part of the table to answer this queries. Let's say a scan of a table is 100x faster using the In-Memory column store. But if only 10% of the table is in the column store, then you will get a 1.1X speedup instead of a 100X speedup. This is because 90% of the table will have to be read using the conventional row store, and this is no faster than before.

That is a huge difference with the buffer cache example above where having 10% of the table in cache produced a 20X speedup for OLTP workloads. Therefore, for the In-Memory column store to work effectively, all of the table or partition should be populated in memory.

Comments:

hi

could you compare performance on oltp query (not analytic) with table s in KEEP cache(all blocks are cached), with same table in IM area? Is there also huge difference in perf.?

thanks

martin

Posted by guest on July 30, 2014 at 04:54 AM PDT #

So how is this different than setting the buffer_pool to keep or "alter table <table> cache?"

Posted by guest on July 30, 2014 at 03:28 PM PDT #

The difference is how the data is accessed and scanned. I will explain more details on this in the upcoming blog posts.

Thanks,
Maria

Posted by Maria Colgan on July 31, 2014 at 12:56 PM PDT #

Thanks for your posts Maria.

In Memory benefits go beyond of just having data blocks in RAM.

IM storage indexes are automatically created for each column, that means that you can put any column in the WHERE section of your SELECT statements, even those without a "traditional" index.

Vector operations that process multiple values in a single instruction and perform faster aggregations

Data compression (less data blocks to handle with)

Optimizer's new features.

Posted by Leopoldo Gonzalez on October 22, 2014 at 12:35 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Database In-Memory blog is written by the Oracle product management team and sheds light on all things In-Memory.

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today