Oracle Database 12c In-Memory How To Get Started

Oracle Database In-Memory feature has been released by Oracle in recent patch set 12.1.0.2. Here is how to upgrade Oracle database to version 12.1.0.2: Upgrade Oracle Database 12c with ASM 12.1.0.1 to 12.1.0.2

With the introduction of Oracle Database In-Memory, a single database can now efficiently support mixed workloads, delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting. This is possible due to a unique “dual-format” architecture that enables data to be maintained in both the existing Oracle row format, for OLTP operations, and a new purely in-memory column format, optimized for analytical processing. It is not a free feature though, see at the bottom for prices.

Database In-Memory uses an In-Memory column store (IM column store), which is a new component of the Oracle Database System Global Area (SGA), called the In-Memory Area.  The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0) and must have a minimum size of 100MB.

The following objects can be stored in Oracle In-Memory database:

• Tablespaces
• Tables
• Partitions and Sub-partitions
• Materialized Views

If it is enabled at the tablespace level, then all new tables and materialized views in the tablespace will be enabled for the In-Memory column store by default.

Enabling In-Memory option in Oracle database:

Oracle Database In-Memory Area specified at the Container Database level (CDB) and by default each Pluggable Database (PDB) inherits the INMEMORY_SIZE from Container Database level (CDB).

Login as “sys” user to CDB (container database), change the parameter, restart database and start pluggable database PDB01;

SQL> ALTER SYSTEM SET inmemory_size = 512M scope=spfile;
SQL> shutdown immediate;
SQL> startup;
SQL> alter pluggable database "PDB01" open read write;

Enabling in-memory option for an entire table “customers”

Login as “sh” user to pluggable database PDB01 and alter table;

SQL> ALTER TABLE customers INMEMORY;
table CUSTOMERS altered.

SQL> select count(*) from customers;
  COUNT(*)
----------
     55500 

Checking in-memory option:

SELECT table_name,
  inmemory,
  inmemory_priority,
  inmemory_compression
FROM user_tables
WHERE table_name='CUSTOMERS';

TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
---------- -------- ----------------- --------------------
CUSTOMERS  ENABLED  NONE              FOR QUERY LOW

Enabling various In-Memory options in Oracle database:

Enabling the In-Memory attribute on the EXAMPLE tablespace by specifying the INMEMORY attribute

SQL> ALTER TABLESPACE example INMEMORY;

Enabling the In-Memory attribute on the sales table but excluding the “prod_id” column

SQL> ALTER TABLE sales INMEMORY NO INMEMORY(prod_id);

Disabling the In-Memory attribute on one partition of the sales table by specifying the NO INMEMORY clause

SQL> ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NO INMEMORY;

Enabling the In-Memory attribute on the customers table with a priority level of critical

SQL> ALTER TABLE customers INMEMORY PRIORITY CRITICAL;

Option CRITICAL: object is populated immediately after the database is opened.

I encourage you to read the following white paper “Oracle Database In-Memory” where you will find detailed information about In-Memory features such as In-Memory compression, scans, joins, aggregation, column store on RAC and much more.

Oracle claims In-Memory Database:

• as much as 300 times faster in terms of performance improvement
• fetching 1 mln records without in-memory option took ~20 minutes, while fetching 1 mln records with in-memory option enabled took roughly ~5 seconds

Oracle Database In-Memory prices