Getting started with Oracle Database In-Memory Part I - Installing & Enabling

Now that Oracle Database 12.1.0.2 has been officially released, I can finally start sharing more technical details on how Oracle Database In-Memory (Database In-Memory) works.

I thought we should start this series of posts right at the very beginning by answering the most fundamental question, how and when is Database In-Memory installed and enabled.

Let’s start by doing a clean install of 12.1.0.2 and allowing the installer to create a typical single instance database.

So, has In-Memory been installed?

Yes, Oracle Database In-Memory is installed. How do I know? Oracle Database In-Memory is not a bolt on technology to the Oracle Database. It has been seamlessly integrated into the core of the database as a new component of the Shared Global Area (SGA). When the Oracle Database is installed, Oracle Database In-Memory is installed. They are one and the same. You can’t unlink it or choose not to install it.

The more important question is if In-Memory is automatically enabled or not?

The answer is NO. In order to prove this we are going to need to look at some of the new initialization parameters that control In-Memory.

Six new initialization parameters with the INMEMORY prefix have been introduced to directly control the different aspects of the new in-memory functionality. There is also a new optimizer parameter that controls whether queries can use the INMEMORY or not.

Right now we are only interested in one of these parameters, INMEMORY_SIZE to determine if In-Memory is enabled.

Database In-Memory uses an In-Memory column store (IM column store), which is the new component of the SGA, called the In-Memory Area. Data in the IM column store does not reside in the traditional row format used by the Oracle Database; instead it uses a new column format. The size of the IM column store is controlled by the INMEMORY_SIZE parameter. As you can see the INMEMORY_SIZE parameter is set to 0 and therefore Database In-Memory is not enabled, as there is no IM column store allocated. We can also confirm the In-Memory Area is not allocated by querying v$SGA.

Still don't trust me? Let’s confirm Database In-Memory is not enabled by examining the feature tracking information. I’m going to force the feature-tracking table to be updated before I query it.

If Database In-Memory isn’t enabled out of the box how do you enable it?

Enabling Database In-Memory is actually a multi-step process.

Step1: First we must allocate memory for the IM column store by setting the INMEMORY_SIZE parameter to a non-zero value that is greater than 100MB.

ALTER SYSTEM SET inmemory_size = 20G scope=spfile;

Since the IM column store is part of the SGA, we also need to ensure the SGA_TARGET parameter is set large enough to accommodate the new IM column store and all of the other existing components (buffer cache, shared pool, large pool etc.). By default, the installer set the SGA_TARGET to 71G, so I’m going to bump it by 20G.

ALTER SYSTEM SET sga_target = 91G scope=spfile;

Now let’s bounce the database so these parameter changes can take effect.

As you can see, we now have an IM column store. But Database In-Memory is still not in use because no objects have been populated into the IM column store. To confirm this we can look at two new v$ views, v$IM_SEGMENTS and v$IM_USER_SEGMENTS that indicate what objects are in the In-Memory Column Store.

We can also confirm it by checking the feature tracking information again.

Step 2: Unlike a pure In-Memory database, not all of the objects in an Oracle database need to be populated in the IM column store. The IM column store should be populated with the most performance-critical data in the database. Less performance-critical data can reside on lower cost flash or disk. Of course, if your database is small enough, you can populate all of your tables into the IM column store. Only objects with the INMEMORY attribute are populated into the IM column store. The INMEMORY attribute can be specified on a tablespace, table, (sub)partition, or materialized view. In this case let's enable the INMEMORY attribute on one of the user tables CUSTOMERS.

ALTER TABLE SSB.customers INMEMORY;

By default Oracle automatically decides when to populate the table into the In-Memory Column Store. This is also referred to as “on demand”, as Oracle typically populates the table after it has been accessed for the first time. So, let’s run a query on the CUSTOMERS table.

SELECT cust_valid, Count(*)
FROM customers
GROUP BY cust_valid
;

Now if we check v$IM_SEGMENTS we see the CUSTOMERS table has been populated in the IM column store.

If we check the feature tracking information now, we will see Database In-Memory is enabled and being used.

If you want to remove a table from the IM column store you simply need to specify the NO INMEMORY attribute.

ALTER TABLE SSB.customers NO INMEMORY;

In next week's post, I will explain in a lot more detail all of the different sub-clause of the INMEMORY attribute and what all of the columns in the new v$ views represent.


Comments:

