Thursday Jun 23, 2016

Defining Analytics

We tell people that Database In-Memory is all about improving analytic processing. The definition that I use is "using aggregation to find patterns or trends in data". I believe I saw this used in one of Juan Loaiza's OpenWorld presentations and it made sense to me. After all Database In-Memory is really good at scanning and filtering a lot of data, and most of the articles on this blog echo that theme.

The problem is, the definition that I use doesn't necessarily make sense for everyone. When we do presentations or give webinars many people don't seem to really get what we mean by analytics.

[Read More]

Friday Jun 17, 2016

Questions You Asked: Can I force a query to use the IM column store?

I was recently asked by a customer, "is it possible to force a query to use the In-Memory column store?" and I thought it would be a good idea to share the answer here on the blog.

The first thing that comes to mind when you starting thinking about forcing how the Oracle Database will execute a query is Optimizer hints. There is an INMEMORY hint available, but that hint just enables the use of the In-Memory column store (IM column store) if the INMEMORY_QUERY parameter has been set to DISABLE (default is ENABLE). If we look closer at the documentation for the hint, we see that it says "This hint does not instruct the optimizer to perform a full table scan. If a full table scan is desired, then also specify the "FULL hint".

So it appears that the answer is that you have to use the FULL hint to ensure that the access method is a full table scan which is a requirement for accessing the IM column store. If the INMEMORY_QUERY has been set to DISABLE then you would also need to use the INMEMORY hint. Of course this also assumes that the IM column store has been enabled and that the object has been enabled for in-memory.

[Read More]

Friday Jun 03, 2016

Questions You Asked: Why do I sometimes get better compression using Advanced Row compression compared to In-Memory Query compression?

The compression techniques that are use for in-memory compression are different from what we use for on-disk compression. The compression on-disk is designed to reduce the overall space usage for the table segments. While the compression algorithms used in memory are designed for query performance and speeding up the queries by being able to operate on the compressed format. Due to the different techniques, it is not uncommon to have table segments see different compression ratios and different sizes in-memory and on-disk.

[Read More]

Tuesday May 03, 2016

New Technical Briefs

We've added a new Technical Briefs section to the blog. If you scroll down on the blog you will see a new section titled "Technical Briefs" on the right hand side.

One of the briefs is an update for Oracle Database 12c on how to create SQL Monitor active reports and the other describes how to create an Optimizer trace file. Hopefully you will find these useful quick references.

Monday Apr 18, 2016

Why does BYTES_NOT_POPULATED suddenly show a non-zero value?

I recently got a question from a customer who has just begun adopting Database In-Memory and thought it was worth answering as a blog post in case anyone else had encountered the same scenario and was wondering what was going on.

Here is the question I got:

“I've observed a scenario where a table is fully populated in the In-Memory column store with 0 in the  BYTES_NOT_POPULATED column in v$im_segments, but after some time, the BYTES_NOT_POPULATED column shows a non-zero value. Why is Oracle purging part of my table from memory?”

Let me begin to answer this by assuring you all that Oracle does not purge your data out of the In-Memory column store.

[Read More]

Monday Apr 11, 2016

Oracle OpenWorld 2016 call for papers has begun!

Believe it or not, it's time to start thinking about Oracle OpenWorld 2016!

The Oracle OpenWorld 2016 call for papers is now opens! Oracle customers and partners are encouraged to submit proposals to present at this year's Oracle OpenWorld conference, which will be held September 18 - 22, 2016 at the Moscone Center in San Francisco. Details and submission guidelines are available on the Oracle OpenWorld Call for Papers web site. The deadline for submissions is Friday,  May 9, 11:59 p.m. PDT.

We look forward to checking out your sessions on Oracle Database In-Memory and how it has changed the way you do business!

Friday Mar 25, 2016

Why didn't Database In-Memory improve the performance of my query?

After I posted last weeks blog post on identifying analytic queries that benefit from Database In-Memory, I got the following question, Why didn't Database In-Memory improve the performance of my query?"

