Oracle 12c In-Memory Database

While data warehouses do have their own place, most OLTP databases today are still used to run analytical and DSS type queries and workloads to support real-time decision-enabling information requirement which the business has. Designing a database to cater for this hybrid mix of OLTP and DSS workloads presents its own challenges to the DBA. For example the DBA needs to create indexes to support those adhoc or analytical queries, but the presence of those indexes slows down the day to day OLTP Insert/Update/Delete statements.

OLTP databases are best served by having data in the row format for optimise DML activity – few rows many columns. A row format allows quick access to all of the columns in a record since all of the data for a given record are kept together either in memory in the database buffer cache or on disk storage.

Reporting and decision-making type queries require just the opposite data model – few columns but which span vast number of rows. A column format is ideal for analytics, as it allows for faster data retrieval when only a few columns are selected but the query accesses a large portion of the data set.

The Database In-Memory feature was introduced in Oracle 12c 12.1.0.2 (June 2014) and provides the ability to easily perform real-time data analysis together with real-time transaction processing without any application change or re-design. A subset of data Data can now be stored in an in-memory column format, optimized for analytical processing.

Oracle Database In-Memory feature provides the best of both worlds by allowing data to be simultaneously populated in both an in-memory row format (the database buffer cache) and a new in memory column format.

An area in the SGA is allocated for the In-Memory column store called In-Memory Area and here the data is stored in column format instead of the traditional row format.

Let us now look at a worked example of the In-Memory feature in action!

I created a SALES2 table based on the SALES table residing the example SH schema. The table was populated with about 11 million rows and there are no indexes on the table.

This is the test query I used:

SELECT CALENDAR_MONTH_DESC, 
       sum(quantity_sold), 
       lag(sum(quantity_sold),1) over (ORDER BY CALENDAR_MONTH_DESC) "Previous Month" 
FROM 
       SH.sales2,
       SH.TIMES
WHERE
       SH.times.TIME_ID = SH.sales2.TIME_ID AND
       SH.times.CALENDAR_YEAR <> '2000'
GROUP BY CALENDAR_MONTH_DESC
ORDER BY CALENDAR_MONTH_DESC ASC;

We need to first enable the In-Memory memory area and note that this portion of memory is taken from the SGA already allocated to the database.

When we start the database we can see a new In-Memory area of memory displayed.

SQL> alter system set inmemory_size=1024m scope=spfile;

System altered.


SQL> startup;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2929552 bytes
Variable Size             603982960 bytes
Database Buffers         1526726656 bytes
Redo Buffers               13844480 bytes
In-Memory Area           1073741824 bytes
Database mounted.
Database opened.

We then use the INMEMORY clause to enable the SALES2 and TIMES tables to use the In-Memory column store.

The INMEMORY attribute can be
specified on a tablespace, table, (sub)partition, or materialized view.

SQL> alter table sh.times inmemory;

Table altered.


SQL> alter table sh.sales2 inmemory;

Table altered.

Note – at this stage since the tables have not been accessed, they have not been populated in the IM column store memory.

SQL> select segment_name, INMEMORY_SIZE , populate_status from v$im_segments;

no rows selected

ALTER TABLE customers INMEMORY PRIORITY CRITICAL;
Let us now execute the query – it takes .40 seconds to execute

CALENDAR SUM(QUANTITY_SOLD) Previous Month
-------- ------------------ --------------
2001-10              294444         246948
2001-11              268836         294444
2001-12              273708         268836
...
...

36 rows selected.

Elapsed: 00:00:00.40

Note the Explain Plan

SQL> select * from table(dbms_xplan.display_cursor());


----------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |       |       |   770 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION         |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                   |                           |       |       |            |          |
|   3 |    VECTOR GROUP BY                 |                           |    60 |  1200 |     5  (20)| 00:00:01 |
|   4 |     KEY VECTOR CREATE BUFFERED     | :KV0000                   |       |       |            |          |
|*  5 |      TABLE ACCESS INMEMORY FULL    | TIMES                     |  1461 | 29220 |     4   (0)| 00:00:01 |
|   6 |   WINDOW BUFFER                    |                           |    60 |  2520 |   765  (26)| 00:00:01 |
|   7 |    SORT GROUP BY                   |                           |    60 |  2520 |   765  (26)| 00:00:01 |
|*  8 |     HASH JOIN                      |                           |    60 |  2520 |   764  (26)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   9 |      VIEW                          | VW_VT_0834CBC3            |    60 |  1320 |   762  (26)| 00:00:01 |
|  10 |       VECTOR GROUP BY              |                           |    60 |   660 |   762  (26)| 00:00:01 |
|  11 |        HASH GROUP BY               |                           |    60 |   660 |   762  (26)| 00:00:01 |
|  12 |         KEY VECTOR USE             | :KV0000                   |       |       |            |          |
|* 13 |          TABLE ACCESS INMEMORY FULL| SALES2                    |    11M|   115M|   758  (25)| 00:00:01 |
|  14 |      TABLE ACCESS FULL             | SYS_TEMP_0FD9D6600_5B3351 |    60 |  1200 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
   5 - inmemory("TIMES"."CALENDAR_YEAR"<>2000)
       filter("TIMES"."CALENDAR_YEAR"<>2000)
   8 - access("ITEM_5"=INTERNAL_FUNCTION("C0") AND "ITEM_6"="C2")
  13 - inmemory(SYS_OP_KEY_VECTOR_FILTER("SALES2"."TIME_ID",:KV0000))
       filter(SYS_OP_KEY_VECTOR_FILTER("SALES2"."TIME_ID",:KV0000))

Note
-----
   - vector transformation used for this statement

The In-Memory column store has now been populated with the SALES2 and TIMES tables.

SQL> select segment_name, INMEMORY_SIZE , populate_status from v$im_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
INMEMORY_SIZE POPULATE_
------------- ---------
TIMES
      1179648 COMPLETED

SALES2
     91422720 COMPLETED

Disable In-Memory column store

We see that the same query now takes 2.85 seconds

SQL> alter session set inmemory_query=disable;

Session altered.


CALENDAR SUM(QUANTITY_SOLD) Previous Month
-------- ------------------ --------------
2001-10              294444         246948
2001-11              268836         294444
2001-12              273708         268836

…..
…….

36 rows selected.

Elapsed: 00:00:02.85

Note the difference in the Explain Plan with In-Memory option disabled

SQL> SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  2qvd8xzntr6fr, child number 1
-------------------------------------
SELECT CALENDAR_MONTH_DESC,        sum(quantity_sold),
lag(sum(quantity_sold),1) over (ORDER BY CALENDAR_MONTH_DESC) "Previous
Month" FROM        SH.sales2,        SH.TIMES WHERE
SH.times.TIME_ID = SH.sales2.TIME_ID AND        SH.times.CALENDAR_YEAR
<> '2000' GROUP BY CALENDAR_MONTH_DESC ORDER BY CALENDAR_MONTH_DESC ASC

Plan hash value: 1281295501

-------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |       | 14659 (100)|          |
|   1 |  WINDOW BUFFER       |        |    60 |  1860 | 14659   (1)| 00:00:01 |
|   2 |   SORT GROUP BY      |        |    60 |  1860 | 14659   (1)| 00:00:01 |
|*  3 |    HASH JOIN         |        |   918K|    27M| 14638   (1)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TIMES  |  1461 | 29220 |    18   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| SALES2 |   918K|  9870K| 14617   (1)| 00:00:01 |
-------------------------------------------------------------------------------