With Oracle Database 12.1.0.2, Oracle has released its In-Memory Database functionality. In short, Oracle In-Memory database provides transparent column-format memory access over an existing Oracle database. The normal row-format memory access is preserved for fast OLTP transactions; however, with new system parameters, that same data can be accessed from memory in column-format, drastically improving OLAP performance. Because it is transparent, there is no need to change applications to leverage this new functionality, which had previously only been exposed in engineered systems and ZFS as storage-level optimizations.
Enabling Oracle In-Memory Database
There are seven initialization parameter related to in-memory database, but the only one needed to enable the functionality is INMEMORY_SIZE. This system level, non-dynamic parameter specifies the amount of memory to be allocated to the in-memory database. As it is non-dynamic, a database restart is required to enable the feature.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 1040191104 bytes
Database Buffers 553648128 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER inmemory_size
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
inmemory_size big integer 0
SQL> ALTER SYSTEM SET inmemory_size=100M scope=spfile;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 1040191104 bytes
Database Buffers 436207616 bytes
Redo Buffers 13848576 bytes
In-Memory Area 117440512 bytes
Database mounted.
Database opened.Note that after enabling the parameter, a new line appears in the SGA details “In-Memory Area 117440512 bytes”. In this case, we have allocated 100M towards the in-memory database.
In-Memory Database Example
Now that the in-memory database is enabled, let’s work through an example to highlight the functionality. First, let’s create a user for this example, grant some privileges to that user and connect as that user:
SQL> CREATE USER inmem_user IDENTIFIED BY inmem_user;
User created.
SQL> GRANT connect, resource, unlimited tablespace, plustrace TO inmem_user;
Grant succeeded.
SQL> conn inmem_user/inmem_user
Connected.Then let’s create two tables: one without the in-memory functionality and one with in-memory. Oracle provides the ability to use the in-memory column store at the column, partition, table, materialized view and tablespace level. For this example, we’ll just be using it in conjunction with the CREATE TABLE statement:
SQL> CREATE TABLE no_inmem
2 NO INMEMORY
3 AS
4 SELECT * FROM all_objects;
Table created.
SQL> CREATE TABLE inmem
2 INMEMORY
3 AS
4 SELECT * FROM all_objects;
Table created.Now, let’s query against both tables and compare the results using AUTOTRACE:
SQL> SET AUTOTRACE ON
SQL> SELECT COUNT(*)
2 FROM no_inmem
3 WHERE object_id > 10000;
COUNT(*)
----------
69346
Execution Plan
----------------------------------------------------------
Plan hash value: 2339683627
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 5 | 351 (1)| 00:00:01 |
| 1| SORT AGGREGATE | | 1 | 5 | | |
|* 2| TABLE ACCESS FULL| NO_INMEM | 65855 | 321K| 351 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">10000)
Statistics
----------------------------------------------------------
33 recursive calls
0 db block gets
1297 consistent gets
1255 physical reads
0 redo size
544 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*)
2 FROM inmem
3 WHERE object_id > 10000;
COUNT(*)
----------
69346
Execution Plan
----------------------------------------------------------
Plan hash value: 3697233323
------------------------------------------------------------------------------
| Id| Operation | Name | Rows |Bytes |Cost (%CPU)|Time |
------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 5 | 14 (8)|00:00:01 |
| 1| SORT AGGREGATE | | 1| 5 | | |
|* 2| TABLE ACCESS INMEMORY FULL| INMEM| 65855| 321K| 14 (8)|00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - inmemory("OBJECT_ID">10000)
filter("OBJECT_ID">10000)
Statistics
----------------------------------------------------------
61 recursive calls
0 db block gets
1329 consistent gets
1255 physical reads
0 redo size
544 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processedCouple important things to note:
- With in-memory database, the cost is much lower than without this functionality (14 versus 351). That shows that OLAP queries such as the example above will run much faster than before without any changes to the application.
- The explain plan shows a new operation “TABLE ACCESS INMEMORY FULL” for the OLAP query.
- The recursive calls double in the in-memory example due to the fact that the table is loaded into memory in two different ways. So use caution with in-memory database if you don’t have a high buffer cache hit percentage.
Link to script that contains the examples in this post.