Oracle Scratchpad

January 26, 2015

In-memory DB

Filed under: 12c,in-memory,Infrastructure,Oracle — Jonathan Lewis @ 8:18 am BST Jan 26,2015

A recent thread on the OTN database forum supplied some code that seemed to show that In-memory DB made no difference to performance when compared with the traditional row-store mechanism and asked why not.  (It looked as if the answer was that almost all the time for the tests was spent returning the 3M row result set to the SQL*Plus client 15 rows at a time.)

The responses on the thread led to the question:  Why would the in-memory (column-store) database be faster than simply having the (row-store) data fully cached in the buffer cache ?

Maria Colgan has addressed this question in part 3 of her series on In-Memory Database (see catalogue below), but I thought I’d repeat the basic ideas with a few imaginative numbers thrown in to give a better flavour of what’s going on. So imagine you have a table sized at 100GB, with 100 columns of data where every column holds data of a similar size and pattern; and want to execute a query of the form: select {list of columns} from big_table where colX >=  {some constant}.

Traditional Tablescan (approximation) with table fully cached

For each block of the 100GB, Oracle has to acquire the “cache buffers chains” latch, pin the block, drop the latch, and scan the block testing each row, then acquire the latch, unpin the block, and drop the latch.  Scanning the block requires a walk through the row directory and, for each row pointer, jumping to the correct location in the block for the row, stepping along the row one column at a time to get to the correct row, and then checking the column  value. If the column matches the predicate extract, format and return the required columns from that row.

It’s a lot of memory to scan, in a large number of small steps, involving a lot of latching and pinning – which translates into a lot of CPU. On the plus side, although it’s very expensive to identify the required rows, it’s very cheap to construct and return a row once you’ve identified it.

In-memory scan (approximation)

  1. Given the way I’ve described the table (100GB, 100 similar columns), Oracle can recreate it in memory as 100 lists of 1GB each; so we can identify the rows we want by scanning one of those lists and applying the predicate – so only 1GB of (fairly contigious) memory to scan, rather than 100GB, and virtually no latching and pinning to find that memory, and no jumping around following pointers and counting along rows.
  2. But it’s probably NOT 1GB of memory to scan, because Oracle has some compression/deduplication methods that it can apply to the data that often reduces the memory requirement by a factor of four of five – so perhaps it’s only 250 MB of memory to scan.
  3. But Oracle breaks large lists into chunks, so rather than 250MB of contiguous memory, it’s likely to be 250 chunks of 1MB; and as part of the description of each chunk Oracle records the lowest and highest value in the chunk; [ed:  Christian Antognini says that the metadata list every distinct value for the chunk] so it can check the predicate against the boundary values on the chunk and be in a position to discard entire chunks without looking at their content. So, depending on the actual content and pattern of the data, we may examine only a handful of chunks in detail, dropping the scan from 250MB to, perhaps, 10MB.
  4. And we still haven’t finished there, because the code that handles the column-store is able to take advantage of the SIMD (Single Instruction,  Multiple Data) operations in the CPU to check the predicate against 4, or possibly even 8, values simultaneously at a speed matching a single column comparison that has to be used for the traditional cached row-store. (Given the way that Oracle  (probably) handles the compression, I suspect that this final advantage is only significant for range-based predicates – but that’s purely conjectural).

So the huge benefit you get from In-Memory column store, compared to “fully cached row-store”  is that you are likely to scan far less memory to identify the rows that match your predicate, and do it with far less “infrastructure” activity like latching and pinning. The potential saving in CPU usage is huge.

There is, of course, a penalty to pay. As you identify the rows of interest you can (in effect) construct a bitmap representing the position of those rows in the table (and if you have predicates on more than 1 column you can use bitmap operations on the individual column bitmaps to identify the rows you want in the final result) but then you have to construct the row that goes into the result set. If your query is interested in just 5 columns that means using the bitmap to locate the correct entry from each of 5 separate column lists; if your query is interested in 99 column that means extracting the correct entry from each of 99 separate column lists. Identifying the rows you want can be very  quick, building the final result may be relatively slow.

