9 years ago

How to Enable Oracle Database In-Memory

An image of Dual-Format Database.

Oracle Database In-Memory allows you to use a single database for all of your workloads.  That means that Oracle Database In-Memory helps you get the best performance for your transactions while also ensuring that you have real-time reporting and analytics.

Oracle Database In-Memory is a new component of the SGA, or shared global area, thus ensuring that it is integrated fully into your database core.  You do not have to do anything, really.  Once you have the Oracle Database installed, the Oracle Database In-Memory is likewise installed.

During the launch of this product, Oracle announced that the feature is enabled automatically.  And a lot of people thought it meant that all they have to do was to install Oracle Database and the said program would kick in without them having to do anything.

The truth, however, is that Oracle Database In-Memory is not usable by default yet.  Oracle claims that it is a bug that they are working on.  So do not be confused and start working on enabling Oracle Database In-Memory for you to take full advantage of it.

In the meantime, Oracle has provided these helpful steps for you to enable this feature.  Take a look at this page for more information.

Long story short, here are the steps you need to enable Oracle Database In-Memory:

  1. Change the allocated memory of the IM column store to a value greater than 100MB. ALTER SYSTEM SET inmemory_size = 10G scope=spfile;
  1. Change the SGA_Target parameter to a size that would be able to accommodate the new IM column store value you have specified in step 1, plus all the other processes that it needs to take care of.  By default, Oracle puts in โ€œ71Gโ€ for this parameter, so it would be safe to set your SGA_Target parameter to โ€œ71Gโ€ plus whatever amount you specified in step 1. ALTER SYSTEM SET sga_target = 81G scope=spfile;
  1. If you are working with a large database, you need to know that you do not need to put your entire database into IM column store.  Instead, you can specify a table, a table space, a partition and other elements that are most important for your database performance.  For example, to enable in-memory for a table named Cornerstone, you would need to use: ALTER TABLE OWNER.cornerstone INMEMORY;

Oracle Database In-Memory typically puts your table into the in-memory column when you first access it.  Or it does so on demand.  To check if you have successfully enabled in-memory for your Cornerstone table, run a query on the table after you access it.  Take a look at the v$IM_SEGMENTS, and you should be able to see that your table has a COMPLETED status.  Then you should take a look at the feature tracking information and confirm that the feature is now enabled AND used.

Conversely, you can use the NO INMEMORY attribute to get the table out of the IM column store.

ALTER TABLE OWNER.cornerstone NO INMEMORY;

Need help enabling and using Oracle Database In-Memory?  Call Four Cornerstone today!

Photo by Oracle.

Scroll to Top