Oracle In-Memory Database

Introduction:

• • • The In-Memory Column Store (IM column store) allows users to store Oracle Database objects like columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. It is mainly used for analytic queries found in business intelligence products.
    • The In-Memory Column Store is the new feature from Oracle Database version 12.1.0.2.
    • The IM column store is a separately licensed option of Oracle Database Enterprise Edition.
    • The In-Memory column store is a new section of the SGA, sized using the INMEMORY_SIZE initialization parameter.
    • You can choose to store specific groups of columns, whole tables, materialized views or table partitions in the store.
    • Alternatively, you can enable IM column store at the tablespace level, so all tables and materialized views in the tablespace are automatically enabled for the IM column store.

The following commands have been modified to include additional in-memory clauses.

• • • CREATE TABLE
    • ALTER TABLE
    • CREATE TABLESPACE
    • ALTER TABLESPACE
    • CREATE MATERIALIZED VIEW
    • ALTER MATERIALIZED VIEW

Where to use this feature:

• • • Large scans that apply “=”, “<“, “>” and “IN” filters.
    • Queries that return a small number of columns from a table with a large number of columns.
    • Queries that join small tables to large tables.
    • Queries that aggregate data.

Where NOT TO use this feature:

• • • Queries with complex predicates.
    • Queries that return a large number of columns.
    • Queries that return large numbers of rows.
    • Queries with multiple large table joins.
    • The important thing to remember here is *you* will be responsible for deciding which objects will benefit the most from inclusion in the IM column store. If you choose wisely you will see big improvements in performance. If you choose badly, you will waste a lot of memory that could be used by the buffer cache.

Enabling In-Memory feature:

• • • In-Memory Column Store is part of the SGA (Shared Global Area), so the SGA must be capable of containing the amount of memory you want to assign to the INMEMORY_SIZE parameter.
    • If you are using AMM (MEMORY_TARGET), you will need to extend this to account for the INMEMORY_SIZE parameter value.
    • If you are using ASMM (SGA_TARGET), you will need to extend this to account for the INMEMORY_SIZE parameter value.
    • Assuming the COMPATIBLE parameter is set to 12.1.0 or higher and there is enough room in the SGA to hold the IM column store, the following SQL commands will enable the IM column store. For example, here we are setting the INMEMORY_SIZE parameter to 2G.
    • ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE;
    • ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;
    • SHUTDOWN IMMEDIATE;
    • STARTUP;

ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size: 2929552 bytes
Variable Size: 419433584 bytes
Database Buffers: 637534208 bytes
Redo Buffers: 13844480 bytes
In-Memory Area: 2147483648 bytes
Database mounted.
Database opened.
SQL>
Notice the “In-Memory Area” line produced during the startup.
Disabling In-Memory feature:

• • • There are many ways to disable the IM column store, depending on what we are trying to achieve.
    • Setting the INMEMORY_FORCE parameter to “OFF” means objects will not be maintained in the IM column store. Switching it back to “DEFAULT” returns to the default behavior.

System level

• • • ALTER SYSTEM SET INMEMORY_FORCE=OFF;
    • ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;
    • Setting the INMEMORY_QUERY parameter to “DISABLE” means the optimizer will not consider the IM column store to optimize queries. Switching it back to “ENABLE” reverts it to the default functionality.

System level

• • ALTER SYSTEM SET INMEMORY_QUERY=DISABLE;
  • ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;

Session level

• • ALTER SESSION SET INMEMORY_QUERY=DISABLE;
  • ALTER SESSION SET INMEMORY_QUERY=ENABLE;
  • To disable the IM column store completely and release the memory, reset the INMEMORY_SIZE parameter.
  • ALTER SYSTEM RESET INMEMORY_SIZE SCOPE=SPFILE;
  • SHUTDOWN IMMEDIATE;
  • STARTUP;

Manage IN-Memory on TABLE, COLUMN, M-VIEW and TABLESPACE level:

• • • In-Memory feature can be enabled/disabled using “INMEMORY/NO INMEMORY” clauses as mentioned in the following example SQL commands.

TABLE Level:

• • CREATE TABLE T1 (id NUMBER) INMEMORY; –enabling when the table is created
  • ALTER TABLE T1 NO INMEMORY; –disabling after the creation of the table with In-Memory feature

COLUMN Level:

• • CREATE TABLE T2 (id NUMBER, col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER) INMEMORY
  •                INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2)
  •                INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
  •                NO INMEMORY (id, col4);
  • ALTER TABLE T2 NO INMEMORY (col1, col2)
  •             INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
  •            NO INMEMORY (id, col4);
  • MATERIALIZED VIEW Level:
  • CREATE MATERIALIZED VIEW MV1 INMEMORY AS SELECT * FROM T1;
  • ALTER MATERIALIZED VIEW MV1 INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH;
  • ALTER MATERIALIZED VIEW MV1 NO INMEMORY;

TABLESPACE Level:

• • CREATE TS1 DATAFILE ‘/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf’ SIZE 10M DEFAULT INMEMORY;
  • ALTER TABLESPACE TS1 DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

ALTER TABLESPACE TS1 DEFAULT NO INMEMORY;