« Why the In-Memory Column Store is not used (II) | Main | Oracle 12C - In-Memory Option Resources »
Wednesday
Sep242014

Why the In-Memory Column Store is not used (I)

yuuupie!

Now finally after a long period of waiting - and looking presentations and reading blogs I was able to do In-Memory databasing on my own. Having sampled some theoretical background how a a pure In-Memory Db works with SAP HANA; my expectations on the Oracle In-Memory Option were high. Also because Oracle promised it would work without code change, out of the box.

The setup

The Oracle database is located on Sun Solaris Machine with enough memory and 32 Xeon-Cores; The Memory Pool is sized 64G, compared to the rest of the SGA , buffer cache , shared_pool ... 4G, and 300G of data.

Then I started to put tables to be populated to memory with the ALTER TABLE ... INMEMORY statement. I had in mind immediatelly to test one of our ugly long running problem statements with a lot of unions and counting and grouping by , joining a lot of tables; which could last > 25sec dependent on size of the customers structure/number transactions;

So simply I put all tables which I identified in the execution plan into memory. I wanted to see how it dramatically improves! I checked the single table access - all full table scans with filters were done on 2nd call in memory (ExecutionPlan step TABLE ACCESS INMEMORY FULL) for every singe table/segment. Fine!!

You also can check the state - populate_status should be 'COMPLETED'

SELECT IMS.SEGMENT_NAME, IMS.POPULATE_STATUS FROM V$IM_SEGMENTS IMS

And now the complete orchestra; :)

The big disappointment!

Nothing! Nada! Niente ! ничто́ ! Negativ ! - not one single In-Memory column was accessed by the execution of the SQL  - it was just the old style plan (Nested Loops, Hahs Jooin) using all the PKs to join with other tables or other fitting indexes. I tried a lot - Hints, SQL rewrite (did some feature of my SQL hinder the optimizer to do an In-Memory access?), different In-Memory compression levels but no in-memory access! All the 'IM scan%' stat values were zero except the 'IM scan segments disk' state value which was 3 for some reason on every execution;

Instead oracle generated the old style - uuuaaah ;) - plan with nested loops hash joins and a lot access was traditionally based on indexes; Not that indexes are useless now; It's a lot less work for a CPU to access a low number of rows via an index in the buffer cache then scanning the tables column full in-memory.

So my first impression was - yes In-Memory works but only for single tables - Ok it's more some OLAP feature and not usable for data stored in real complex relationships.

It seemd to me that the optimizer was not able to translate the index access into an equivalent In-Memory Column Store access; Maybe the column store is not able to deliver an index like behavior; And hence Oracle seems still to rely still on traditional indexes even full columnar copies of the table to memory is in place.

Maybe the Oracle structure the Column Store very different to the SAP HANA one; in SAP HANA every Colums Store is sorted ( this guarantees also that a bunch of column values can loaded with one CPU command as they are located together in RAM ) - and to avoid resort on every insert an unsorted differential buffer takes a bunch of fresh inserted data which is flushed in an interval to the sorted/indexed SAP HANA column store;

Maybe we are on basic starting point for Oracle In-Memory and additional features wil enrich execution plans of unchanged SQL;

Ok to sum it up if your problem SQL has this kind of execution plan (see below) it may be not touched by the new In-Memory DB feature at all;

This is not the End!

Ok in the next Blog I will tell you about how I did overcome the described situation; maybe you already know about; Or may you tell your experiences with Oracle In-Memory Option?

cheers

/Karl

PS.: I am aware probably to be more clever if I had read the manuals - I did - but I wanted just to test the promise - it's just works - no - in my ( maybe special ) case it did not - and now cu it's stays exciting!

 

References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>