Applies to: 
Oracle database – 12.1.0.2

Author:

Mahesh Reddy M

Introduction:

With the introduction of Oracle Database 12c In-Memory, a single database can now efficiently support mixed workloads, delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting. In-Memory enables both datamarts and data warehouses to provide more ad-hoc analytics, giving end-users the ability to ask multiple business driving queries in the same time it takes to run just one now. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patch set.

Oracle Database 12c In-Memory Architecture:

Traditionally data is stored in ROW format only, where as In-Memory database store data in columnar format only. So, oracle database 12c supports dual format architecture.

1. Whenever data are requested for read/write operations (data manipulations), they are loaded into the traditional Row Store (Buffer Cache)

2. Whenever data are requested for read-only operations, they are populated into a new In-Memory Column Store. This population, of course, includes a transformation from row to columnar format.

3. Whenever a transaction that includes inserts, updates, or deletes is committed, the new data will immediately and simultaneously appear in both the row store and the in-memory column store. Therefore both stores are transactionally consistent

The In-Memory Column store:

Database In-Memory uses an In-Memory column store, which is a new component of the System Global Area (SGA), called the In-Memory Area. Data in the IM column store does not reside in the traditional row format, instead it uses a new column format. The IM column store does not replace the buffer cache, but acts as a supplement, so that data can now be stored in memory in both a row and a column format

The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0). The current size of the In-Memory area is visible in V$SGA

IM Column store sub divided into two pools:

  1. 1 MB pool—Store the actual column formatted data
  2. 64k Pool--- Store the metadata about the objects.

We can see the amount memory available in each pool using below query

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES      USED_BYTES    POPULATE_STATUS     CON_I

1MB POOL              2.5767E+11            2.3569E+11            DONE                               1

64KB POOL             6.4408E+10            876347392             DONE                               1

1MB POOL               2.5767E+11            2.3569E+11           DONE                              2

64KB POOL              6.4408E+10           876347392             DONE                               2

1MB POOL                2.5767E+11           2.3569E+11           DONE                               3

64KB POOL              6.4408E+10           876347392             DONE                               3

Enable IM Column store:

As a static pool, any changes to the INMEMORY_SIZE parameter will not take effect until the database instance is restarted. It is also not impacted or controlled by Automatic Memory Management (AMM). The In-Memory area must have a minimum size of 100MB.

sql> ALTER SYSTEM SET SGA_TARGET=500G SCOPE=SPFILE;
sql> ALTER SYSTEM SET INMEMORY_SIZE=300G SCOPE=SPFILE;
sql>SHUTDOWN IMMEDIATE;
sql>STARTUP;

ORACLE instance started.

Total System Global Area 4.2950E+11 bytes
Fixed Size 7677400 bytes
Variable Size 2.0938E+10 bytes
Database Buffers 8.5899E+10 bytes
Redo Buffers 529190912 bytes
In-Memory Area 3.2212E+11 bytes
Database mounted.
Database opened.

We can see the In-Memory settings using below query

SQL> SHOW PARAMETER INMEMORY

NAME                                     TYPE       VALUE
----------------------------------   -----------    -----
inmemory_clause_default                string
inmemory_force                         string       DEFAULT
inmemory_max_populate_servers          integer        1
inmemory_query                         string       ENABLE
inmemory_size                          biginteger    300G
inmemory_trickle_repopulate_servers_   integer       1
percent
optimizer_inmemory_aware               boolean      TRUE

We can set inmemory_size parameter at CDB and PDB level also. If you set this parameter at PDB level no need to restart instance or PDB. The sum of all PDBs value is less than on equal to CDB value.

We can enable and disable In-Memory option at PDB level shown below

Connect to PDB and then execute below command

Disable:

Alter system set inmemory_size=0; or

Alter system reset inmemory_size;

Enable:

Alter system set inmemory_size=20G;

In-Memory priority levels:

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. Database In-Memory adds a new INMEMORY attribute for tables and materialized views.

We can enable INMEMORY attribute at

Tablespace, Table, (sub) partition and materialized view.

If you enable this attribute at tablespace level, then all the tables and meterilized views in the tablesapce will be enabled for IMCOLUMN store by default.

alter tablespace quest INMEMORY;

if you enable this attribute at table level means all the columns of table should be populated into the IMCOLUMN store. However, it is possible to populate only subset of columns into IMCOLUMN store.

Alter table quest_tab INMEMORY NO Inmemory (EMP);

Similarly, for partitioned table

Alter table quest_tab INMEMORY MODIFY Partition  quest_part_1 No Inmemory;

Background Processes:

IMCO:  The IMCO background process initiates population (prepopulation) of in-memory enabled objects with priority LOW/MEDIUM/HIGH/CRITICAL.

SMCO: SMCO dynamically spawns slave processes (Wnnn) to implement these tasks.

Wnnn: Wnnn processes execute in-memory populate and in-memory repopulate tasks for population or repopulation of in-memory enabled objects.

Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after they are scanned (queried) for the first time. The order in which objects are populated is controlled by the keyword PRIORITY, which has five levels. The default PRIORITY is NONE, which means an object is populated only after it is scanned for the first time

Different priority levels controlled by the PRIORITY sub clause of the INMEMORY clause

Priority Level    Description

CRITICAL        Object is populated immediately after the database is opened

HIGH                Object is populated after all CRITICAL objects have been populated                                             

MEDIUM          Object is populated after all CRITICAL and HIGH objects have been populated

LOW                 Object is populated after all CRITICAL, HIGH, and MEDIUM objects have been populated

