IN-MEMORY population/[trickle] re-population : How much work ? Part 2

In one of my previous blog post i demonstrated that the amount of work needed for IN-MEMORY population/[trickle] re-population in oracle is more than we may expect : IN-MEMORY population/[trickle] re-population : How much work ? You may be surprised !

Let’s do a very quick check  on how this is now handled in oracle 6/UEK4

Continue reading


This is a reference list grouping some IN-MEMORY post i published.I’ll be updating it  whenever I add a new one.


IN-MEMORY population/[trickle] re-population : How much work ? You may be surprised !

We all know that IN-Memory is a great new feature, but how much work is done on behalf to maintain the in-memory column store ? You may be surprised !

In this post i will trace the work done by the In-memory  background worker processes W0nn during population and  trickle re-population .I will be using systemtap and some scripts developed by Luca Canali.So let’s begin !

Continue reading

In-Memory : Optimizer cost calculation some anomaly ! (IM Quotient, IM Journal Rows)

When investigating how the optimizer calculate the cost of In-memory operation i hit some anomaly. Let’s begin with a big overview of how the cost of in-memory operation is calculated (For more info please check ). The cost of in-memory operation is based on this new keys statistics which are maintained in real time (as stated by the previous doc and demonstrated by Riyaj Shamsudeen for example function “ktmpjUpdInMemExtStat”  update In-memory extent statistics in the same session ) :

Continue reading

ORACLE IN-MEMORY transaction journal or buffer cache ?

Oracle in-memory white paper stated that “When a DML statement changes a row in an object that is populated into the IM column store, the corresponding entries for that row is marked stale in the IMCU and a copy of the new version of the row is added to the in-memory transaction journal.”

“When a query with a newer SCN is executed against the object, it will read all of the entries for the columns in the IMCU except the stale entries. The stale entries will be retrieved either from the transaction journal or from the base table (buffer cache).

UPDATE 30/03/2017 : It’s seem to be a documentation bug the new doc state : “The stale entries will be retrieved from the base table (i.e. row store).”

Lets try some example and observe :

Continue reading

Improving IN-MEMORY query with Attribute Clustering (IMCU pruning and compression ratio)

Attribute clustering is a new feature of oracle 12c for more info check

I will focus here only on how attribute clustering can improve in-memory query.For a description of in-memory pruning please check my previous post.

Here is a quick example to demonstate how Attribute clustering can improve in-memory query pruning and compression ratio. Here is the simple steps :

  1. Create a table and load it in-memory.
  2. Check for the min-max column for every IMCU and the total allocated space in-memory.
  3. Use attribute clustering for one column and then reload the table in-memory.
  4. Check for the min-max column for every IMCU and the total allocated space in-memory.
  5. Campare the results.

Continue reading

Oracle 12C In-memory : Part 3 (IMCU pruning)

For other parts : Part1 , Part2

Let’s see a quick example of in-memory pruning using storage index :

In-memory white paper : “A further reduction in the amount of data accessed is possible due to the In-Memory Storage Indexes that are automatically created and maintained on each of the columns in the IM column store. Storage Indexes allow data pruning to occur based on the filter predicates supplied in a SQL statement.An In -Memory Storage Index keeps track of minimum and maximum values for each column in an IMCU.When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column is examined to determine if any entries with the specified column value exist in each IMCU by comparing the specified value(s) to the minimum and maximum values maintained in the Storage Index. If the column value is outside the minimum and maximum range for an IMCU, the scan of that IMCU is avoided”

Quick example :
Continue reading

ORACLE 12C in-memory : Part 2 (Read consistency)

This is the second blog post about the new in-memory feature of oracle database 12c.For the preceding post Part 1

UPDATE 10/11/2014 : Flushing the buffer cache before testing to check for physical reads

In this post i will try to demonstrate how read consistency is maintained in the in-memory column store and determine how much work is done on behalf. Please read the white paper about in-memory before (you can find a link in the bottom of the page).

Let’s begin by creating our testing table and loading it on the in-memory column store :
Continue reading