Soundbite summary

  • Using the In-memory Database, you can identify the rows you want very quickly but it’s relatively slow to reconstruct them.
  • Using a fully cached traditional row-store, it’s relatively slow to identify the rows you want, but once you’ve found them you spend no time reconstructing them.

Bear in mind that this is an extremely simplified analysis and ignores all sorts of details about read-consistency, the probability of physical reads, the probability of scanning blocks instead of scanning chunks, and so on; my focus is only on the type of activity that differentiates row-store handling from column-store handling when all the data is in memory so that you can have some appreciation of why the benefits available from In-memory DB can vary with the pattern of the data and the way you use it.

Catalogue of blog posts by Maria Colgan and Andy Rivenes:

Other articles on In-memory DB:

 

5 Comments »

  1. Oracle was part of the Proof of Concept at our site. The compression (Query High) was 2x, not 4 or 5. And the In-Memory, Columnar option ran slower than the row store for BI / DS type queries. I’ve worked with other, truly columnar databases and know what to expect. I suspect Oracle’s HCC as the culprit. Whatever the cause, the option does not perform as advertised.

    Comment by Joel Wittenmyer — January 26, 2015 @ 2:03 pm BST Jan 26,2015 | Reply

  2. Jonathan,

    Thanks for providing a high level information on In-memory DB and all the other blog posts and articles.

    Thanks
    Aswath

    Comment by Aswath Rao — January 31, 2015 @ 3:49 pm BST Jan 31,2015 | Reply

  3. Reblogged this on A Rahim Khan's Blog.

    Comment by A Rahim Khan — February 7, 2015 @ 5:50 pm BST Feb 7,2015 | Reply

  4. Reblogged this on Oracle Learnings | A Rahim Khan.

    Comment by A Rahim Khan — February 7, 2015 @ 5:56 pm BST Feb 7,2015 | Reply

  5. Hi Jonathan.,

    I’m still slightly disappointed by speed of IN-MEMORY column store. I have comapred column store (without compression, with default compression it was slightly slower) against fully cached store. test1 and test2 were created from the same source table , the test1 is in-memory, test2 fully cached.

    I have just counted all columns of tables (I have also tried to run range based predicates against each column in one query with help of OR, it was more less same result). Is count on uncompressed table able to get advantage of SIMD? From my point of view ot should be usable. Nad SIMD is leading to another note, testcase was ran on AIX 12.1.0.2 and Oracle is cheek to IBM customers enough, so SIMD is not implemented on AIX as of now

    SELECT COUNT (internal_key)
    , COUNT (from_date)
    , COUNT (TO_DATE)
    , COUNT (balance)
    , COUNT (calc_amt)
    , COUNT (rate)
    , COUNT (cr_int)
    , COUNT (dr_int)
    , COUNT (print_date)
    , COUNT (cr_auth_int)
    , COUNT (dr_auth_int)
    , COUNT (seq_no)
    , COUNT (tran_date)
    , COUNT (TYPE)
    FROM cbs.test1
    /

    Elapsed: 00:00:04.26

    SELECT COUNT (internal_key)
    , COUNT (from_date)
    , COUNT (TO_DATE)
    , COUNT (balance)
    , COUNT (calc_amt)
    , COUNT (rate)
    , COUNT (cr_int)
    , COUNT (dr_int)
    , COUNT (print_date)
    , COUNT (cr_auth_int)
    , COUNT (dr_auth_int)
    , COUNT (seq_no)
    , COUNT (tran_date)
    , COUNT (TYPE)
    FROM cbs.test2
    /

    Elapsed: 00:00:06.20

    However even without SIMD, I suggest the difference should be more noticable
    1) very low latching activity (one latch per IMCU against 2 cbc latches per consistent get on traditional db block)
    2) more sequential access to memmory and espacially Power7 and Power8 are able to accelerate seqnetuial memory access by HW prefetching to L3 cache
    3) no need to reconstruct rows in my particular case

    Comment by Pavol Babel — February 18, 2015 @ 8:13 pm BST Feb 18,2015 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.