NONE              Objects only populated after they are scanned for the first time (Default)

 Objects that are smaller than 64KB are not populated into memory, as they will waste a considerable amount of space inside the IM column store as memory is allocated in 1MB chunks.

Ex: alter table quest inmemory priority critical;

In-Memory Compression Techniques:

The IM column store uses special compression formats optimized for access speed rather than storage reduction. The database increases speed in the following ways:

  • The compression formats enable the database to decrease the amount of memory processed for each column. SQL executes directly on the compressed columns.
  • The database uses SIMD vector (array) instructions to process an array of column values in a single CPU clock cycle. The database can store many values in a vector, which maximizes the performance benefits with SIMD vector processing.

In-memory compression is specified using the keyword MEMCOMPRESS, a sub-clause of the INMEMORY attribute. There are six levels, each of which provides a different level of compression and performance.

No Memcompress: Data is populated to In-Memory without compression.

Memcompress for DML: It is mainly for DML performance and minimal compression.

Memcompress for Query Low: Optimized for Query performance (Default).

Memcompress for Query High:  Optimized for Query performance and also space saving

Memcompress for Capacity Low: More space saving compare to Query high and low

Memcompress for Capacity High: Optimized for space saving and little bit less performance.

Compression ratios can vary from 2X – 20X, depending on the compression option chosen, the data type, and the contents of the table.

Ex: alter table quest inmemory memcompress for query high;

In-Memory Column Store on RAC:

In Cluster environment each node has its own column store. Each node in the cluster should be maintain equal size of IM Column store. By default all objects populated into memory will be distributed across all of the IM column stores in the cluster.The distribution of objects across the IM column stores in a cluster is controlled by two additional subclauses to the INMEMORY attribute: DISTRIBUTE and DUPLICATE (Enineered systems only).

Distribute:

The objects are distributed across the cluster is controlled by distribute sub-clause. You can distribute the objects following ways

Distribute by rowed ranage—Distrubute the objects by rowed to different nodes

Distribute by Partition – distribute the partitions to nodes with in the cluster

Distribute by sub partition – distribute the sub partition to different nodes.

Ex: alter table quest inmemory distribute by partition;

Here, importatnt to say that Oracle In-Memory RAC is shared-nothing architecture for queries. i.e if you raise a query againest inmemory data objects (assume objects are distrubuted in Two IM-Column stores),can access the data resides in the affinity node only, means do not share the IMCU's (Inmemory compression units) accross the instances in the cluster.

So, you can set the degree of parallelism(DOP) to AUTO. The parallel query cordinator identify the other instances of IMCU' location. if you cann't set the DOP to AUTO, the parallel query co-ordinator won't use the other instances of IMCU's.

set the degree of parallel to auto

alter system set parallel_degree_policy=AUTO scope=both sid='*';

Duplicate:

To increase the availability, objects are copied into all instances. You can use the duplicate sub-clause to specify the inmemory table is stored in all instances with in the cluster. For non-engineered systems this option wont work.

Ex; alter table quest inmemory duplicate all;

       quest table is stored in all instances.

Monitoring In-memory Objects:

For monitoring inmemory objects, oracle introducing two new V$views are

v$IM_SEGMENTS or  v$IM_USER_SEGMENTS and v$IM_COLUMN_LEVEL.

Using these views we can find out the how many objects are currently populated into the IMCOLUMN store.

EX:

set linesize 256

set pagesize 999

select segment_name,ROUND(SUM(BYTES)/1024/1024/1024,2) "DATA GB",

ROUND(SUM(INMEMORY_SIZE)/1024/1024/1024,2) "IN-MEM GB",

ROUND(SUM(BYTES-BYTES_NOT_POPULATED)*100/SUM(BYTES),2) "% IN_MEM",

ROUND(SUM(BYTES-BYTES_NOT_POPULATED)/SUM(INMEMORY_SIZE),2) "COMP RATIO"

from V$IM_SEGMENTS

group by owner,segment_name

order by SUM(bytes) desc;

 

SEGMENT_NAME              ORIG GB     IN-MEM GB   % IN_MEM    COMP RATIO

H_LINEITEM                         317.27          68.2                88.77           4.13

H_PARTSUPP                         35.17        21.04                100             1.67

  

EX:

set linesize 256

set pagesize 999

set verify off

col OBJECT format a30

SELECT owner||'.'||table_name OBJECT,

inmemory INMEMORY,inmemory_priority PRIORITY,

inmemory_distribute DISTRIBUTE,inmemory_compression COMPRESSION,

inmemory_duplicate DUPLICATE

FROM all_tables

where owner='QUEST'

ORDER BY inmemory, owner||'.'||table_name;

OBJECT               INMEMORY   PRIORITY   DISTRIBUTE   COMPRESSION              DUPLICATE

H_NATION         ENABLED      CRITICAL          AUTO           FOR QUERY HIGH            NO DUPLICATE

H_REGION         ENABLED     CRITICAL          AUTO           FOR QUERY HIGH            NO DUPLICATE

H_CUSTOMER

H_SUPPLIER

  In the example above you will notice that two of the tables – Customer and Supplier – don’t have a value for the INMEMORY column. The INMEMORY attribute is a segment level attribute. Both Customer and Supplier are partitioned tables and are therefore logical objects. The INMEMORY attribute for these tables will be recorded at the partition or sub-partition level in *_TAB_ (SUB) PARTITIONS.

Three additional columns – INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, and INMEMORY_COMPRESSION – have also been added to the *_TABLES views to indicate the current InMemory attributes for each table.

References:

Oracle Database In-memory