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 http://docs.oracle.com/database/121/DWHSG/attcluster.htm#DWHSG9338.

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.

What was the actual DOP for my query ? (ASH and the PX_FLAGS)

In this post i will show a quick way to check for the actual DOP for a past query(Only for version higher than 11.2.0.2).We will be using for that ASH and the undocumented column PX_FLAGS (Randolf Geist already talked about that and used it in his awesome utility XPLAN_ASH http://oracle-randolf.blogspot.com/2014/03/new-version-of-xplanash-utility.html)

Let’s begin with a simple query using one DFO (Like you may know it’s not the query that run in parallel. It’s the Data flow operations and a query may contain many of them)

ORACLE 12C in-memory : Part 4 (IMCU Stale entries)

“The more stale entries in an IMCU,the slower the scan of IMCU will become” (Oracle in-memory white papers)
In fact the stale row will be read from the transaction journal (if it’s there) or from the buffer cache.

The question is how much stale entries do we have ? How to reduce them if required ?