In-Memory Column Store: 10046 May Be Lying to You!

The Oracle In-Memory Column Store (IMC) is a new database option available to Oracle Database Enterprise Edition (EE) customers. It introduces a new memory area housed in your SGA, which makes use of the new compression functionality brought by the Oracle Exadata platform, as well as the new column oriented data storage vs the traditional row oriented storage. Note: you don’t need to be running on Exadata to be able to use the IMC!

Part I – How does it work?

In this part we’ll take a peek under the hood of the IMC and check out some of its internal mechanics.

Almost immediately upon creating this table, the w00? processes will wake up from sleeping on the event ‘Space Manager: slave idle wait’ and start their analysis to check out the new table. By the way, the sleep times for this event are between 3 and 5 seconds, so it’s normal if you experience a little bit of a delay.

So that’s still the wrong one right there, and the STAT line even clearly shows that we’ve actually done 22005 physical reads, and therefore likely no in-memory scan, but a full scan from disk. There’s clearly a bug there with the execution plan reported, which is plain wrong.

Thus, be careful about using INMEMORY PRIORITY NONE, as you may not get what you expect. Since the PRIORITY NONE settings may also be overridden by any other PRIORITY settings, your data may get flushed out of the IMC, even though your execution plans will say otherwise. And I’m sure many of you know it’s often not slow response times on queries which cause a phone ringing hot. It’s inconsistent response times. This feature, if used inappropriately will pretty much guarantee inconsistent response times.

Apparently, what we should be doing is size up the In Memory Column store appropriately, to hold the objects we actually need to be in there. And make sure they’re always in there by setting a PRIORITY of LOW or higher. Use CRITICAL and HIGH to ensure the most vital objects of the application are populated first.

There was one other oddity that I noticed while tracing the W00? processes.

Part III – What are you scanning, Oracle ?

The m000 process’ trace file reveals many back-to-back executions of this select:

It could simply be a bug that results in these direct path reads being allocated to the wrong cursor. Or it could be intended, as it’s indeed this process’ job to analyze and load this table, and using this the resource usage caused by this is instrumented and can be tracked?

Either way, to sum things up we can say that:

– Performance benefits can potentially be huge
– Oracle automatically scans and caches segments marked as INMEMORY PRIORITY LOW|MEDIUM|HIGH|CRITICAL (they don’t need to be queried first!)
– Oracle scans segments marked as INMEMORY PRIORITY NONE (the default) only after they’re accessed the second time – and they may get overridden by higher priorities
– Oracle analyzes the table and stores the results in compression$
– Based on that analysis, Oracle may decide to load one or the other column only into IMC, or the entire table, depending on available space, and depending on the INMEMORY clause used
– It’s the W00? processes using some magic to do this analysis and read the segment into IMC.
– This analysis is also likely to be triggered again, whenever space management of the IMC triggers again, but I haven’t investigated that yet.

Stef, I think no matter what PRIORITY setting is and whether inmemory representation for the table (or rather segment) exist, execution plan should always indicate inmemory because Oracle will try to access segment in IMC cache and fallback to regular scans if data isn’t in IMC. And if you are scanning partitioned table with many IMC segments and some of them not in cache, then execution plan still needs to reflect inmemory scan attempt. I don’t think it’s a bug.

Although your warning that “priority none” means you may have done a physical read when the plan says the operation is “TABLE ACCESS INMEMORY” is important, but I’m not sure I’d say this represented a bug in the execution plan.

Consider the (not quite perfect) analogy that when Oracle performs a “logical I/O” it sometimes has to do a “physical I/O” first – when you see “table access by rowid” you don’t worry about whether the requisite buffer visit was or was not preceded by a physical read.

The code path used to project the correct result at that line of the plan could well be the in-memory code path – IF SO, the fact that the code had to load the in-memory area first is arguably not relevant when considering the correctness of the operation. (I’m not claiming that this is what happens, I haven’t tried tracing the in-memory code path yet, I’m must pointing out a possible error in interpretation.)

Would you want the optimizer to put some sort of “statistics collector” operation and then show two child operations for “full table scan” and “inmemory scan” ? If not then the run-time engine would have trigger re-optimization (which is possible) – buy what if the table got loaded into the in-memory cache by another session while re-optimisation was taking place, should your session re-optimise again ? Is it legal for a table to be partly cached (extent by extent, say) – if so what should the optimizer report if it does a part-scan by inmemory access and part by ordinary table scan ?