I've simplified the query in question down to the following,

SELECT Count(DISTINCT cust_id)
FROM ssh.sales
WHERE amount_sold > 1;

So, why didn’t Database In-Memory improve the performance of this query?

[Read More]

Friday Mar 18, 2016

How do I identify analytic queries that will benefit from Database In-Memory?

Although Database In-Memory can be used in both Enterprise OLTP systems and Data Warehouses, only analytic queries will benefit from accessing data from the In-Memory column store (IM column store). Remember the IM column store enables large volumes of data to be rapidly scanned and processed using a variety of optimizations. Simpler transaction based queries, such as a primary key look up do not benefit from the IM column store and are automatically directed to the buffer cache.

But how do you identify an analytic query?

An analytic query is one where a large amount of data is scanned and filtered to return critical data that drives better business decisions. It doesn’t necessarily mean a query that contains one of Oracle’s analytical functions but they can also see benefits. A star query is a good example of such a query. By star query, I mean a query where a large fact table (center of the star) is joined to a number of smaller dimension tables (the points of the star).

If you don’t have a simple star schema (most people don’t) then you can identify a good candidate query by examining some key characteristics.

[Read More]

Monday Mar 14, 2016

Oracle Database In-Memory at Collaborate 16 in Las Vegas

Andy Rivenes (@TheInMemoryGuy) from the Oracle Database In-Memory team will be presenting two hands-on-labs and will be co-presenting a session at this year's IOUG Collaborate 16 conference, in Las Vegas April 10 - 14. Below are details on the sessions he will be presenting. We hope that you have an opportunity to check out some of these sessions if you plan to attend the event!

[Read More]

Saturday Feb 20, 2016

What is an In-Memory Compression Unit (IMCU)?

In our previous blog series "Getting Started", we described how the In-Memory column store (IM column store) is part of the System Global Area (SGA) and is allocated at instance startup by setting the initialization parameter inmemory_size. For the purposes of this blog entry we have a database running with an 800MB inmemory_size and an sga_target of 3008MB:

This results in the following SGA allocation:

What we didn’t explain was that the IM column store is actually divided into two pools, a 1MB pool and a 64KB pool.

[Read More]

Friday Feb 12, 2016

How do I limit the amount of memory each PDB can use in the IM column store?

In case you aren’t familiar with what a PDB is, let me begin by explaining what a Multitenant environment is and how PDBs fit into it.

Oracle Multitenant is a new database consolidation model in Oracle Database 12c in which multiple Pluggable Databases (PDBs) are consolidated within a single Container Database (CDB). While keeping many of the isolation aspects of single databases, Oracle Multitenant allows PDBs to share the system global area (SGA) and background processes of a common CDB.

When used with Oracle Database In-Memory, PDBs also share a single In-Memory column store (IM column store) and hence the question, "How do I control how much memory each PDB can use in the IM column store?"

[Read More]

Friday Nov 20, 2015

What happens to a table In-Memory if one of my RAC nodes goes down?

We’ve written a number of blog posts on how Database In-Memory behaves in a RAC environment but recently we’ve gotten a lot of questions regarding what happens if one of the RAC nodes should fail. So, I thought I would try tackle this question and point out a couple of other interesting aspects of running Database In-Memory on RAC in this post.

Quick Recap

If you recall from part 1 of our RAC series each RAC node has it’s own In-Memory column store (IM column store). When a table is populated into memory in a RAC environment it will be distributed across all of the IM column stores in the cluster. That is to say, a piece of the table will appear in each RAC node.

Let’s take a look at an example using the LINEORDER table, which has 5 million rows in it and is approximately 550MB in size on my 3 node RAC cluster.

[Read More]

Friday Nov 13, 2015

Questions You Asked: When using interval partitioning, will new partitions be placed In-Memory?

This is the second in our series of "Questions You Asked" and this time the question has to do with interval partitioning and whether newly created partitions will be populated into the IM column store.

