12c New Features – Database In-Memory

Here comes the most awaited feature with the release of 12.1.0.2 patchset – Database In-Memory. In this post we will first dwell upon the internals of this feature and then enable this feature in our test environment to benchmark the performance gain. This article assumes that the reader is well aware of the Oracle 11g Memory architecture, if not please go through Oracle 11g Memory Architecture & Oracle 11g Process Architecture.

To understand Database In-Memory feature and its benefits we first need to understand the unique “dual format” architecture that enables Oracle Database tables to be simultaneously represented on disk and in memory, using a traditional row format and a new in-memory column format.

Row Format – As we all know Oracle has traditionally stored data in row format where each new record is represented as a new row in a table having multiple columns with each column representing a different attribute about that record. This format is ideal for OLTP env. as it allows quick access to all the columns in a record since all the columns of the record are kept intact in-memory and on-storage.

Column Format – A column format database stores each of the attributes of a record in a separate column-structure. So obviously this is good for an OLAP env, as it allows faster data retrieval when a large portion of data is selected but only for a few columns.

So a row format is ideal for processing DMLs (Insert, Update, Delete) and a column format for a SELECT (Large portion but for a few columns), so up until now if you choose only one of the option and thus have to experience the cons of the format. Now with Oracle 12.1.0.2 comes, Oracle Database In-Memory feature which enables data to be simultaneously populated in memory in both a row format (in the buffer cache) and a new in-memory column format. The Oracle Database query optimizer is fully aware of the column format and thus it automatically routes analytic queries to the column format and OLTP operations to the row format, ensuring outstanding performance and complete data consistency for all workloads without any application changes.

Oracle Database In-Memory uses an In-Memory column store (IM Column Store), which is a new component of SGA known as In-Memory Area, data in the IM column store does not reside in the traditional row-format but in columnar format.  In-Memory area is a static pool (Static Parameter) within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0).

Also the IM Column Store is populated by a set of background processes known as worker processes (ora_w001_orcl).  Each worker process is given a subset of database blocks from the object to populate into the IM column store, which is made up of IM Compression Units IMCUs (Think IMCUs as analogous to what extents are to tablespace)

Now that we know the basic theory of the dual-architecture and the database In-Memory feature, its component and the processes involved, let’s go ahead and test the feature.

Enabling In-Memory Column Store

Before enabling let us take a look at the core Initialization Parameters related to Database In-Memory feature:-

1 2 3 4 5 6 7 8 9 10 11

SQL> show parameter INMEMORY NAME                                                TYPE        VALUE ------------------------------------------------ ----------- ------------- inmemory_clause_default                            string inmemory_force                                     string      DEFAULT inmemory_max_populate_servers                      integer        0 inmemory_query                                     string      ENABLE inmemory_size                                     big integer     0 inmemory_trickle_repopulate_servers_percent        integer        1 optimizer_inmemory_aware                           boolean      TRUE SQL>

Now let’s enable the IM Column Store by setting inmemory_size parameter to a non-default value and then re-start the DB as it is a static parameter.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

SQL> alter system set inmemory_size=500m scope=spfile;   System altered.   SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup ORACLE instance started.   Total System Global Area 1577058304 bytes Fixed Size 2924832 bytes Variable Size 973082336 bytes Database Buffers 50331648 bytes Redo Buffers 13848576 bytes In-Memory Area 536870912 bytes Database mounted. Database opened. SQL>  NAME                                                TYPE        VALUE ------------------------------------------------ ----------- ------------- inmemory_clause_default                            string inmemory_force                                     string      DEFAULT inmemory_max_populate_servers                      integer        1 inmemory_query                                     string      ENABLE inmemory_size                                     big integer    512M inmemory_trickle_repopulate_servers_percent        integer        1 optimizer_inmemory_aware                           boolean      TRUE SQL>

So we have enabled the In-Memory database Feature for our database, (If you are using AMM (MEMORY_TARGET) or ASMM (SGA_TARGET), you will need to extend this to account for the INMEMORY_SIZE parameter value) Now time to USE this feature to see how it effects the performance on analytical queries.

The In-Memory feature can be applied on a tablespace, table, partition, sub-partition or materialized view, however there are a few exceptions like any objects owned by SYS, SYSTEM or SYSAUX, IOTs, Clustered Tables cannot be populated in the IM column store for obvious reasons.

