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 ?

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 :

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 :

Oracle database 12c in-memory – part1 (unwanted behavior)

In my first post about the amazing in-memory feature (others are coming) I will talk about some “unwanted behavior” with the in-memory feature when choosing the best execution plan and the table was not yet populated in the in-memory column store.

Here is a quick and simple example :

ORACLE 12C Automatic big table caching

In my first blog post i will talk about one of the new feature added in Oracle 12.1.0.2, that is automatic big table caching.

Like the name suggest automatic big table caching enable caching of big table in memory precisely in a portion of the buffer cache (avoiding direct path read). First, what is considered a big table and how table scan was handled in pervious release that is 11g (When direct path read does kick in)? And then we will discover this new feature.

All tests are done in 12.1.0.2 and 11.2.0.3.9 single instance database. (Using default buffer cache and default parameters)