Thanks for this Maria, I know there have been some concerned bloggers expressing uncertainty as to how easy it might be to accidentally enable the new IN_MEMORY option and what the implications of INMEMORY_QUERY defaulting to ENABLE might mean come an Oracle license audit. I expect this post will be referenced many times in that it clearly shows how the IN_MEMORY option is enabled and gives visibility to the not so well known DBMS Feature Usage Statistics that DBAs can add to their monitoring and alerting processes.

I look forward to you perhaps discussing some straightforward high impact use cases that we all can use to demonstrate the powerful capabilities of Oracle 12c IN_MEMORY option. @DBAStorage

Posted by Peter Herdman-Grant on July 28, 2014 at 03:27 PM PDT #

I would have been happier if the INMEMORY attribute could be set at the Column level. I may have a table with 100 columns of which I need only 2 columns to be In-Memory. Is that possible ?

Posted by Hemant K Chitale on July 28, 2014 at 08:39 PM PDT #

Hi,

Does the inmemory_size parameter needs be explicitly set before someone can create a table with the inmemory clause or will in that case Oracle automatically allocates space within the sga?

Posted by Freek on July 29, 2014 at 12:41 AM PDT #

Hi,

No setting the INMEMORY attribute on a table does not automatically allocate space within the SGA.

Setting the INMEMORY attribute when the INMEMORY_SIZE parameter is set to zero means the attribute is effectively ignored, as there is no IM column store that the table can be populated into.

Thanks,
Maria

Posted by Maria Colgan on July 29, 2014 at 10:44 AM PDT #

Hi Hermant,

I am sure Maria can answer better that me your question but I think what you are asking for is possible

http://docs.oracle.com/database/121/ADMIN/memory.htm#BABGGDGD

Regards,
Alex

Posted by guest on July 29, 2014 at 11:32 AM PDT #

Hemant,
Thats possible.

ALTER TABLE oe.product_information
INMEMORY MEMCOMPRESS FOR QUERY (
product_id, product_name, category_id, supplier_id, min_price).

Posted by guest on July 29, 2014 at 05:14 PM PDT #

Hi Herbert,

It is possible to populate just a subset of columns from a table into the IM column store but perhaps not the way you think.
By default all of the columns in a table or partition inherit their In-Memory attribute and sub-attribute from the table. All of the column are marked INMEMORY if the table is.

However, you do have the ability to override the inherited In-Memory attributes on a column. For example you can exclude the column by marking a column NO INMEMORY. Or you can change the compression type etc.

But a column can't have the INMEMORY attribute if the table is doesn't.

For example,

Alter table sales INMEMORY NO INMEMORY(prod_id);

This statement marks the SALES table INMEMORY but excludes the prod_id column from being populated into memory.

I will post a lot more about the different aspects of the syntax in next weeks blog.

Thanks,
Maria

Posted by Maria Colgan on July 29, 2014 at 09:30 PM PDT #

@Hemant K Chitale:

you can enable in-memory even at column level,

http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF56746
http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF56751

-Rajesh.

Posted by Rajeshwaran, Jeyabal on July 29, 2014 at 09:31 PM PDT #

I have a standard Oracle table called EMPLOYEE. I first executed

ALTER TABLE employee INMEMORY

I then ran the query

SELECT ename FROM employee WHERE salary > 10

The feature tracking view reports that I was using the in-memory option this query, even though I never set the inmemory_size parameter so I don’t have a column store. Is this correct behavior?"

Posted by guest on July 30, 2014 at 09:20 AM PDT #

Since the inmemory_size parameter is set to 0, there is no IM column store so we cannot populate the employee table into memory.

Recording that the In-Memory option is in use in this case is a bug and we will fix it in the first patchset update coming in October.

Thanks,
Maria

Posted by guest on July 30, 2014 at 09:46 AM PDT #

Hi Maria,

In the example you talked about SGA_TARGET, what about automatic memory management and inmemory_size? Can you make use to AMM and in memory_size, and does this mean the defined size is the hard limit?

Thanks.

Posted by guest on August 22, 2014 at 01:13 PM PDT #

Excellent post - thanks for the clarification, Maria. The URL for this article will be widely shared, I'm sure.

One slight suggestion: if you could include the actual text of cool SQL (like that query against dba_feature_usage_stats) in-line, your readers would appreciate the typing savings... :-)

Posted by Jim Carter on September 17, 2014 at 06:57 AM PDT #

If there is a system failure or database crashes , is there 100% recovery/consistency for the in-memory data?

Posted by guest on October 23, 2014 at 05:18 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

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

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today