We'll begin our experiment by creating an interval partitioned table based on the SUPPLIER table from our SSB schema. I've added a key_no column to make the interval partitioning easy. We then insert data into three partitions and list the results. Note that the first partition is named p1 because we had to create at least one partition with our CREATE TABLE statement. The other two are system generated names and those partitions were created automatically as part of the interval partitioning feature when we ran the second and third insert statements as you can see below.

[Read More]

Friday Nov 06, 2015

Oracle OpenWorld 2015 Wrap up!

I can't believe another Oracle OpenWorld has been and gone.

Thanks to all those that attended the In-Memory sessions and stopped by the In-Memory demopod during this years Oracle OpenWorld. It was an amazing conference this year with lots of great technical sessions and interesting discussion at the demo grounds.

I've been inundated with requests to share the presentation we gave on In-Memory at the conference, so I thought it would be a good idea to share the links to the presentations here.

A number of Oracle Database In-Memory customers present on their experiences of using In-Memory. You can find details from their sessions below;

We also gave folks the opportunity to try out Oracle Database In-Memory for themselves in our hands-on lab, Oracle Database In-Memory Option Boot Camp: Everything You Need to Know, so I thought I would share the lab handbook in case anyone want to try some of the labs at home.

Finally there was a lot of talk about how Oracle Database In-Memory takes advantage of Software in Silicon ,so I thought it would be good to share this link to a short video of Juan Loaiza describing exactly what we mean by Software in Silicon.

We hope you enjoyed the conference as much as we did and we look forward to see you all again next year at OOW 2016 September 18–22, 2016 in San Francisco.

Monday Oct 26, 2015

Day 2 of Oracle OpenWorld 2015 October 26th

Oracle OpenWorld started yesterday and San Francisco is just buzzing with Oracle folks.

If you are attending the conference don't miss the opportunity to chat with the Oracle Database In-Memory team at one of our sessions or at the Oracle demogrounds.

Today kicks off with one of the best general sessions of the conference, General Session: Next-Generation Data Management Architecture Session GEN6765 at 11am in Marriott Marquis—Salon 7/8/9 (Yerba Buena Ballroom) in which Andy Mendelsohn, EVP of Database development, will share how Oracle Database 12c and its next-generation architecture are helping customers on their journey to the cloud.

Then in the afternoon the first 2 of the 8 customers speaking about their experiences with Oracle Database In-Memory will have their sessions. First up is Facebook’s Use of Oracle Database In-Memory from Inception to Rollout Session CON1685 at 2:45pm in Moscone South - room 301. Sudhi Vijayakumar will describe how Facebook evaluated Oracle Database In-Memory and then rolled it out in production to reap the benefits of faster response times for analytical queries.

Then at 4:00pm Five Ways Swiss Mobiliar Improved Business Value with Database In-Memory Session CON2715 in Moscone South - room 310. In this session folks from Swiss Mobiliar explain how they extended their traditional business intelligence (BI) systems to a more real-time solution using Oracle Database In-Memory. They will also compare this new solution to their original IBM DB2 solution.

Today is also your first chance to catch our hands-on-lab, Oracle Database In-Memory Bootcamp (Session HOL10483) in the Golden Gate room (25th Floor) at the Hotel Nikko. In this hands-on lab we'll provide a step-by-step guide on how to get started with Oracle Database In-Memory, how to identify which of the optimizations are being used, and how your SQL statements benefit from them. You will also get to experience firsthand just how easy it is to start taking advantage of this technology and the incredible performance improvements it has to offer.

Remember it's about a 15 minute walk from Moscone to the Hotel Nikko, so make sure you give yourself plenty of time to get there.

Members of the In-Memory team will also be available at booth SLD 28 from 10:00am to 6pm at the Oracle Database demogrounds in Moscone South, to answer any questions you might have. Remember the In-Memory demo booth is the best place to snag a limited edition In-Memory bumper sticker!


The Oracle Database In-Memory blog is written by the Oracle product management team and sheds light on all things In-Memory.


  • Oracle
  • Oracle Database In-Memory
« June 2016