Oracle 12c In-Memory database how to start

December 30, 2016 by Leave a comment 

The In-Memory Column Store feature that was introduced by Oracle in the database version 12c (12.1.0.2) brings the solution for accelerating performance of database-driven business decision-making to real-time speeds. Since it is an extra-license feature for which Oracle makes you pay around 50% on top of your CPU license (similar to RAC option), you probably ask yourself a few valid questions. Don’t we cache everything already in memory anyway? Is it really required for my application, my company? Will it work for my workload at all? In this post I’ll give you a short guideline and introduction into Oracle database In-Memory feature.

First of all me personally and so probably you do not know any database that works only on disk. Indeed we cache most of our data, code and intermediate results in memory already. Furthermore there are some extra Oracle database performance features that help you to achieve that fairly efficiently, for example:

– KEEP/RECYCLE Pools
– RESULT Cache
– 12c Big Table Caching
– 12c Full Database Caching
– ….

The key point of Oracle In-Memory is not “What to cache” but “How”. So the major difference of Oracle In-Memory Column Store is that it enables individual database segments to be loaded into memory in the compressed columnar format. This technique enables segment scans to perform much faster than the traditional on-disk formats, providing performance boost for analytical and reporting workload.

Oracle 12c database In-Memory Architecture dadbm.com

Oracle 12c database In-Memory Architecture

Oracle 12c In-Memory Store benefits

– The amount of data that can fit in memory is greatly increased using columnar compression
– Considerable performance increase mostly for analytic and reporting queries
– Make DML run faster by removing most of your existing analytical or “performance” indexes
– Less I/O pressure on storage system
– Great flexibility and granularity by loading individual segments into In-Memory Store
– Application transparency (no code rewrite required)
– Easy setup of the In-Memory feature
– Use the same Oracle database and its functionalities with the new In-Memory technology

When to use Oracle 12c In-Memory Store?

Below I recommend a simple decision tree to start considering Oracle 12c In-Memory Store for your application:

1. If your end-users have a “real” performance problem and it is query related

2. If it can not be solved with the “classic” database tuning

4. If the (part of) application mostly consist of analytic and reporting queries that operate on few columns returning many rows rather than for OLTP operating on few rows returning many columns.

5. It’s useful not only for huge systems. Smaller systems can get independent from I/O.

6. If the In-Memory performance benefit can outperform the additional Oracle license costs.

Of course you can not maybe get precise answers to all the above questions without proper testing. But the beauty of In-Memory feature is that it’s already built in the Oracle database engine and there is a tiny small effort to start using and testing it. See a few short steps on the way to Oracle In-Memory below.

Oracle 12c In-Memory Store setup

Step 1 – Clarify the In-Memory license topic

Usually you can easily test it for 30+ days or/and clarify with your Oracle account

Step 2 – Enable Oracle In-Memory feature

– Use Oracle Database Enterprise Edition 12.1.0.2+

– Ensure database minimum compatibility with parameter:

COMPATIBLE = 12.1.0.0.0

– Set the amount of memory for Oracle In-Memory Column Store (which is a part of SGA):

ALTER SYSTEM SET inmemory_size=60000M SCOPE=SPFILE;

– Restart the database

Note, the required size of the In-Memory pool depends on the raw size of the segments you want to load into In-Memory Store and the actual segment compression rates. As the initial size you might allocate around 5 – 15% of the raw data.

Step 3 – Identify required segments for Oracle In-Memory Store

There are different options to identify best candidates for In-Memory store:

– Based on application logic and developers experience

– Very hot data, large segments (not less than 1MB)

– Segments frequently accessed by analytical queries, operating on many rows returning few columns

– Based on Oracle In-Memory Advisor: command line (MOS Doc ID 1965343.1) or OEM 12c+

– Based on Oracle segment statistics, number of column scans, AWR reports

Note, Oracle will still use Buffer Cache based on optimizer decisions for:

– segments that are not defined for Oracle In-Memory Store

– Fetch-by-ROWID

– Queries with 1 row processing

– All DML

Step 4 – Define database segments to be loaded into Oracle In-Memory Store

CREATE TABLE large_tab (c1 …) INMEMORY;

ALTER TABLE | PARTITION … INMEMORY;

ALTER TABLE sales NO INMEMORY;

CREATE TABLE countries_part ... PARTITION BY LIST ..
( PARTITION p1 .. INMEMORY, PARTITION p2 .. NO INMEMORY);

CREATE TABLE myimtab (c1 NUMBER, c2 CHAR(2), c3 DATE)
INMEMORY NO INMEMORY (c1);

ALTER TABLE tab INMEMORY MEMCOMPRESS FOR CAPACITY LOW (c2, c3)
NO INMEMORY (c1, c4);

ALTER TABLE myimtab NO INMEMORY (c2);
CREATE TABLE test (…) INMEMORY PRIORITY CRITICAL;

Use following SQL to list tables for In-Memory Store:

SELECT owner, table_name, inmemory, inmemory_compression, inmemory_priority priority, inmemory_distribute RAC, inmemory_duplicate DUP
FROM dba_tables
WHERE inmemory = 'ENABLED'
/

Notes:

– It is recommended to enable all columns for In-Memory Store at once rather than using ALTER TABLE for each column, as it is more efficient.

– Changing the compression level of columns with the ALTER TABLE statement results in an update of the dictionary, but does not force a repopulation of existing data into the In-Memory column store

– There are different In-Memory compression options, but I recommend the default one: FOR QUERY LOW

Step 5 – Populate objects into Oracle In-Memory Store

New background processes IMCO, SMCO, Wnnn populate tables into In-Memory Store fully or partially (if there is not enough space in the In-Memory Store) at the following occasions:
– On database startup based on population priorities (CRITICAL, LOW)
– On demand population – at first object access (usually when priority=NONE)
– Manual object population (see below):

exec DBMS_INMEMORY.POPULATE(schema_name => user, table_name => 'SALES');

Use following SQL to see the objects in the In-Memory Store:

select v.owner, count(*), sum(v.bytes)/1024/1024 orig_size_mb, sum(v.inmemory_size)/1024/1024 in_mem_size_mb, sum(v.bytes)/sum(v.inmemory_size) comp_ratio
from v$im_segments v
group by v.owner
order by 1,2,3
/

Step 6 – Run your tests

Run your tests against the segments that were populated into In-Memory Store.

– Usually applications can transparently use In-Memory Store without any changes
– Ensure the query optimizer uses In-Memory Store by looking into SQL execution plans
– Analytics indexes can be eliminated being replaced by scans in the In-Memory Store.
If optimizer still uses index access rather than In-Memory scan, drop the corresponding indexes.
– All Oracle database mechanisms and features work transparently with new In-Memory feature
– Increasing query parallelization (DOP) usually speeds it up considerably:

SELECT /*+ full(s) parallel(s,4) */ count(amount_sold) FROM sales s;
SELECT /*+ full(s) parallel(s,16) */ count(amount_sold) FROM sales s;

– You can compare response times with and without In-Memory Store by disabling In-Memory functionality in your session:

ALTER SESSION SET inmemory_query = ENABLE;
ALTER SESSION SET inmemory_query = DISABLE;

Step 7 – Understand the general Oracle In-Memory Store architecture

See the architecture diagram above. More information on Oracle In-Memory architecture in the next posts…

So that was a short guideline and introduction into a new Oracle database 12c performance feature In-Memory Store along with how to start using it. Stay tuned for more updates on this topic…

Enjoyed this article? Please share it with others using the social site of your choice:

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email