For this example we would use a sample schema (SH), so that you could reproduce the case at your end too

Let us check a few things before moving a table in memory.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> Conn SH/SH Connected. SQL> SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate FROM user_tables WHERE table_name='CUSTOMERS';   TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL ---------------------------------------- -------- -------- --------------- ----------------- ------------- CUSTOMERS DISABLED   SQL> SQL> SQL> SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;   POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS -------------------------- ----------- ---------- -------------------------- 1MB POOL 418381824 0 DONE 64KB POOL 100663296 0 DONE

As you can see above (By querying v$inmemory_area) In-Memory area is divided in two pools: 1MB pool & 64 KB pool. 1MB pool is used to store the actual column formatted data populated into memory whereas 64KB pool is used to store metadata about the objects that are populated in the IM Column Store.

Now let’s move the table in memory:-

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

SQL> alter table CUSTOMERS inmemory;   Table altered.   SQL> SELECT cust_valid, Count(*) FROM customers GROUP BY cust_valid;   C   COUNT(*) - ---------- I      44879 A      10621   2 rows selected.   SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate FROM user_tables WHERE table_name='CUSTOMERS';   TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL ---------------------------------------- -------- -------- --------------- ----------------- ------------- CUSTOMERS ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE   SQL> select owner, segment_name, populate_status from v$im_segments;   OWNER                     SEGMENT_NAME              POPULATE_ ------------------------- ------------------------- --------- SH                        CUSTOMERS                 COMPLETED   1 row selected.   SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;   POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS -------------------------- ----------- ---------- -------------------------- 1MB POOL                     166723584    4194304 DONE 64KB POOL                     25165824     131072 DONE   2 rows selected.

To verify if the Table In-Memory is used:-

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL> set autot traceonly explain SQL> SELECT cust_valid, Count(*) FROM customers GROUP BY cust_valid; 2 3 Elapsed: 00:00:00.03   Execution Plan ---------------------------------------------------------- Plan hash value: 1577413243   -------------------------------------------------------------------------------------- | Id | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- | 0  | SELECT STATEMENT          |           | 2     | 4     | 30 (10)    | 00:00:01 | | 1  | HASH GROUP BY             |           | 2     | 4     | 30 (10)    | 00:00:01 | | 2  | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 55500 | 108K  | 28 (4)     | 00:00:01 | --------------------------------------------------------------------------------------   SQL>

As you can see from the above explain plan (TABLE ACCESS INMEMORY FULL), table was accessed but in memory.

Similarly we can remove the table inmemory, alter table to move certain columns in memory, alter tablespace in memory etc according to the application requirement. Thus Oracle has bestowed us with the power of using INMEMORY feature but at the end of the day it depends on the DBA & Developer on how to use it else even INMEMORY column store can drastically affect performance just as a wrong INDEX does.

That’s all for today, I’ll write another post to cover the benchmarking part and would further explore Database In-Memory feature, till then you could yourself create your 12c test lab on a VM with OEL 7 with my 63 Steps to build you own 12c lab article.

You can use below options to further explore in-memory clause:-

1 2 3 4 5 6 7 8 9

ALTER TABLE CUSTOMERS NO INMEMORY; ALTER TABLE CUSTOMERS INMEMORY MEMCOMPRESS FOR CAPACITY LOW; ALTER TABLE CUSTOMERS INMEMORY PRIORITY HIGH;   ALTER TABLE CUSTOMERS NO INMEMORY (CUST_GENDER,CUST_MAIN_PHONE_NUMBER,CUST_INCOME_LEVEL,CUST_CREDIT_LIMIT,CUST_EMAIL,CUST_TOTAL,CUST_TOTAL_ID,CUST_SRC_ID,CUST_EFF_FROM,CUST_EFF_TO,CUST_VALID,COUNTRY_ID,CUST_STATE_PROVINCE_ID,CUST_STATE_PROVINCE,CUST_CITY_ID,CUST_CITY,CUST_POSTAL_CODE,CUST_STREET_ADDRESS,CUST_MARITAL_STATUS,CUST_YEAR_OF_BIRTH) INMEMORY MEMCOMPRESS FOR QUERY HIGH (CUST_ID,CUST_LAST_NAME) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (CUST_FIRST_NAME);   ALTER TABLESPACE USERS DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;