Friday Jul 22, 2016

New In-Memory Advisor Version Available

A new version of the Oracle Database In-Memory Advisor has been released. The In-Memory Advisor is designed to help identify analytic processing that may benefit from the use of Oracle Database In-Memory. It supports Oracle Database and later so you don't have to upgrade to Oracle Database 12c to try it out. The new version includes a number of enhancements including an enhanced and simplified script to run the Advisor and get recommendations.

You can get more information about the In-Memory Advisor here.

Friday Jul 01, 2016

Questions You Asked: Where did the other bloom filter go?

A couple of weeks ago at the Spring Conference of the Bulgarian Oracle User Group, Joel Goodman gave a great session on Bloom Filters and how the can improve the performance of hash joins in the Oracle Database especially in the In-Memory column store. At the end of that session the chap sitting in front of me turned around to show me an execution plan they got while testing Oracle Database In-Memory and posed the following question, “Where did the other bloom filter go?”

[Read More]

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]

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


« July 2016